ORA-32004小记

  

学习eygle的《深入解析ORACLE DBA入门，进阶与诊断案例》参数与参数文件时，

按照示例做了如下修改:

 

环境:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL>

 

SQL> alter system set sql_trace=false scope=both;

System altered.
(在Oracle10g中，sql_trace已经变为了一个动态参数)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

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

Total System Global Area  321601536 bytes
Fixed Size                  2088304 bytes
Variable Size             209715856 bytes
Database Buffers          104857600 bytes
Redo Buffers                4939776 bytes
Database mounted.
Database opened.
SQL>

启动时报ORA-32004错误，查了下错误代码

bash-2.05$ oerr ora 32004
32004, 00000, "obsolete and/or deprecated parameter(s) specified"
// *Cause:  One or more obsolete and/or parameters were specified in
//          the SPFILE or the PFILE on the server side.
// *Action: See alert log for a list of parameters that are obsolete.
//          or deprecated. Remove them from the SPFILE or the server
//          side PFILE.
bash-2.05$

 

查看了alert文件，

bash-2.05$ more /export/home/oracle/u01/oracle/diag/rdbms/me6/me6/trace/alert_me6.log

发现

Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in client-side pfile /export/home/oracle/u01/oracle/product/11.1.0/db_1/dbs/initme6.ora on machine nbbseme6
System parameters with non-default values:
  processes                = 500
  sga_target               = 308M
  control_files            = "/export/home/oracle/u01/oracle/oradata/ME6/controlfile/o1_mf_7h5n6dwk_.ctl"
  control_files            = "/export/home/oracle/u01/oracle/flash_recovery_area/ME6/controlfile/o1_mf_7h5n6f2m_.ctl"
  db_block_size            = 8192
  db_cache_advice          = "ON"
  compatible               = "11.1.0.0.0"
  log_archive_format       = "%t_%s_%r.dbf"
  db_create_file_dest      = "/export/home/oracle/u01/oracle/oradata"
  db_recovery_file_dest    = "/export/home/oracle/u01/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 30G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=me6XDB)"
  audit_file_dest          = "/export/home/oracle/u01/oracle/admin/me6/adump"
  audit_trail              = "DB"
  db_name                  = "me6"
  open_cursors             = 300
  sql_trace                = FALSE
  pga_aggregate_target     = 102M
  diagnostic_dest          = "/export/home/oracle/u01/oracle"
Deprecated system parameters with specified values:
  sql_trace               
End of deprecated system parameter listing
Mon Feb 27 16:45:26 2012
PMON started with pid=2, OS id=9533
Mon Feb 27 16:45:26 2012
VKTM started with pid=3, OS id=9535 at elevated priority
VKTM running at (20)ms precision
Mon Feb 27 16:45:26 2012
DIAG started with pid=4, OS id=9539
Mon Feb 27 16:45:27 2012
DBRM started with pid=5, OS id=9541
Mon Feb 27 16:45:27 2012
PSP0 started with pid=6, OS id=9543

 

按照提示，把sql_trace从参数文件中删除，如下：

方法一:

 

SQL> create pfile from spfile；

File created.

vi编辑pfile(initme6.ora)

bash-2.05$ vi initme6.ora
"initme6.ora" 30 lines, 1188 characters me6.__db_cache_size=104857600
me6.__java_pool_size=16777216
me6.__large_pool_size=4194304
me6.__oracle_base='/export/home/oracle/u01/oracle'#ORACLE_BASE set from environment
me6.__pga_aggregate_target=109051904
me6.__sga_target=322961408
me6.__shared_io_pool_size=0
me6.__shared_pool_size=188743680
me6.__streams_pool_size=0
*.audit_file_dest='/export/home/oracle/u01/oracle/admin/me6/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/export/home/oracle/u01/oracle/oradata/ME6/controlfile/o1_mf_7h5n6dwk_.ctl','/export/home
/oracle/u01/oracle/flash_recovery_area/ME6/controlfile/o1_mf_7h5n6f2m_.ctl'
*.db_block_size=8192
*.db_cache_advice='ON'
*.db_create_file_dest='/export/home/oracle/u01/oracle/oradata'
*.db_domain=''
*.db_name='me6'
*.db_recovery_file_dest='/export/home/oracle/u01/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=32212254720
*.diagnostic_dest='/export/home/oracle/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=me6XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=106954752
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'"initme6.ora" 30 lines, 1188 characters
*.sga_target=320864256
*.sql_trace=FALSE
*.undo_tablespace='UNDOTBS1'

 

把“*.sql_trace=FALSE”从文件中删除。

 

创建spfile
SQL> create spfile from pfile='/export/home/oracle/u01/oracle/product/11.1.0/db_1/dbs/initme6.ora';

File created.

启动成功
SQL> startup
ORACLE instance started.

Total System Global Area  321601536 bytes
Fixed Size                  2088304 bytes
Variable Size             209715856 bytes
Database Buffers          104857600 bytes
Redo Buffers                4939776 bytes
Database mounted.
Database opened.

SQL>

 

方法二:

 

SQL> alter system reset sql_trace scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  321601536 bytes
Fixed Size                  2088304 bytes
Variable Size             209715856 bytes
Database Buffers          104857600 bytes
Redo Buffers                4939776 bytes
Database mounted.
Database opened.
SQL>

 

PS:重置SPFILE中设置的参数

虽然并不常用，但是Oracle仍然提供了重置参数的方法。当我们想恢复某个参数为缺省值时，可以使用如下命令:

alter system reset parameter <scope=memory|spfile|both> sid='sid|*'

该命令通常用于RAC环境中，在单实例环境中，需要指定sid='*',reset一个参数，Oracle将从SPFILE文件中去除该参数

 

整理于eygle的《深入解析ORACLE DBA入门，进阶与诊断案例》