Mapping for the compatible parameter digits:
=> To check your current COMPATIBLE parameter setting, issue the following command:
SELECT name, value, description FROM v$parameter WHERE name='compatible';
=> 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.
=> CAUTION!!! when setting it to higher level >>
Note that if you set it up, you in general cannot set it back (it modifies how we store, persist data on disk -- this is not reversible).... SO, test it first on a test system.
when you change this, there is no going back - it permits the use of disk storage structures that older releases did not understand. You would be allowing those structures to be created.
You need to set it "up" when you want to use a new feature that uses some new structure (eg: in 11gR1, you can have table compression enabled for conventional path operations - you need compatible set to 11.1 in order to use that new feature)
Otherwise, it is your call - do you want to enable these new features - doing so will make it not possible to downgrade - once you set it up and open up the database - there is no going back.
" Oracle recommends increasing the COMPATIBLE parameter only after complete testing of the upgraded database has been performed. After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the new Oracle Database 11g release. However, after you increase the COMPATIBLE parameter, the database cannot subsequently be downgraded "
=> COMPATIBLE=9.2.0 mean the lowest value (9.2.0 and 9.2.0.0.0 are SAME thing)
=> Compatible tells us what features we are allowed to use that affect how stuff is stored on disk. compatible simply tells us what we can write to disk. We won't use new redo log formats, we won't write new block level information, we won't create datafiles that have anything <earlier version> didn't have.
compatible only controls what is written to disk.
compatible isn't about performance in as much as it is about what FEATURES you may use.
You have compatible at 10.2 That means 11g will generate redo that a 10.2 database could process.
That means 11g will generate database blocks that 10.2 database could process (eg: new OLTP compressed blocks will NOT appear in that database - that feature will not be able to be used, since 10.2 cannot read that block format)
That means anything written to disk will be in a format the earlier release could read.
=> Steps to change compatible parameter
1) Perform full backup of your database (optional).
Before changing compatible parameter, you should take a full backup of your database. Because 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 whenever you want.
2) If you are using spfile parameter to start up your database then complete the following things.
a. 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;
b. Shut down and restart the instance.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
3) If you are using pfile parameter, then complete the following steps.
a. Shut down the instance if it is running:
SQL> SHUTDOWN IMMEDIATE
b. 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
c. Start the instance using STARTUP.
SQL> STARTUP