2

Team:

Recently i upgraded a 12.1.0 database to 19c (non CDB architecture). Current compatibility is set to 12.2.0. Can I downgrade it to the earlier version - 12.1.0? Documentation states the downgrade is possible but am not sure about the compatibility parameter.

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/downgrading-oracle-db-after-upgrade.html#GUID-59F92E2A-DC4D-46A3-9AC9-63F47BE4A86F

Improve this question

3 Answers 3

Reset to default
1

No. You cannot downgrade to a level below the current setting of COMPATIBLE.
From the 19c Database Reference

"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. "

So if your COMPATIBLE=12.2, then you cannot downgrade to below 12.2. I'm curious as to why, if you went from 12.1 to 19c, you set COMPATIBLE=12.2, a version you never had.

Improve this answer
4
  • Thanks for the info Ed. Setting the compatible with 12.2.0 was a stupid typo. I believe the datapump downgrade solution works provided we create a new 12.1.0.2 DB and perform version based expdp followed by impdp. Or is there any other way to simulate teh downgrade process?
    – The Owl
    Oct 31, 2020 at 21:13
  • export create a file that, while binary (not just text) is essentially just a bunch of CREATE <object> and INSERT statements to be issued by the companion import utility. As such, it is essentially blind to versions and compatibility. Since your COMPATIBLE is at a level higher than you want to go back to, that is really your only option.
    – EdStevens
    Oct 31, 2020 at 22:40
  • 1
    This is a too simplistic view of the export-import problem. Export (exppd) has to be aware if the version of the database where the data is imported. There maybe changes/extensions in the format of the binary dump file that the older impdp tool does not know. Also there may be changes in the s syntax, new data types or kind of ocjects.that are not available in the older database.. as @TheOwl already mentioned, a version specific export is needed.
    – miracle173
    Nov 1, 2020 at 6:21
  • @miracle173 - changes in structure of the dmp file would be addressed by use of the proper release of the export/import utilities. Changes in the syntax of the resulting sql would be extensions of the syntax from the higher-version db, but given that that db had compatible=12.2 and the target db has compatible 12.1, then the possibility that the source db could generate sql that is incompatible with the target is nil. At this point the OP should just try it and then address any issues that arise from there. It's really his only option.
    – EdStevens
    Nov 1, 2020 at 13:16
1

Check for Incompatibilities When Downgrading Oracle Database

If the compatibility level of your Oracle Database 12c database is set to 12.2.0, then you are not able to downgrade.

You can downgrade to 12.2, but not to 12.1.

Improve this answer
2
  • But the current DB version is not 12.2. Its 19c and the compatibility is set to 12.2. If it ere set to 19 then downgrade wouldn't be possible. Current: 19c Compatibility set to:12.2.0 Downgrade to: 12.1.0
    – The Owl
    Oct 31, 2020 at 19:35
  • @TheOwl You can not downgrade to a lower version than what is specified in compatible parameter.
    – Balazs Papp
    Oct 31, 2020 at 21:21
1

Other posts already mentioned that you cannot downgrade to a version below the compatible level. You have to export the objects and their data from the source database and import them into the target database. Usually you will use Oracle Datapump Export and Import for these tasks.

Oracle Support note "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)" tells us the following

Export Data Pump compatibility:

  1. The compatibility level of the Data Pump dumpfile set is determined by the compatibility level of the source database.

  2. When exporting data, use the same version Export Data Pump client as the version of the source database (up to one major version lower expdp client is possible, but this is not recommended).

  3. If the dumpfile needs to be imported into a lower version target database, use the Export Data Pump parameter VERSION to match the compatibility level of the target database.

...

4.2. Export Data Pump parameter: VERSION

With the Export Data Pump parameter VERSION, you can control the version of the dumpfile set, and make the set compatible to be imported into a database with a lower compatibility level.

...

When specified, internal Data Pump components like the Metadata Application Program Interface (API) make sure that the metadata objects are compatible with the specified version. As a result of this you have the ability to determine which version of the objects will be exported. Database objects or attributes that are incompatible with the specified version will not be exported. For example, tables containing new datatypes that are not supported in the specified version will not be exported.

...

Remarks:

  1. Import Data Pump cannot import dumpfile sets with a higher compatibility level than the target database. I.e.: an export Data Pump dumpfile created with VERSION=COMPATIBLE (default) from a 11.1.0.6.0 source database with COMPATIBLE=11.1.0 can never be imported into a 10.2.0.4.0 target database.

...

  • In exception to the above: new features are usually only introduced with a new main release (like with Oracle10g, Release 2 (10.2.0.1.0)), and not with a patchset (like 10.2.0.4.0 versus the base release of Oracle10g Release 2 (10.2.0.1.0)). Therefore it is possible to import any dumpfile from a main release in all patched versions of the main release (e.g.: import an 10.2.0.4.0 Export Data Pump dumpfile set into a 10.2.0.1.0 target database without specifying the VERSION=10.2.0.1.0 for the Export Data Pump job).

Import Data Pump compatibility:

  1. When importing data, use the same version Import Data Pump client as the version of the target database (up to one major version lower impdp client is possible, but this is not recommended).

  2. Import Data Pump can always read Export Data Pump dumpfile sets created by older versions of the database.

  3. Import Data Pump cannot import dumpfile sets if the compatibility level of the dumpfile set is higher than the the compatibility level of the target database.

...

5.2. Import Data Pump parameter: VERSION

With the Import Data Pump parameter VERSION, you can control the version of the objects that are loaded into the target database. When specified, internal Data Pump components like the Metadata Application Program Interface (API) make sure that the metadata objects are comptible with the specified version. As a result of this you have the ability to determine which version of the objects will be imported: database objects or attributes that are incompatible with the specified version will not be transferred to the target database.

...

Remarks:

  1. It is important to realize that the Import Data Pump parameter VERSION cannot be used to allow a higher version dumpfile to be imported into a lower release database. If the dumpfile needs to be imported into a lower release target database, you have to use the Export Data Pump parameter VERSION to match the compatibility level of the target database.

...

So you export with expdp tool from the source database. If the COMPATIBLE parameter of the source database is higher than the COMPATIBLE parameter of the target database you have to use the VERSION parameter for the export and set it to the value of the COMPATIBLE parameter of the source database. For the import you use the impdp tool of the target database. If you din't use the VERSION parameter for the export in this situation an import will not be possible.

Improve this answer
1
  • Hey @miracle173, Thanks for taking the time to provide the information. I am aware of this technique and believe this is the only viable option. Thanks again
    – The Owl
    Nov 20, 2020 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.