How to set Oracle Compatible Property?

[knoppt]

Hi @gvenzl,
thanks for this great docker image!

I want to simulate the behavior of a production database in version 19.9.0 with Oracle-XE.
Since unfortunately there is no ORACLE-XE image for version 19.9, I use the image oracle-free:23-slim and then try to simulate the behavior of Oracle 19.9.0 with the help of the COMPATIBLE parameter! For this, I use the following init script:

ALTER SYSTEM SET COMPATIBLE = '19.9.0' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Shutdown and startup afterwards, as the parameter probably takes effect only on the next restart. The output of the script is as follows:

2024-11-22 11:38:29 System altered.
2024-11-22 11:38:29
2024-11-22 11:39:07 Database closed.
2024-11-22 11:39:09 Database dismounted.
2024-11-22 11:39:10 ORACLE instance shut down.
2024-11-22 11:39:17 ORACLE instance started.
2024-11-22 11:39:17
2024-11-22 11:39:17 Total System Global Area 1603726632 bytes
2024-11-22 11:39:17 Fixed Size 5360936 bytes
2024-11-22 11:39:17 Variable Size 654311424 bytes
2024-11-22 11:39:17 Database Buffers 939524096 bytes
2024-11-22 11:39:17 Redo Buffers 4530176 bytes
2024-11-22 11:39:17 ORA-00201: control file version 23.4.0.0.0 incompatible with ORACLE version
2024-11-22 11:39:17 19.9.0.0.0
2024-11-22 11:39:17 ORA-00202: control file: '/opt/oracle/oradata/FREE/control01.ctl'
2024-11-22 11:39:17 Help: https://docs.oracle.com/error-help/db/ora-00201/

Am I going in the wrong direction here? What is the error?
Please let me know if you need further assistance or additional information!

Thanks, Torsten

[gvenzl]

Hey @knoppt,

Thanks a lot for using these images!

It's a common misunderstanding of what the COMPATIBLE parameter does. It's an upgrade parameter that controls with what minimum version the database disk format remains compatible. For example, when we introduced the BOOLEAN data type in 23ai, a database with COMPATIBLE=19.0.0.0 would not allow a table to be created that contains a boolean data type because that is a disk format that a 19c database is not familiar with. Hence, if a user wanted to downgrade the database to 19c again, he couldn't any longer. COMPATIBLE is there to safeguard for this, but it's a one-way street. It can only be increased and once it has, never decreased (because a newer disk format has already been written).

The explanation in the doc is quite good:

The COMPATIBLE parameter 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 COMPATIBLE parameter or any later version.

Setting COMPATIBLE ensures 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 COMPATIBLE to work correctly may be restricted or disabled to ensure downgrades are possible.

Typically, users keep COMPATIBLE unchanged when upgrading their Oracle software. After upgrade, users will run the new release of the Oracle software for a few weeks to ensure that the new release is working correctly. Afterwards, users can choose to update COMPATIBLE to the latest version to take advantage of the new features.

COMPATIBLE is an upgrade parameter and, unfortunately, doesn't do what you want.
Oracle Database does not allow setting the behavior to an exact version, although the naming "compatible" may at first suggest that.
Unfortunately, what you want isn't possible. You can't make Oracle Database 23ai behave exactly like Oracle Database 19.9 or any other version.