ORA-32004: obsolete and/or deprecated parameter(s)

Oracle 10g在设置了archivelog模式的参数log_archive_start(SQL> alter system set log_archive_start=true scope=spfile;)时，关闭后，重新启动时报错：

 

ORA-32004: obsolete and/or deprecated parameter(s)

ORACLE instance started.

此时虽然database已经正常启动，也不影响使用，但每次都报个错误，看起来不爽……

 

解决办法：

SQL>host oerr ora 32004
// *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.

 

提示到alert log中查看：

仔细查看后发现有如下提示：

Deprecated system parameters with specified values:
  log_archive_start       
End of deprecated system parameter listing
PMON started with pid=2, OS id=11327
PSP0 started with pid=3, OS id=11329
MMAN started with pid=4, OS id=11331
DBW0 started with pid=5, OS id=11333
CKPT started with pid=7, OS id=11342
SMON started with pid=8, OS id=11344
RECO started with pid=9, OS id=11346
LGWR started with pid=6, OS id=11335
MMON started with pid=11, OS id=11358
MMNL started with pid=12, OS id=11360

 

难道是10g 不支持该参数了? 原来在 10g 中，当打开归档模式(archive log mode) ，则 ARCn 进程被隐性的自动设定。10g 中也就不会出现 9i 以前那种数据库已经在归档模式，但是自动归档没有被设定的情况了。（也就是说从非归档模式使用命令切换到归档模式，不用再去指定是自动规定了）这也算是 Oracle 修正的一个小Bug。如果是从 9i 升级到 10g 的话，不注意可能会有点小问题的。要解决该问题，需要重置 log_archive_start 参数。

 

使用命令：

1、SQL> ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*';
ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*'
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

 

2、为了解决步骤1中的问题，shutdown immediate,然后重建spfile

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

SQL> conn as sysdba
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

3、重启实例：

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

Total System Global Area 2550136832 bytes
Fixed Size                  1980808 bytes
Variable Size             587204216 bytes
Database Buffers         1946157056 bytes
Redo Buffers               14794752 bytes
Database mounted.
Database opened.

4、执行 ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*';

SQL> ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*';

System altered.

5、重启实例：

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

Total System Global Area 2550136832 bytes
Fixed Size                  1980808 bytes
Variable Size             587204216 bytes
Database Buffers         1946157056 bytes
Redo Buffers               14794752 bytes
Database mounted.
Database opened.
SQL>

重启后就OK了