Re: compatible

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 7 Sep 2006 13:06:08 GMT
Message-ID: <J584EE.Do6@igsrsparc2.er.usgs.gov>

You can always convert your SPFILE to a PFILE and then modify the parameter. Convert the PFILE back to the SPFILE and you can start your instance. My example shown below is 10.2 on Solaris.

SQL> alter system set compatible='this_sucks' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00400: invalid release value this_sucks for parameter compatible

This is probably where you are at right now. So shutdown the instance and create the PFILE from the SPFILE.

GAST SQL> shutdown abort
ORACLE instance shut down.
GAST SQL> create pfile from spfile;
ERROR:
OCI-21710: argument is expecting a valid memory address of an object

File created.

An error was received, but the PFILE was created. You can always create the PFILE from the SPFILE, even if the instance is not running. I used a text editor (vi) to modify the PFILE's contents. Now, let's create the corrected SPFILE and start the instance:

SQL> create spfile from pfile;
ERROR:
ORA-01034: ORACLE not available

File created.

Again, an error is thrown, but the SPFILE has been created. The instance can now be started.

SQL> startup
ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size                  2029368 bytes
Variable Size             142608584 bytes
Database Buffers          268435456 bytes
Redo Buffers                6356992 bytes

Database mounted.
Database opened.
SQL> show parameter compatible

NAME                            TYPE        VALUE
------------------------------- ----------- -----------------------
compatible                      string      10.2.0.1.0

This exercise shows you how you can easily recover from a corrupted SPFILE that won't let you start your instance. You will not be able to issue ALTER SYSTEM command to modify the SPFILE's contents and fix the (logical) corruption, but you can always convert it to a PFILE, even if you do not have a PFILE created, and get the instance started.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu Sep 07 2006 - 08:06:08 CDT