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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s