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.

Should you change COMPATIBLE when you apply an RU?

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:

Now this doesn’t make me happy:

Should you change COMPATIBLE when you apply an RU?

Why not? I think COMPATIBLE should not have anything to do with RUs and RURs as they don’t transport new features. Second, the default value, at least in my Oracle 18.3.0 environment is “18.1.0”, and not 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.1.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.

–Mike

 

Share this:

8 thoughts on “Should you change COMPATIBLE when you apply an RU?

  1. Mike,
    Thanks for sharing these tiny, but extremely important and very often misunderstood, pieces of information. While on the subject of “un-blogged” issues, I was applying the Jan 2019 patch and found that 12.1.0.2 databases were hit by a bug which is documented here: PDB$SEED Database is in mount stage a after applying Jan 2019 DB PSU Patch 12.1.0.2.190115 (Doc ID 2500678.1). The MOS note says that the bug will show up for cloned databases, but I saw this issue in database created via DBCA. Fortunately a patch and workaround are available and I was able to get around the issue. The side effect of bug is that datapatch will fail to apply the SQL part of the patch because PDB$SEED remains in MOUNT mode.
    Thanks,
    Arun

  2. Hi Mike,
    Please note that some features might require to increase compatibility level at patchset level.
    We had a issue with a table with XMLtype not being replicated to a logical standby database (version 11.2.0.4 with compatible set to 11.2.0).
    My colleague found that in order to make this replication work we needed to set compatible to at least 11.2.0.3 as it affects format of redologs. More details here:
    https://docs.oracle.com/cd/E11882_01/server.112/e41134/data_support.htm#SBYDB00305
    ” XMLType stored in object-relational format or as binary XML requires that the primary database be running Oracle Database 11g Release 2 (11.2.0.3) or higher with a redo compatibility setting of 11.2.0.3 or higher”

    It would be nice to have some central place that would list all features that are affected by this parameter.
    Thanks,
    Edward

    • Ed,

      yes, but when we speak about “patch set level” than – in the new release model – we speak about 18c to 19c – and not 18.4.0 to 18.6.0.
      You shouldn’t have to increase it EVER for an RU or RUR as there shouldn’t be any new features depending on COMPATIBLE. Going to a new release (or a patch set in the old days) is a different story.

      Cheers,
      Mike

  3. Hi Mike,
    If we don’t use multitenant and use a non cdb database – what complications does changing compatible with RU bring in?
    So it’s better to leave the compatible at default even though we’re upgrading from a previous release to 18c latest RU – can you please confirm?

    Regards,
    Satsuah

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.