Oracle Multitenant: Be aware of the silent COMPATIBLE change

This topic is included in our Multitenant slides for a long time. But whenever I want to point somebody to the blog post, I realize that it’s not on the blog yet. Actually I discussed this topic recently during a customer visit again. It’s time to put it on the blog. When you use Oracle Multitenant: Be aware of the silent COMPATIBLE change.

Photo by Clint Patterson on Unsplash

Mixed version environments

When you work with Oracle Multitenant, the sooner or later you will have mixed version environments. This may be CDBs with Oracle 12.1.0.2 or Oracle 12.2.0.1 together with new Oracle 19c CDBs. In case you can’t or won’t do an “Everything at once” upgrade, you rather will prefer and attempt an unplug/plug/upgrade for one or more PDBs.

My example shows only one PDB, but you can do this of course with as many PDBS as necessary.

Default COMPATIBLE

When you create a CDB in Oracle 12.2.0.1, it will get the default COMPATIBLE value for Oracle 12.2.0.1: 12.2.0. Please remember: Never use more than 3 numbers, and stay with the default for the release. When you create a 19c CDB, it will have COMPATIBLE=19.0.0 – this is expected. But here you may need to pay attention. And potentially you may want to change the default value in your creation scripts or templates.

See also:

• When and how should you change COMPATIBLE?
• Should you change COMPATIBLE when you apply an RU?

Silent COMPATIBLE change

The danger here is: You unplug your PDB from CDB1 and plug it into CDB2. And at this point, COMPATIBLE gets increased silently. Of course, when you do a PLUG_COMPATIBILITY_CHECK, you will get a message in PDB_PLUG_IN_VIOLATIONS. But it is not required to do this check. Or, even it contains warnings, you are not forced to do anything. You can still unplug the PDB, and plug it into CDB2.

And then it happens.

And there is no way back anymore.

This is the danger.

At this point you can’t do the usual fallback activities anymore. No downgrade is possible as you did increase COMPATIBLE. Well, you didn’t – it just happened.

What would be the recommended way?

I recommend to have COMPATIBLE set equally across all CDBs as long as you haven’t upgraded fully to 19c yet. This will prevent such situations where you destroy the quickest fallback possibility accidentially, the downgrade.

But this has two or three negative implications. At first, you can’t benefit from the new COMPATIBLE-dependent features. Then you will need downtime again to adjust COMPATIBLE later on. And the big question will be: When you do the above exercise with 50 PDBs in the target CDB, you potentially won’t be able to restart the entire CDB just to change COMPATIBLE. Of course, you could run for a long while with COMPATIBLE=12.2.0 in 19c. But this may not be your desired solution. And finally, I know that there are different viewing angles regarding the test scenarios whether you may need to do separate tests with COMPATIBLE=12.2.0, and later, with COMPATIBLE=19.0.0.

The ideal solution would be if a PDB you plug in would keep its source COMPATIBLE – and you could adjust it on a PDB level. But it hasn’t been implemented this way.

More Information and Links

• Everything at Once – Multitenant Upgrade from 12.1.0.2 to 12.2.0.1
• Unplug a 12.2.0.1 PDB and plug into the Cloud
• Unplug / Plug / Upgrade
• Database Migration from non-CDB to PDB – The COMPATIBLE pitfall
• When and how should you change COMPATIBLE?
• Should you change COMPATIBLE when you apply an RU?
• Unplugging, Plugging and Upgrading a PDB to a new CDB – Tech Paper

–Mike