Sunday, August 17, 2014

RDBMS -- Oracle "compatible" parameter

Lets have a quick look to the compatibility parameter in Oracle Databases..

The Compatible parameter is used to control the formats of oracle data blocks and redo streams. It is basically controlling what is written to disk.
Compatible parameter can be set to the current database version or a lower version, according to the needs. The important thing is that; when you set your compatible parameter to a upper version, you can set it back. For example: if you set your compatible parameter to 11.2.0 , you can set it back to 11.1.0..
Compatible parameters are used mainly in upgrades and migrations. ASM instance also use the compatible parameters to behave according to the desired binary versions..
ASM has attributes in the diskgroup levels, too.
The compatible.asm diskgroup attribute controls the format of the ASM metadata.  This attribute controls the needed version of  the ASM instances which can mount the related diskgroup.
The compatible.rdbms diskgroup attribute of ASM determines the format of ASM files.
The diskgroup can be accessed by any database instance with a compatible init.ora parameter set equal to or higher than the compatible.rdbms attribute.

In this manner, you can have an Oracle Database 11g  which run in 10.2 compatibility mode.
If you have such a database, your database will generate redo and generate data block that a 10.2 database could understand. On the other hand; even if this database is an 11g, a new  feature -- OLTP Compressed block 11g-- feature will not work  in this database, as its compatible parameter is set to 10.2, and as the Compressed block feature requires a change on data block format..
On the other hand, your data file are written in a format that an 10g Oracle Database could still read..

Additional info:



It is important to know that, compatible parameters does not affect the medata stored in the database..
It also does not effect the optimizer behaviour..  OPTIMIZER_FEATURES_ENABLE parameter controls all about the optimizer.For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0.
AWR repository also is not affected from  compatible parameter.
As mentioned compatible controls what is written to disk..


So, in a case of an upgrade, the feautes depends on the db block or redo streams requires this parameter to be set to the upgraded version..  To know these feature beforehand, we need to examine the documentation and look for the new features and their compatibility requirements..

Lastly, lets check an Ex-factory Exadata X4 to see that parameter in use;
--in this example we'll see compatability between ASM and Database instances, as well..

We will query v$asm_diskgroup view, which displays one row for every ASM disk group discovered by the ASM instance on a node.

select name,compatibility,database_compatibility from v$asm_diskgroup;

NAME                           COMPATIBILITY                                   DB_COMPATIBILITY

DATAC1                         11.2.0.4.0                                                   11.2.0.2.0
DBFS_DG                        11.2.0.4.0                                                   11.2.0.2.0
RECOC1                         11.2.0.4.0                                                   11.2.0.2.0


As you see here, we  have 3 diskgroups which has compatability set to 11.2.0.4.. This means minimum software version required for an ASM instance to mount these diskgroups needs to be 11.2.0.4..
Also, our diskgroups have 11.2.0.2 set for the databases_compatability, which means minimum software version required for a database instance to use files in this disk group is 11.2.0.2.

So as you may expected, in order to startup a new database which stores its files in ASM of these Exadata environment, our database compatible parameter must be set to 11.2.0.2 at least ...

Okay, that is all for now.. I hope you will find it useful.

No comments :

Post a Comment