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:
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
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
Hi Arun,
as always, thanks for sharing your knowledge.
This is very good to know – I wasn’t aware!
Thanks!!!
Mike
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
Hi Mike,
Thanks for the information!
BTW, the documentation for 19c is even more confusing as the maximum value is 18 while the default value is 19
https://docs.oracle.com/en/database/oracle/oracle-database/19/spmsu/values-for-the-compatible-initialization-parameter.html#GUID-1146B7A7-81EE-4F90-8A0A-8440A6618CD9
Default Value: 19.0.0
Maximum Value: 18.Update.Revision
Felipe Reyes
Thanks Felipe,
I will send this straight to the doc team.
Cheers,
Mike
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
Satsuah,
it disabled to roll back a patch in case you need to. I will add this to the blog post.
Cheers,
Mike
Hi Mike ,
Thanks for your post ,
just need to clear one more thing in case of my 11gr2 version of rdbms , if i have compatibility of 11.2.0 in version 11.2.0.4 , will keeping value of “11.2.0” allow me to make use of features available in 11.2.0.4 or i need to set it 11.2.0.4 .
Hi Krishna,
I thought I made this more clear in my blog post – no feature ever (at least since 9i) has required to have COMPATIBLE on more than 3 digits. Hence, COMPATIBLE=11.2.0 is fully fine in EVERY patch set of 11g. And of course, fine for you, too. You won’t miss anything with COMPATIBLE=11.2.0 in Oracle 11.2.0.4.
Cheers,
Mike
Thanks Mike
Hi Mike,
if you set compatible =19.0.0 in spfile the do you think we can reverse it using the initsid.ora?
for example :
alter system set compatible=’19.0.0′ scope=spfile sid=’*’;
system alter
later on you want to reverse it then start the db using the initsid.ora
startup pfile=’initsid.ora’;
create spfile from pfile;
shutdown immediate/startup
do you think it work?
Hi Jonathan,
no, this won’t work. You can only set COMPATIBLE to the same and higher value. Otherwise the process will reject it.
Cheers,
Mike
Hi Mike,
we did test the upgrade it was fine but took too long . do you have any recommendation to shorten the upgrade time? we are trying to minimize the down time as much as possible!
thank you in advance!
Jonathan
Hi Jonathan,
please see:
https://mikedietrichde.com/videos/
Episode 10 – How Low Can You Go
We included several options to decrease upgrade duration.
Thanks,
Mike
thank you Mike! by the way. we are getting ORA-06520: PL/SQL: Error loading external library after the upgrade.it was working fine before upgrade. what are we missing after upgraded to 19.8C?
thanks,
Jonathan
Hi Jonathan,
at first, don’t go to 19.8.0 – this is 1.5 years old by now, you miss almost 4000 fixes.
Second, please open an SR. I am not aware of such issues. Do you use specific PLSQL parameters in your spfile?
Cheers,
Mike
“Oracle 19c: Data Guard Administration Workshop”
Data Guard Broker: Requirements
COMPATIBLE parameter: Set to 19.1 or later for primary and standby databases to take advantage of the new 19.1 features(Optional).
Does that mean Data Guard Broker has features in 19.1 that are not available or broken in 19.0?
Hi Blake,
no, not at all. It is only regarding those features:
https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features-19c-release-updates.html
You won’t find DG features as far as I am aware.
Hence, no need to change it.
Cheers
Mike
Hello Mike,
We have set the compatibility parameter of our 19.10c databases to 19.10.0.
Now we know this was a bug because it is recommended to always set this parameter to 19.0.0 or lower on 19c, right?
What are the “bad effects” of our setup?
Should we fix this? / How can we fix this problem?
How is 19.10.0 setting working with data pump and rman?
Is the 23c also compatible with 11.2g database?
Thanks for the help and best regards / Carsten
Hi Carsten,
there is no deeper issue – just make sure you use 19.10.0 then everywhere as COMPATIBLE to avoid any issues when you switch between homes, especially with Multtenant.
And as far as I am aware, the min COMPATIBLE setting for 23c should be 12.2.0.
Cheers,
Mike