ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started

环境oracle10gR2  solaris10

在执行sql_trace查看一些sql执行情况时，做了一下操作：

SQL>alter system set sql_trace scope=both;
SQL>shutdown immediate
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              71305460 bytes
Database Buffers           92274688 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

查看$ORACLE_BASE/admin/TEST/bdump/alter_TEST.log文件

Tue Jul 03 10:10:40 CST 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.5.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 121634816
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  sga_target               = 289406976
  control_files            = /oradb/oracle/oradata/TEST/control01.ctl, /oradb/oracle/oradata/TEST/control02.ctl, /oradb/oracle/oradata/TEST/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 150994944
  compatible               = 10.2.0.5.0
  log_archive_dest_1       = LOCATION=/oradb/oracle/arch
  log_archive_format       = %t_%s_%r.dbf
  db_file_multiblock_read_count= 8
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  global_names             = TRUE
  service_names            =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=TESTXDB)
  job_queue_processes      = 10
  background_dump_dest     = /oradb/oracle/admin/TEST/bdump
  user_dump_dest           = /oradb/oracle/admin/TEST/udump
  core_dump_dest           = /oradb/oracle/admin/TEST/cdump
  audit_file_dest          = /oradb/oracle/admin/TEST/adump
  db_name                  = TEST
  open_cursors             = 300
  sql_trace                = FALSE
  pga_aggregate_target     = 96468992 Deprecated system parameters with specified values:
  sql_trace               
End of deprecated system parameter listing
PMON started with pid=2, OS id=1024
PSP0 started with pid=3, OS id=1026
MMAN started with pid=4, OS id=1028
DBW0 started with pid=5, OS id=1030
LGWR started with pid=6, OS id=1032
CKPT started with pid=7, OS id=1034
SMON started with pid=8, OS id=1036
RECO started with pid=9, OS id=1038
CJQ0 started with pid=10, OS id=1040
MMON started with pid=11, OS id=1042
Tue Jul 03 10:10:43 CST 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=1044
Tue Jul 03 10:10:43 CST 2012
starting up 1 shared server(s) ...
Tue Jul 03 10:10:43 CST 2012
ALTER DATABASE   MOUNT
Tue Jul 03 10:10:50 CST 2012
Setting recovery target incarnation to 2
Tue Jul 03 10:10:50 CST 2012
Successful mount of redo thread 1, with mount id 2085797798
Tue Jul 03 10:10:50 CST 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Tue Jul 03 10:10:50 CST 2012
ALTER DATABASE OPEN
Tue Jul 03 10:10:50 CST 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=1053
Tue Jul 03 10:10:50 CST 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=1055
Tue Jul 03 10:10:50 CST 2012
Thread 1 opened at log sequence 10
  Current log# 1 seq# 10 mem# 0: /oradb/oracle/oradata/TEST/redo01.log
Successful open of redo thread 1
Tue Jul 03 10:10:50 CST 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Tue Jul 03 10:10:50 CST 2012
ARC1: Becoming the heartbeat ARCH
Tue Jul 03 10:10:50 CST 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 03 10:10:50 CST 2012
SMON: enabling cache recovery
Tue Jul 03 10:10:51 CST 2012
Successfully onlined Undo Tablespace 1.
Tue Jul 03 10:10:51 CST 2012
SMON: enabling tx recovery
Tue Jul 03 10:10:51 CST 2012
Database Characterset is WE8ISO8859P1
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=1057
Tue Jul 03 10:10:53 CST 2012
Completed: ALTER DATABASE OPEN

 

然后查看一下信息，发现sql_trace已经作为不可用参数了

SQL>select name from v$parameter where isdeprecated = 'TRUE';
NAME
--------------------------------------------------------------------------------
lock_name_space
buffer_pool_keep
buffer_pool_recycle
max_commit_propagation_delay
remote_archive_enable
log_archive_start
parallel_server
parallel_server_instances
fast_start_io_target
logmnr_max_persistent_sessions
serial_reuse

NAME
--------------------------------------------------------------------------------
max_enabled_roles
global_context_pool_size
cursor_space_for_time
plsql_compiler_flags
sql_trace
parallel_automatic_tuning
drs_start

18 rows selected.

解决方案：

1、通过alter system命令修改spfile

SQL>alter system reset sql_trace scope=spfile SID='*';

2、通过pfile修改启动参数，然后再重建spfile

SQL>create pfile from spfile;
SQL>show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oradb/oracle/102/dbs/spfileTE
                                                 ST.ora

bash#vi /oradb/oracle/102/dbs/initTEST.ora

注释掉sql_trace=FALSE 后执行create spfile from pfile 然后重新启动数据库。