Re: compatible

From: Richard Piasecki <usenet2_at_ogoent.com>
Date: 7 Sep 2006 05:54:02 -0500
Message-ID: <lotvf256ljhuij4tp9k1s79ga8tkb20ub1@4ax.com>

On 6 Sep 2006 16:03:19 -0700, mike7411_at_gmail.com wrote:

>On Oracle 10g, I tried the following option:
>
>ALTER SYSTEM SET compatible='8.1.0' SCOPE=spfile;
>
>However, when I try to reload, it refuses to load, saying:
>
>ORA-00401: the value for parameter compatible is not supported by this
>release
>
>I don't know how to change the parameter back without the database
>being loaded.
>
>Any ideas?

To start-up the database after you have messed-up your spfile in this way, create a pfile that points to the spfile but then overrides the compatibility parameter. For example, let's say I screw up my spfile in a similar way...

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

System altered.

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

SQL>
SQL>
SQL> startup

ORA-00400: invalid release value booger for parameter compatible SQL> So, now I create a new pfile with two initialization parameters, the spfile parameter which will point to the current spfile and the compatible parameter to override the value in the current spfile. In my case, my new pfile looks like this...

$ cat newpfile.ora
spfile='/apps/oracle/product/9.2.0.1/dbs/spfileogo.ora' compatible=9.2.0.1

Now, I need to start the database with the new pfile and issue the "alter system" command to change the spfile.

SQL> startup pfile=newpfile.ora
ORACLE instance started.

Total System Global Area 302747688 bytes

Fixed Size                   450600 bytes
Variable Size             167772160 bytes
Database Buffers          134217728 bytes
Redo Buffers                 307200 bytes

Database mounted.
Database opened.
SQL> alter system set compatible='9.2.0.1' scope=spfile;

System altered.

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

Total System Global Area 302747688 bytes

Fixed Size                   450600 bytes
Variable Size             167772160 bytes
Database Buffers          134217728 bytes
Redo Buffers                 307200 bytes

Database mounted.
Database opened.
SQL> Received on Thu Sep 07 2006 - 05:54:02 CDT