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)


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:

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):

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:

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:

so i ran this:

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
  Completed Objects: 4

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

sources: (thanks to user )

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;

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!