How to set Compatible Parameter in Oracle ?

Applies to: Oracle Database Software- Enterprise Edition - Version 9.2.0.1 to 12.1.0.1.0 [Release 9.2 to 12.1]
Information in this document applies to any platform.

Upgrade and Downgrade of database is responsibility of remote DBA and COMPATIBLE initialization parameter controls the compatibility level of your database. When you as an remote DBA and no longer need the ability to downgrade your database back to its previous version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

There are few more situations when not raising compatibility parameter may cause some issue. In recent one, 

I was working on a database having version Oracle 11.2.0.3 with Bundle patch 14 and getting ORA-00600: interne code, argument: [7005] which is a bug 7276499. On investigations i found that this bug is fixed in Oracle 11.2.0.3 Bundle patch 12.

How this is possible a bug which is fixed in lower version still exists in higher version ?

The reason was very simple, the compatible parameter of database was set to 11.2.0.2, That is the reason online dba was still seeing this bug.

Command to check compatible parameter

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Steps to change compatible parameter of database


After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for Oracle Database.

1. Change parameter value

   SQL> ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE;

2. Shutdown database

SQL> SHUTDOWN IMMEDIATE

3. Start database

SQL> Startup

4. Cross check for parameter for new value

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

Note: Please take your database backup before changing this parameter, because after changing this parameter database will become incompatible with earlier releases of Oracle Database.

To set compatible parameter in ASM instance. Please see http://www.dbas-oracle.com/2013/01/asm-compatibility.html

1 comment:

  1. hi , are you sure about this ?

    "The reason was very simple, the compatible parameter of database was set to 11.2.0.2, That is the reason online dba was still seeing this bug."

    best regards Marc

    ReplyDelete