When Should You Report A Change Of Address?

Uniform is an about mystic parameter. It has a default setting for each release. Just if you attempt to detect more information what information technology really does, you lot won't be very happy. And in answer to my previous blog post almost whether you need to change Compatible when you apply an RU, I received the following question: When and how should yous change Uniform?

What does COMPATIBLE practice?

To observe an respond to this question, I consulted the documentation at first. And I found this:

COMPATIBLE
Setting Uniform ensures that new features do not write information formats or structures to disk that are not compatible with the earlier release, preventing a time to come downgrade. Features that require a higher value of COMPATIBLE to work correctly may be restricted or disabled to ensure downgrades are possible. The Compatible parameter specifies the Oracle version number that the database disk format must exist compatible with. The database tin be downgraded to the version specified in the Uniform parameter or any later version.

That is a skillful explanation.COMPATIBLE determines the information format and structures in disk. This for instance may utilize 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 notation of – if y'all change information technology, you won't be able to downgrade anymore.

And the Database Upgrade guide gives yous even more insights.

Furthermore, I queried MOS with terms such as "Uniform" 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 Uniform ?

When you modify Uniform …

What happens when you change COMPATIBLE? At first, y'all can just set up it to a college 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 inverse, you lot are not able to revert to the previous value.

Changing Compatible requires a restart of the database:

alter arrangement set COMPATIBLE='19.0.0' scope=spfile; shutdown immediate startup mount

You tin can't adjust Compatible while the database is up and running. And yous tin can't have dissimilar Compatible settings on different instances in a RAC environment.

When you alter it, it most likely will adapt 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.due east. how it interacts with an awarding. Having Compatible fix to 12.i.0 in an Oracle 18c environment should allow the database to behave as information technology would exist an 12.1 database.

But yet, where is the listing which features depend exactly on COMPATIBLE?

Unfortunately in that location is no such list to share. It would be good to have such a listing. Only the Oracle database code is a complex thing. We are working on something similar that for the next releases. Some features are pretty obvious and well documented. For instance, see the LONG identifiers nosotros introduced in Oracle 12.2. When Compatible is <12.2 you tin't create a tabular array named "THIS_IS_MY_TABLE_AND_IT_HAS_A_VERY_LONG_NAME" (44 byte), only when Compatible is >=12.2, then y'all can.

When should you alter COMPATIBLE?

Now the real question somebody raised after my previous blog mail Should you change Compatible when y'all apply an RU? was: "When should you change Uniform?"

And our usual answer is: "Change it 7-10 days afterward the upgrade IF y'all will get additional downtime."

Why that?

If you change it as office of the upgrade, y'all won't be able to downgrade anymore. And actually, the database downgrade is the by far virtually simple way to become 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 unremarkably is even faster than the database upgrade as we don't need to remove everything from an upgraded database. But your database will behave every bit information technology did before.

Hence, the 7-10 days are an estimate based on feel. And honestly, neither Roy nor I accept seen many databases being downgraded in real life later on an upgrade. Only at least we could convince DBAs and project leads to include the downgrade option as a possible fallback scenario and test information technology.

Still, remember that you volition need boosted downtime to change Compatible afterwards every bit the database volition demand to be restarted. This is not the instance 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 class, you can. Merely you will miss a lot of cool features. That is the downside.

Another person on Twitter raised the manus 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 tin can meet 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, nosotros don't modify COMPATIBLE. And neither does the DBUA. Or the new AutoUpgrade.

At that place are 2 exceptions to this rule:

COMPATIBLE gets adjusted when there is a forced Compatible change needed. This is the case when the release yous are upgrading to does non back up the COMPATIBLE setting of the source anymore. Simple example: Yous operate an Oracle 11.ii.0.4 database with Compatible=10.ii.0, then it will be pushed up. Or some other case: You upgrade an Oracle ix.ii database to Oracle 11.1.0.seven. Oracle 11g did not support Compatible=9.two.0 – hence, there had to exist an implicit COMPATIBLE adjustment.
Be aware when operating with Oracle Multitenant across different releases. Once you unplug from a CDB with lower COMPATIBLE setting and plug into one with a higher setting, an implicit COMPATIBLE change will happen. And it will block your option to plug dorsum into the source again.

Uniform 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 aligning in Uniform has no effect on the optimizer and its features. Those are derived from the value of OPTIMIZER_FEATURES_ENABLE. Please encounter also the SQL Tuning Guide almost how to influence the Optimizer with the setting of OPTIMIZER_FEATURES_ENABLE.

How many digits should yous utilise?

That is some other typical question I meet from time to time. And my advice is: 3

Why three? The default is 3 number, for instance 'xviii.0.0'. And 3 numbers are always plenty.

Even a marquee feature such as Oracle In-Retentiveness which got introduced with so called patch ready 12.one.0.2 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 Choice (DBIM) Nuts and Interaction with Information Warehousing Features:

The COMPATIBLE parameter must exist set to at least 12.1.0 to enable the feature, and the RDBMS version must exist at least 12.1.0.2.0.

Hence, 3 numbers are always enough. And as I wrote here, there's no need to alter Compatible when you apply an RU or RUR.

–Mike