How To Use Datapump Between Different Database Releases

Imagine that you are asked to provide a datapump export of your database which is to be imported somewhere else.

The biggest mistake that can happen here is the fact that we assume the target database is of the same version or higher, than our source database.

That is not always the case.

What if the source database is 12.2 and the target database version is 11.2?

What if the versions are the same, but the compatible parameter of the target database is lower?

Next time someone asks you for a datapump export, before you even begin, ask them, what version the target database is, or what is the compatible mode of the target database?
This way you will not be wasting your time and doing double work.

Once you find out the target version/compatible parameter, running a datapump export job with the appropriate version will be easy, as Oracle is providing you with an export datapump parameter called VERSION to facilitate this process.

Why would you want to use the VERSION parameter?

• Downgrading a database. You’ve upgraded your database to a higher release let’s say from 12.1 to 12.2, and after the database has been in use, you realized that there were problems. You cannot really restore from the pre-upgrade backup, as new data was added to your database and there would be data loss, so one of the solutions is
  to downgrade your database, by exporting from the 12.2 version and importing into a 12.1 version.
  If you are just exporting from 12.2 without explicitly specifying the version number, you will not be able to import into the 12.1 database.
  By default the export datapump VERSION parameter’s value is COMPATIBLE, which indicates that the object definitions will be compatible with the release that is specified by the COMPATIBLE init.ora parameter.
• Providing a dump file to a vendor for troubleshooting. We cannot assume that the target database is at the same or higher version than our database.
• Copying data into a database that has a lower version, for any internal use.

There might be other reasons, but these are three I can think of right now.

So, how would you proceed to run a datapump export with a lower version?

It is simple. Just specify the parameter VERSION=12.1, or any other version that you need to, in the export datapump parameter file and the export datapump will be run with the requested compatibility mode.

Here is an example:

expdp.par content:

directory=data_pump_dir
dumpfile=db_export_v121.dmp
logfile=db_export_v121.log
full=y
version=12.1

$ expdp parfile=expdp.par

You will notice that you might get some warnings and the export will still continue on. These warnings are due to the fact, that the new features
from the newer release cannot be exported in the older release version:

Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.

A few things to remember:

• An export datapump dumpfile created by a lower version of Oracle will always be compatible with a higher version of Oracle.

For example, a dumpfile exported from an 11.2 version database, can always be imported into a 12.1 or higher database. The higher version database is always compatible with lower version dumpfiles.

• When you run the export datapump in a higher release with the VERSION set to a lower release, you can get errors and warnings for features that are not available in the lower release.

Example: when you export in a 12.2 database with the VERSION=12.1, you will get the following warning:

“Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.” The export will still complete.

• When you run the export datapump in a higher release with the VERSION set to a lower release, the created dump file can be imported into the lower release.

Example: if you export a 12.2 database with the VERSION=12.1, the created dump file can be imported into a 12.1 database.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!