Compatible Parameter in Oracle ???


When you upgrade to a new release of the Oracle Database, certain new features may make your database incompatible with your previous release. Your upgraded Oracle database becomes incompatible with your previous release under the following conditions:

A new feature stores any data on disk (including data dictionary changes) that cannot be processed with your previous release.

An existing feature behaves differently in the new environment as compared to the old environment. This type of incompatibility is classified as a language incompatibility.

The COMPATIBLE Initialization Parameter

The Oracle Database enables you to control the compatibility of your database with the COMPATIBLE initialization parameter. By default, when the COMPATIBLE initialization parameter is not set in your parameter file, it defaults to 10.2.0 for Oracle Database 10g release 10.2. You cannot use new Oracle Database 10g features that would make your upgraded database incompatible until the COMPATIBLE initialization parameter is set to this value.

Downgrade the Oracle Database

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.

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.

Question:  How many digits are important when setting compatible init parameter? What is the difference between setting it to 10.2.0, 10.2.0.1 and 10.2.0.2?

Are all of the digits in the compatible parameter important?

Can I keep compatible set to 10.2.0 for all releases of 10gR2?

Answer:  Yes, the number of digits is meaningful, and many shops use less digits to make distributed communications easy between instances on different sub releases using compatible=10.2.

You should set the compatible parameter to the version of Oracle that you are using in order to use all replication features of that version of the database.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s