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.

 

01	SQL> shutdown immediate;

02	Database closed.

03	Database dismounted.

04	ORACLE instance shut down.

05	SQL> startup;

06	ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

07	ORACLE instance started.

08

09	Total System Global Area 711430144 bytes

10	Fixed Size 1338896 bytes

11	Variable Size 536871408 bytes

12	Database Buffers 167772160 bytes

13	Redo Buffers 5447680 bytes

14	Database mounted.

15	Database opened.

16

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

 

1	SQL> shutdown immediate;

2	Database closed.

3	Database dismounted.

4	ORACLE instance shut down.

5	SQL> create pfile from spfile;

6

7	File created.

8

9

SQL>

Let us find where the init.ora file went...

 

01	oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]

02

$ ll

03

total 28

04	-rw-rw---- 1 oracle oinstall 1544 Feb 15 2010 hc_DBUA0.dat

05	-rw-rw---- 1 oracle oinstall 1544 Feb 15 2010 hc_odmdb.dat

06	-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

07	-rw-r--r-- 1 oracle oinstall 900 May 25 21:19 initodmdb.ora

08	-rw-r----- 1 oracle oinstall 24 Feb 15 2010 lkODMDB

09	lrwxrwxrwx 1 oracle oinstall 31 Feb 19 2010 orapwodmdb -> /u02/admin/odmdb/dbs/orapwodmdb

10	drwx------ 2 oracle oinstall 4096 Feb 15 2010 peshm_DBUA0_0

11	drwx------ 2 oracle oinstall 4096 Feb 15 2010 peshm_odmdb_0

12	lrwxrwxrwx 1 oracle oinstall 36 Feb 19 2010 spfileodmdb.ora -> /u02/admin/odmdb/dbs/spfileodmdb.ora

13	oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/dbs]

14	$ vi initodmdb.ora

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

 

01	odmdb.__db_cache_size=167772160

02	odmdb.__java_pool_size=4194304

03	odmdb.__large_pool_size=4194304

04	odmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

05	odmdb.__pga_aggregate_target=247463936

06	odmdb.__sga_target=465567744

07	odmdb.__shared_io_pool_size=0

08	odmdb.__shared_pool_size=281018368

09	odmdb.__streams_pool_size=0

10	*.audit_file_dest='/u02/admin/odmdb/adump'

11	*.audit_trail='db'

12	*.compatible='11.2.0.0.0'

13	*.control_files='/u02/oradata/odmdb/control01.ctl','/u02/flash_recovery_area/odmdb/control02.ctl'

14	*.db_block_size=8192

15	*.db_domain=''

16	*.db_name='odmdb'

17	*.db_recovery_file_dest='/u02/flash_recovery_area'

18	*.db_recovery_file_dest_size=4039114752

19	*.diagnostic_dest='/u01/app/oracle'

20	*.dispatchers='(PROTOCOL=TCP) (SERVICE=odmdbXDB)'

21	*.log_archive_start=FALSE

22	*.memory_target=713031680

23	*.open_cursors=300

24	*.processes=150

25	*.remote_login_passwordfile='EXCLUSIVE'

26	*.undo_tablespace='UNDOTBS1'

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

 

01	$ sqlplus / as sysdba

02

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

04

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

06

07	Connected to an idle instance.

08

09	SQL> startup

10	ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

11	ORACLE instance started.

12

13	Total System Global Area 711430144 bytes

14	Fixed Size 1338896 bytes

15	Variable Size 536871408 bytes

16	Database Buffers 167772160 bytes

17	Redo Buffers 5447680 bytes

18	Database mounted.

19	Database opened.

20

SQL>

21	SQL> create spfile from pfile;

22	create spfile from pfile

23

*

24	ERROR at line 1:

25	ORA-32002: cannot create SPFILE already being used by the instance

26

27

28

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.

 

01

SQL>

02	SQL> shutdown immediate;

03	Database closed.

04	Database dismounted.

05	ORACLE instance shut down.

06	SQL> exit;

07	Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

08	With the Partitioning, OLAP, Data Mining and Real Application Testing options

09	oracle@domU-12-31-39-03-BD-92:[/home/oracle]

10	$ echo $ORACLE_HOME

11	/u01/app/oracle/product/11.2.0/db_1

12	oracle@domU-12-31-39-03-BD-92:[/home/oracle]

13	$ sqlplus / as sysdba

14

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

16

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

18

19	Connected to an idle instance.

20

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

22	ORACLE instance started.

23

24	Total System Global Area 711430144 bytes

25	Fixed Size 1338896 bytes

26

27

28	Variable Size 536871408 bytes

29	Database Buffers 167772160 bytes

30	Redo Buffers 5447680 bytes

31	Database mounted.

32	Database opened.

33	SQL> create spfile from pfile;

34

35	File created.

36

37

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.

 

01

SQL>

02	SQL> shutdown immediate;

03	Database closed.

04	Database dismounted.

05	ORACLE instance shut down.

06	SQL> startup;

07	ORACLE instance started.

08

09	Total System Global Area 711430144 bytes

10	Fixed Size 1338896 bytes

11	Variable Size 536871408 bytes

12	Database Buffers 167772160 bytes

13	Redo Buffers 5447680 bytes

14	Database mounted.

15	Database opened.

16	SQL> archive log list;

17	Database log mode Archive Mode

18	Automatic archival Enabled

19	Archive destination USE_DB_RECOVERY_FILE_DEST

20	Oldest online log sequence 135

21	Next log sequence to archive 137

22	Current log sequence 137

23

SQL>

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