Monday, January 5, 2009

Data Pump Import fails with ORA-39000 and ORA-39140.

Error Description:
---------------------------------------------------

While I gave multiple file name in the dumpfile parameter of expdp it raised error ORA-39000 and ORA-39140.
$impdp prod16k/thword16k directory=d dumpfile=dump.dmp,dump2.dmp,dump3.dmp
Import: Release 10.2.0.2.0 - Production on Sunday, 13 July, 2008 1:56:14

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39140: dump file "/backup1/dump2.dmp" belongs to job "PROD72"."SYS_EXPORT_TABLE_02"

Cause of The Problem:
------------------------------------------

The error happened as data pump can't import several files at a time if they don't produce from a single export job. The files are produced by multiple export operation. At a time we can import dumps produced from a single export operation.

We can get the cause of the error from OS using oerr as follows,
bash-3.00$ oerr ora 39140
39140, 00000, "dump file \"%s\" belongs to job %s"
// *Cause: When a dump file set consists of multiple files, all files
// in the set must be specified for an import operation, and all
// files must have been produced by the same export job.
// One of the files provided does not belong to the original dump
// file set. For instance, it was created by a different export job
// than the other files.
// *Action: Remove the dump file indicated in the message and retry the
// import operation providing only the complete set of
// dump files created by a specific export job.

Solution of The Problem:
-----------------------------------

Remove the file name from dumpfile parameter if they are not created from a single export operation. If both of above three dumps are generated from three different export operation then to import it invoke three separate operation like,

$impdp prod16k/thword16k directory=d dumpfile=dump.dmp
$impdp prod16k/thword16k directory=d dumpfile=dump3.dmp
$impdp prod16k/thword16k directory=d dumpfile=dump2.dmp

No comments:

Post a Comment