Friday, August 1, 2014

ORA-39142: incompatible version number 4.1 in dump file with IMPDP

Scenario:

Import to a database using IMPDP is erroring out

Versions:

Source DB 12.1.0.1.0
Target DB 11.2.0.4.0

Error:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 4.1 in dump file "/u01/stage/schema_dump.dmp"

Reason:

As the error message rightly points out it is due to the incompatibility of EXPDP/IMPDP versions used.
Here the export was done using 12.1.0.1.0 EXPDP utility and dump created was tried to be imported using 11.2.0.4.0 IMPDP utility, resulting in the error.

Solution:

Use the "VERSION" option available with datapump.

The export data pump has the "VERSION" option which allows to create the dump file which is compatible with the previous releases.

Here is the syntax and description of VERSION from Oracle’s document.

VERSION={COMPATIBLE | LATEST | version_string}

The legal values for the VERSION parameter are as follows:

COMPATIBLE – This is the default value. The version of the metadata corresponds to the database compatibility level. 
Database compatibility must be set to 9.2 or higher.
LATEST – The version of the metadata corresponds to the database release.
version_string – A specific database release (for example, 11.2.0). In Oracle Database 11g, this value cannot be lower than 9.2.

Eg:

expdp system directory=EXPDP_DIR dumpfile=schema.dmp log=schema_expdp.log SCHEMAS=scott version=11.2.0.0.0

2 comments:

  1. expdp system directory=EXPDP_DIR dumpfile=schema.dmp log=schema_expdp.log SCHEMAS=scott version=11.2.0*

    ReplyDelete
  2. There is another solution, I need a Backup of a database that no longer exists, therefore the solution of generating a new backup is not possible, you must restore the one I have.

    ReplyDelete