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
What does COMPATIBLE do?
To find an answer to this question, I consulted the documentation at first. And I found this:
COMPATIBLEensures 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
COMPATIBLEto work correctly may be restricted or disabled to ensure downgrades are possible.The
COMPATIBLEparameter 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
COMPATIBLEparameter 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.
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
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.
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
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
And our usual answer is: “Change it 7-10 days after the upgrade IF you will get additional downtime.”
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:
COMPATIBLEgets adjusted when there is a forced
COMPATIBLEchange needed. This is the case when the release you are upgrading to does not support the
COMPATIBLEsetting of the source anymore. Simple example: You operate an Oracle 184.108.40.206 database with
COMPATIBLE=10.2.0,then it will be pushed up. Or another example: You upgrade an Oracle 9.2 database to Oracle 220.127.116.11. Oracle 11g did not support
COMPATIBLE=9.2.0– hence, there had to be an implicit
- Be aware when operating with Oracle Multitenant across different releases. Once you unplug from a CDB with lower
COMPATIBLEsetting and plug into one with a higher setting, an implicit
COMPATIBLEchange 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
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 18.104.22.168 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:
COMPATIBLE parameter must be set to at least 12.1.0 to enable the feature, and the RDBMS version must be at least 22.214.171.124.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.