The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

ORA-32004: obsolete or deprecated ...

AS I was playing around with Oracle 11g instance, I set the log_archive_start parameter with SPFILE option.

Unfortunately this parameter is obsolete since 10g (oh never realised), so now every time I start this instance Oracle gives me a warning.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>

So now I want to reset or remove this parameter. Well solution comes from Tom Kyte's forum.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2200190221847

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL>

Let us find where the init<SID>.ora file went...

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]
$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Feb 15  2010 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 15  2010 hc_odmdb.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall  900 May 25 21:19 initodmdb.ora
-rw-r----- 1 oracle oinstall   24 Feb 15  2010 lkODMDB
lrwxrwxrwx 1 oracle oinstall   31 Feb 19  2010 orapwodmdb -> /u02/admin/odmdb/dbs/orapwodmdb
drwx------ 2 oracle oinstall 4096 Feb 15  2010 peshm_DBUA0_0
drwx------ 2 oracle oinstall 4096 Feb 15  2010 peshm_odmdb_0
lrwxrwxrwx 1 oracle oinstall   36 Feb 19  2010 spfileodmdb.ora -> /u02/admin/odmdb/dbs/spfileodmdb.ora
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]
$ vi initodmdb.ora

Oh yes! we found it now let us edit it and remove the obsolete parameter.

odmdb.__db_cache_size=167772160
odmdb.__java_pool_size=4194304
odmdb.__large_pool_size=4194304
odmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
odmdb.__pga_aggregate_target=247463936
odmdb.__sga_target=465567744
odmdb.__shared_io_pool_size=0
odmdb.__shared_pool_size=281018368
odmdb.__streams_pool_size=0
*.audit_file_dest='/u02/admin/odmdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/oradata/odmdb/control01.ctl','/u02/flash_recovery_area/odmdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='odmdb'
*.db_recovery_file_dest='/u02/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=odmdbXDB)'
*.log_archive_start=FALSE
*.memory_target=713031680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Now, after we edited the init<SID>.ora file we'll recreate the spfile FROM pfile.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 21:37:18 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

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

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance


SQL>

Oops.. The database was started with SPFILE (by default) so PFILE is not in effect.
So let us restart the database with PFILE. The default PFILE location is $ORACLE_HOME/dbs/init$ORACLE_SID.ora in my instance.

So, we'll shutdown the database, start it up with PFILE option, recreate SPFILE from PFILE, and then restart the database.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 21:57:38 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup PFILE="/u01/app/oracle/product/11.2.0/db_1/dbs/initodmdb.ora"
ORACLE instance started.

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes


Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL>

NOTE: There is no ORA warning. We'll just restart the database now and we are good to go... The database started using SPFILE :-)

Also we are in ARCHIVELOG mode.

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

Total System Global Area  711430144 bytes
Fixed Size                  1338896 bytes
Variable Size             536871408 bytes
Database Buffers          167772160 bytes
Redo Buffers                5447680 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     135
Next log sequence to archive   137
Current log sequence           137
SQL>

Next we'll explore how to manage a database in archivelog mode.

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?