Tuesday, February 3, 2009

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

Problem Description
You have created a dumpfile set of a database 11g(COMPATIBLE parameter was set to 11.1.0.0). Now whenever you try to import this dumpfile set to a database 10g ORA-39001, ORA-39000, ORA-39142 returned as below.

C:\>J:\oracle\product\10.2.0\db_1\BIN\impdp.exe arju/a@orcl directory=d dumpfile=test.dmp tables=test

Import: Release 10.2.0.1.0 - Production on Tuesday, 03 February, 2009 23:02:04

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 2.1 in dump file "d:\test.dmp"

A variant of this error is,
C:\>impdp arju/a DIRECTORY=d DUMPFILE=test.dmp TABLES=test

Import: Release 10.1.0.2.0 - Production on Tuesday, 03 February, 2009 22:02:04
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "d:\test.dmp"

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 arju/a@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:

Post a Comment