ORA-00723 COMPATIBLE must be explicitly set

I was going through Reference book for Oracle 11gR2 for COMPATIBLE parameter at: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams034.htm#REFRN10019

I have a database which was recently upgraded to 11.2.0.3. I had not changed the value of COMPATIBLE parameter and it was still pointing to "10.2.0.4".

I decided to make that change today and also realized that in the documentation of COMPATIBLE parameter for 11gR2 it says the default value is "11.2.0".

So I removed the COMPATIBLE parameter from my pfile and wanted to see if Oracle takes the default value i.e. 11.2.0.

Below is what happend:

SQL> startup
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size                  2233960 bytes
Variable Size             545261976 bytes
Database Buffers          385875968 bytes
Redo Buffers                6123520 bytes
ORA-00723: Initialization parameter COMPATIBLE must be explicitly set

I checked alert.log which shows:
..
..
Spfile /oracle/product/11.2.0.3/dbs/spfileRD11852.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
Tue Dec 20 12:06:01 2011
ALTER DATABASE   MOUNT
ORA-723 signalled during: ALTER DATABASE   MOUNT...
..
..


That tells me that Oracle tried to use the default value i.e. 11.2.0 but it failed while mounting which requires compatible to be set explicitly.


Strange....

Searched on MOS and found 287664.1:

Error:   ORA-00723  (ORA-723)
Text:   Initialization parameter COMPATIBLE must be explicitly set 
---------------------------------------------------------------------------
Cause: Oracle detected that the initialization parameter COMPATIBLE was 
 not explicitly specified, and the compatibility of the database is 
 lower than the default value of the COMPATIBLE parameter. In order 
 to use the new compatible setting, the intialization parameter 
 must be explicitly set by the user. 
Action: Explicitly set the value of COMPATIBLE parameter either in PFILE 
 or SPFILE, whichever is used.

Ahh, that's a relief. So that error was because i was changing the database

compatibility to 11.2.0 and the compatible was not explicitly set. That means if the

compatibility of the database is changing the parameter compatible has to be explicitly set. 

Ok.

I added compatible=11.2.0 to pfile and database opened. Alert.log showed:

..

.. 

ALERT: Compatibility of the database is changed from 10.2.0.4.0 to 11.2.0.0.0.
Increased the record size of controlfile section 4 to 520 bytes
Control file expanded from 612 blocks to 614 blocks
Increased the record size of controlfile section 14 to 200 bytes
Control file expanded from 614 blocks to 618 blocks
Increased the record size of controlfile section 16 to 736 bytes
Control file expanded from 618 blocks to 622 blocks
Increased the record size of controlfile section 20 to 928 bytes
Control file expanded from 622 blocks to 626 blocks
Increased the record size of controlfile section 21 to 124 bytes
Control file expanded from 626 blocks to 632 blocks
Increased the record size of controlfile section 22 to 900 bytes
The number of logical blocks in section 22 remains the same 

..

.. 

Now the compatibility of database is already upgraded to 11.2.0, so if I remove

COMPATIBLE parameter from pfile the instance should startup without ORA-00723...lets see...

AND THAT'S TRUE...database opened without ORA-00723 this time. COMPATIBLE is not set

explicitly...

SQL> select name, value, ISMODIFIED, isdefault from v$parameter where name='compatible';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISMODIFIED                               ISDEFAULT
---------------------------------------- ---------
compatible
11.2.0
FALSE                                    TRUE