When and how should you change COMPATIBLE?

COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE?

What does COMPATIBLE do?

To find an answer to this question, I consulted the documentation at first. And I found this:

• COMPATIBLE
  Setting COMPATIBLE ensures that new features do not write data formats or structures to disk that are not compatible with the earlier release, preventing a future downgrade. Features that require a higher value of COMPATIBLE to work correctly may be restricted or disabled to ensure downgrades are possible.The COMPATIBLE parameter specifies the Oracle version number that the database disk format must be compatible with. The database can be downgraded to the version specified in the COMPATIBLE parameter or any later version.

That is a good explanation.COMPATIBLE determines the data format and structures in disk. This for instance may apply to the internal format of the redologs or the data file headers. In addition, features seem to be dependent on it. And – this is the most important fact to take note of – if you change it, you won’t be able to downgrade anymore.

And the Database Upgrade guide gives you even more insights.

Furthermore, I queried MOS with terms such as “COMPATIBLE” or “COMPATIBLE features“. But honestly, I found a lot of things. And not what I was looking for. Indeed, I read results from the community pages where people had exactly this question: Which features depend on COMPATIBLE?

When you change COMPATIBLE …

What happens when you change COMPATIBLE? At first, you can only set it to a higher value. You can’t revert it to a lower value since Oracle 9i. The ALTER DATABASE RESET COMPATIBILITY command does not exist anymore for over a decade. Hence, once changed, you are not able to revert to the previous value.

Changing COMPATIBLE requires a restart of the database:

alter system set COMPATIBLE='19.0.0' scope=spfile;
shutdown immediate
startup mount

You can’t adjust COMPATIBLE while the database is up and running. And you can’t have different COMPATIBLE settings on different instances in a RAC environment.

When you change it, it most likely will adjust the structure of the controlfiles, the redologs and the data file headers. And in the database, you may have now access to new features such as the online move of data files.

COMPATIBLE is also used to determine how your database acts to the “outside”, i.e. how it interacts with an application. Having COMPATIBLE set to 12.1.0 in an Oracle 18c environment should allow the database to behave as it would be an 12.1 database.

But still, where is the list which features depend exactly on COMPATIBLE?

Unfortunately there is no such list to share. It would be good to have such a list. But the Oracle database code is a complex thing. We are working on something like that for the next releases. Some features are pretty obvious and well documented. For instance, see the LONG identifiers we introduced in Oracle 12.2. When COMPATIBLE is <12.2 you can’t create a table named “THIS_IS_MY_TABLE_AND_IT_HAS_A_VERY_LONG_NAME” (44 byte), but when COMPATIBLE is >=12.2, then you can.

When should you change COMPATIBLE?

Now the real question somebody raised after my previous blog post Should you change COMPATIBLE when you apply an RU? was: “When should you change COMPATIBLE?”

And our usual answer is: “Change it 7-10 days after the upgrade IF you will get additional downtime.”

Why that?

If you change it as part of the upgrade, you won’t be able to downgrade anymore. And actually, the database downgrade is the by far most simple way to go back a few days after you upgraded. Guaranteed Restore Points are your database life insurance for issues happening during an upgrade, the database downgrade is your insurance for issues happening after the upgrade.

Especially customers who don’t (or can’t) test much, should at least test the downgrade option.

The downgrade option is simple and fast. It usually is even faster than the database upgrade as we don’t need to remove everything from an upgraded database. But your database will behave as it did before.

Hence, the 7-10 days are an estimate based on experience. And honestly, neither Roy nor I have seen many databases being downgraded in real life after an upgrade. But at least we could convince DBAs and project leads to include the downgrade option as a possible fallback scenario and test it.

Still, remember that you will need additional downtime to change COMPATIBLE afterwards as the database will need to be restarted. This is not the case in every project.

And if you ask if you could run a database for months or years with a lower COMPATIBLE setting, then my answer is: Of course, you can. But you will miss a lot of cool features. That is the downside.

Another person on Twitter raised the hand and mentioned that running the database even just for a few days with a lower COMPATIBLE setting may required an extra testing cycle, i.e. database upgrade but with lower COMPATIBLE setting versus database upgraded with higher COMPATIBLE setting. While I can see the point, I wouldn’t invest too much into testing with the lower setting (see the optimizer section below).

COMPATIBLE and Database Upgrades

During and after a database upgrade, we don’t change COMPATIBLE. And neither does the DBUA. Or the new AutoUpgrade.

There are two exceptions to this rule:

• COMPATIBLE gets adjusted when there is a forced COMPATIBLE change needed. This is the case when the release you are upgrading to does not support the COMPATIBLE setting of the source anymore. Simple example: You operate an Oracle 11.2.0.4 database with COMPATIBLE=10.2.0, then it will be pushed up. Or another example: You upgrade an Oracle 9.2 database to Oracle 11.1.0.7. Oracle 11g did not support COMPATIBLE=9.2.0 – hence, there had to be an implicit COMPATIBLE adjustment.
• Be aware when operating with Oracle Multitenant across different releases. Once you unplug from a CDB with lower COMPATIBLE setting and plug into one with a higher setting, an implicit COMPATIBLE change will happen. And it will block your option to plug back into the source again.

COMPATIBLE and the Optimizer

A very common misunderstanding happens when COMPATIBLE gets mixed with the Oracle optimizer. Both are independent from each other. As far as I know, an adjustment in COMPATIBLE has no effect on the optimizer and its features. Those are derived from the value of OPTIMIZER_FEATURES_ENABLE. Please see also the SQL Tuning Guide about how to influence the Optimizer with the setting of OPTIMIZER_FEATURES_ENABLE.

How many digits should you use?

That is another typical question I see from time to time. And my advice is: 3

Why 3? The default is 3 number, for instance ‘18.0.0‘. And 3 numbers are always enough.

Even a marquee feature such as Oracle In-Memory which got introduced with so called patch set 12.1.0.2 did not require to adjust COMPATIBLE in the forth digit. If you don’t believe me, please check MOS Note: 1903683.1 – Oracle Database In-Memory Option (DBIM) Basics and Interaction with Data Warehousing Features:

The COMPATIBLE parameter must be set to at least 12.1.0 to enable the feature, and the RDBMS version must be at least 12.1.0.2.0.

Hence, 3 numbers are always enough. And as I wrote here, there’s no need to change COMPATIBLE when you apply an RU or RUR.

–Mike