Oracle DBA and RAC DBA Expert: Datapump export/import Errors ORA-39142, ORA-39000, ORA-39001 and UDI-00018

Tuesday, June 4, 2013

Datapump export/import Errors ORA-39142, ORA-39000, ORA-39001 and UDI-00018

When you are working with 10g to 11g database/user migration, you will encounter many issues.

Here are the few issues common issues.

Scenario 1: User is trying to import a dumpfile using 10g Datapump utility which is generated by 11g Data pump utility.

Oracle@Srv1$ impdp system/xxxxxx schemas=SCOTT dumpfile= scottbkp.dmp logfile=scottbkp.log remap_schema=SCOTT:SCOTTTEST

Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 17 April, 2013 12:59:50

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.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 3.1 in dump file "/u01/home/oracle/product/10.2.0/db_1/rdbms/log/scottbkp.dmp"

Cause: User is trying to import a dumpfile whose version (11.2.0.1) number is incompatible with the dump file version of the Data Pump product currently running on the system (10.2.0.4). You cannot able to use lower version of Data pump to import a dumpfile create by a newer version of the data pump utility.

Solution: To migrate the data from 11g to 10g using Data Pump you need to export the schema/database VERSION parameter that matches the compatibility of the target database i.e. 10g

Oracle@Srv1>expdp \'/ AS SYSDBA\' schemas=SCOTT dumpfile= scottbkp.dmp logfile= scottbkp.log version=10.2.0

Scenario 2: User is trying to import a dumpfile using 11g Datapump utility which is generated by 10g Data pump utility.

Oracle@Srv1>impdp system/xxxxxx @oradb schemas=SCOTT dumpfile=scottbkp.dmp logfile=scottbkp.log remap_schema=SCOTT:SCOTTTEST

Import: Release 11.2.0.1.0 - Production on Wed Apr 17 13:05:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

UDI-00018: Data Pump client is incompatible with database version 10.2.0.4.0

Cause: User is trying use higher version of Datapump utility (11.2.01.1) to a lower version of the database (10.2.0.4)

Solution: Use the datapump client that matches with database version of your target database.

Refer below compatibility matrix
Overview of Data Pump dump file compatibility