Should you change COMPATIBLE when you apply an RU?

I have a million ideas for blog posts. But I like it even more when people ask me to explain something on the blog which isn’t there yet but may be interesting for others as well. And Robert Ortel mailed me the other day and asked if he should change COMPATIBLE when he applies an RU for Oracle 18c. That’s a good question. And I doubt that the documentation has a good recommendation.

Should you change COMPATIBLE when you apply an RU?

No.

That’s a short blog post this time, isn’t it? 🙂 But honestly, you shouldn’t touch COMPATIBLE for several reasons. Reason no.1 is that a Release Update (RU) or an Release Update Revisions (RUR – the ones you shouldn’t use) don’t include new features. Hence, a change of COMPATIBLE does not make any sense.

In reverse, and this is reason no.2, it will complicate things a lot. Especially when you start exchanging PDBs between different CDBs, you’ll shoot yourself in the foot. There’s an implicit COMPATIBLE “upgrade” when you unplug a PDB from a lower COMPATIBLE CDB and plug it into a higher one. This means, you can’t go back anymore. Even if datapatch would allow you to roll back the SQL changes, once you unplug a PDB with COMPATIBLE 18.1.0 and plug it into 18.5.0, you can’t go back anymore.

But how should you set COMPATIBLE?

In this case I first consult the Oracle documentation:

• https://docs.oracle.com/en/database/oracle/oracle-database/18/spmsu/values-for-the-compatible-initialization-parameter.html#GUID-1146B7A7-81EE-4F90-8A0A-8440A6618CD9

I think COMPATIBLE should not have anything to do with RUs and RURs as they don’t transport new features.

The DBCA in Oracle 18c and Oracle 19c creates custom databases with COMPATIBLE at “18.0.0” and “19.0.0” as the documentation indicates.

Unfortunately I changed COMPATIBLE manually to 18.1.0 after I upgraded a database. In order to plug it into a CDB which got created with 18.0.0 by default automatically by DBCA, I had to lift COMPATIBLE for the CDB. Initial failure: I changed COMPATIBLE manually to 18.1.0 instead of the default of 18.0.0.

When you map the old and the new release model to each other, then a value such as “12.2.0” maps to “18”. But for sure it does not map to “18.4.1” as this would map into “12.2.0.1.3” – which never existed.

Summary

In the new release numbering schema, COMPATIBLE should not be changed from the default. There’s no reason to change it as it has no effect but will complicate further actions.

And be aware that Oracle Multitenant will implicitly “upgrade” COMPATIBLE. Once you have such an environment with multiple CDBs, make sure you use exactly the same COMPATIBLE value everywhere – or at least within one release, i.e. COMPATIBLE=12.2.0 for all 12.2 CDBs, COMPATIBLE=18.0.0 for all 18c CDBs, and so on.

By the way, as this question came up after I published the blog post:
Changing COMPATIBLE in a non-CDB environment has a dangerous effect as well. You won’t be able to rollback a patch binary-wise.

Don’t adjust COMPATIBLE to comply with the RU or RUR level. This makes no sense and is dangerous.

Addition Jan 29, 2021

Thanks to my dear colleague Sinan, I learned today that the backported feature Blockchain Tables which is available with Oracle 19.10.0 plus a one-off patch requires to increase COMPATIBLE to 19.10.0 as otherwise you can’t create a Blockchain Table in Oracle 19c. I still can’t recommend to do this change as it has many dangerous aspects, especially in a Multitenant environment where you work with multiple CDBs.

Furthermore, no feature ever so far required COMPATIBLE to be increased within the same release. Not even In-Memory in 12.1.0.2 required a different COMPATIBLE setting from the default.

–Mike