The best place to ask programming/development questions, imo. UPDATE: stackoverflow is the *worst* place to *ask* questions (if your first question/comment doesn't get any up-rating/response, then u can't ask anymore questions--ridiculously unrealistic), but a great reference for finding answers.

My Music (Nickleus)

20120221

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.

1 comment:

  1. Thank you, I have increased parallel from 2 to 8 and now works!

    ReplyDelete