Oracle Compatibility init parameter – for the new oracle dba career






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.

Table 5-1 illustrates the default value and the range of values of the COMPATIBLE initialization parameter in the new Oracle Database 10g release and in each release supported for upgrading to the new Oracle Database 10g release.

Table 5-1 The COMPATIBLE Initialization Parameter

Oracle Database Release Default Value Minimum Value Maximum Value
Oracle8i release 8.1.7 8.0.0 8.0.0.0.0 8.1.7.x.x
Oracle9i release 9.0.1 8.1.0 8.1.0.0.0 9.0.1.x.x
Oracle9i release 9.2 8.1.0 8.1.0.0.0 9.2.0.x.x
Oracle Database 10g release 10.1 10.0.0 9.2.0.0.0 10.1.0.x.x
Oracle Database 10g release 10.2 10.2.0 9.2.0.0.0 10.2.0.x.x

 

Downgrading and Compatibility

Before upgrading to the new Oracle Database 10g release, the COMPATIBLE initialization parameter must be set to at least 9.2.0, which is the lowest possible setting for Oracle Database 10g release 10.2. Only a subset of Oracle Database 10g features are available while the COMPATIBLE initialization parameter is set to this value.

After upgrading to the new Oracle Database 10g release, you can set the COMPATIBLE initialization parameter to match the release number of the new release. Doing so enables you to use all of the features of the new release, but prevents you from downgrading back to your previous release.

If, after upgrading, you will want to downgrade, then the COMPATIBLE initialization parameter must be left as follows after the upgrade:

  • Set to 9.2.0 if you upgraded from release 9.2
  • Set to 10.1.0 or earlier if you upgraded from release 10.1

See Also:

Chapter 7, "Downgrading a Database Back to the Previous Oracle Database Release" for more information about downgrading

How the COMPATIBLE Initialization Parameter Operates

The COMPATIBLE initialization parameter operates in the following way:

  • It controls the behavior of your database. For example, if you run a release 10.2 database with the COMPATIBLE initialization parameter set to 9.2.0, then the release 10.2 database generates release 9.2 compatible database structures on disk. Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new features that make the database incompatible with the COMPATIBLE initialization parameter, then an error is displayed. However, any new features that do not make incompatible changes on disk are enabled.
  • It makes sure that the database is compatible with its setting. If the database becomes incompatible with its setting, then the database does not start and terminates with an error. If this happens, then you must set the COMPATIBLE initialization parameter to an appropriate value for the database.

See Also:

Oracle Database Concepts for more information about database structures

Compatibility Level

The compatibility level of your database corresponds to the value of the COMPATIBLE initialization parameter. For example, if you set the COMPATIBLE initialization parameter to 10.2.0, then the database runs at 10.2.0 compatibility level.

Checking the Current Value of the COMPATIBLE Initialization Parameter

To check the current value of the COMPATIBLE initialization parameter, issue the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter

         WHERE name = ‘compatible’;

When to Set the COMPATIBLE Initialization Parameter

Once the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the Oracle Database 10g release. However, after you do this, the database cannot subsequently be downgraded.

Setting the COMPATIBLE Initialization Parameter

Complete the following steps to set the COMPATIBLE initialization parameter to a higher value:

  1. Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).

Raising the COMPATIBLE initialization parameter may cause your database to become incompatible with earlier releases of the Oracle Database, and a backup ensures that you can return to the earlier release if necessary.

See Also:

Oracle Database Backup and Recovery Basics for more information about performing a backup

  1. If you are using a server parameter file, then complete the following steps:
    1. Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.

For example, to set the COMPATIBLE initialization parameter to 10.2.0, issue the following statement:

SQL> ALTER SYSTEM SET COMPATIBLE = ‘10.2.0’ SCOPE=SPFILE;

  1.  
    1. Shut down and restart the instance.
  2. If you are using an initialization parameter file, then complete the following steps:
    1. Shut down the instance if it is running:
  1. SQL> SHUTDOWN IMMEDIATE
  1.  
    1. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.

For example, to set the COMPATIBLE initialization parameter to 10.2.0, enter the following in the initialization parameter file:

COMPATIBLE = 10.2.0

  1.  
    1. Start the instance using STARTUP.
Author: admin