Sunday, September 20, 2009

ORA-39000, ORA-39142: incompatible version number 2.1 in dump file

Cause of the Problem
--------------------
You made an attempt to import of an Export Data Pump dumpfile into a lower version target database whereas, the export dumpfile was created from a higher version source database which had a higher compatibility level, plus the Export Data Pump job was done without the VERSION parameter. For example, dumpfile set was created from database 11.1.0.6(with compatibility 11.1.0.0) without specifying any VERSION parameter and then you tried to import this dumpfile set into a lower version database like 10.2.0.1.


Solution of the Problem
-----------------------
Before going into solution you may wish to see an example posted in UDE-00018: Data Pump client is incompatible with database version.

As you already created dumpfile set without VERSION parameter so now there is no way to import this dumpfile set into a lower version than the source database compatibility level.

If you wish to import the dumpfile set into a lower version database than the source database compatibility level then the solution is to re-create the dumpfile set with the VERSION parameter VERSION=10.2. Just append the "VERSION=10.2" words with your expdp command if you try to import the dumpfile set into a 10.2.0.1 database compatibility.

You export command should look like,
expdp test/test@orcl directory=d dumpfile=test.dmp tables=test VERSION=10.2

If the source database does not exist then workaround you import the data into a temporary database (11.1.0.6.0 or higher), and re-create the dumpfile set with the correct VERSION parameter (i.e. VERSION=10.2.0), finally import the dumpfile set into a 10g database.

No comments: