workaround for oracle impdp ORA-39014 ORA-39029 ORA-31672 prematurely exited/terminated

i've been trying for the last 24 hours to import a dump/dmp file created with expdp in oracle 10g on ubuntu 8 into a new server on ubuntu 11.10 with oracle 11g enterprise, using this import statement:

oracle@mydbserver:/u01/app/oracle/product/11.1.0/db_1/bin$ ./impdp scott@MYDB PARFILE=/home/me/Downloads/params.txt

but it would run for a little while then throw this error:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

when i increased the PARALLEL number (e.g. to 4) it got past a place it was stuck at, and got somewhat further.


params.txt looks like this:

DIRECTORY=data_pump_dir
DUMPFILE=expdat_scott_20120216.dmp
PARALLEL=4
TABLE_EXISTS_ACTION=REPLACE

another workaround is to import tables one by one using the INCLUDE parameter, so e.g. add this line to the param file (i.e. params.txt):

INCLUDE=TABLE:"IN ('TABLE1')"

then simply rerun the impdp command (while in the bin directory):

./impdp scott@MYDB PARFILE=/home/me/Downloads/params.txt

if somehow you know there is a particular table that is causing trouble you can use the EXCLUDE parameter instead and simply exclude that one table:

EXCLUDE=TABLE:"IN ('MY_CORRUPT_TABLE1')"

our dmp file was made while the database was running and thus one of the tables got corrupted, or was exported in the middle of a process so that's why we had problems importing it into the new database. i found this out by doing this:

cat /u01/app/oracle/admin/mydb/dpdump/import.log

which showed me the name of the import job i was running:

SCOTT.SYS_IMPORT_FULL_01

so i ran this:

./impdp ATTACH=SCOTT.SYS_IMPORT_FULL_01

and it showed me which table was the last table to be attempted imported:

Worker 1 Status:
  Process Name: DW01
  State: UNDEFINED                    
  Object Schema: SCOTT
  Object Name: MY_CORRUPT_TABLE1
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 4

so i knew i should exclude that table in the import.


sources:
https://forums.oracle.com/forums/thread.jspa?threadID=907984 (thanks to user https://forums.oracle.com/forums/profile.jspa?userID=696918 )


p.s. in some previous desperate attempts to get things to work (prior to the instructions above), i also ran the following as sys:

alter system set open_cursors=1024 scope=spfile;
alter system set "_optimizer_cost_based_transformation"=off;
commit;

i cant say for sure whether this was necessary or not. i'm sorry, i know, that sucks, but if the above fails then consider running these alter statements, then trying again.