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

環境oracle10gR2 solaris10

在執行sql_trace檢視一些sql執行情況時，做了一下操作：

 1 SQL>alter system set sql_trace scope=both;
 2 SQL>shutdown immediate
 3 SQL> startup
 4 ORA-32004: obsolete and/or deprecated parameter(s) specified
 5 ORACLE instance started.
 6 Total System Global Area  167772160 bytes
 7 Fixed Size                  1218316 bytes
 8 Variable Size              71305460 bytes
 9 Database Buffers           92274688 bytes
10 Redo Buffers                2973696 bytes
11 Database mounted.
12 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已經作為不可用引數了

 1 SQL>select name from v$parameter where isdeprecated = 'TRUE';
 2 NAME
 3 --------------------------------------------------------------------------------
 4 lock_name_space
 5 buffer_pool_keep
 6 buffer_pool_recycle
 7 max_commit_propagation_delay
 8 remote_archive_enable
 9 log_archive_start
10 parallel_server
11 parallel_server_instances
12 fast_start_io_target
13 logmnr_max_persistent_sessions
14 serial_reuse
15 
16 NAME
17 --------------------------------------------------------------------------------
18 max_enabled_roles
19 global_context_pool_size
20 cursor_space_for_time
21 plsql_compiler_flags
22 sql_trace
23 parallel_automatic_tuning
24 drs_start
25 
26 18 rows selected.

解決方案：

1、透過alter system命令修改spfile

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

2、透過pfile修改啟動引數，然後再重建spfile

1 SQL>create pfile from spfile;
2 SQL>show parameter pfile
3 NAME                                 TYPE        VALUE
4 ------------------------------------ ----------- ------------------------------
5 spfile                               string      /oradb/oracle/102/dbs/spfileTE
6                                                  ST.ora

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

註釋掉sql_trace=FALSE 後執行create spfile from pfile 然後重新啟動資料庫。