环境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.
1.SQL> host oerr ora 12154
2.
3. 32004, 00000, "obsolete and/or deprecated parameter(s) specified"
4.// *Cause: One or more obsolete and/or parameters were specified in
5.// the SPFILE or the PFILE on the side.
6.// *Action: See alert log for a list of parameters that are obsolete.
7.// or deprecated. Remove them from the SPFILE or the server
8.// side PFILE.
9.SQL>
查看$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 然后重新启动数据库。