Last week I came across an interesting problem in one 10gR2 10.2.0.4 database instance on AIX platform. I have a daily full datapump export which is running for last one year without any errors. But when I tried to export a couple of tables from one schema, I got ORA-39014 error. Here is the full error message.
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
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.
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 14:05:45
ORA-39014: One or more workers have prematurely exited.
My export par file was as follows
$ cat special_dp_export_pjda1_tables.par
userid=/
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT
And datapump export command was
$ expdp parfle=special_dp_export_pjda1_tables.par
I did some Metalink search and found out that this is a bug in 10.2.0.4 and their solution was to upgrade to 11g which is not possible for me to do due to production nature of this instance.
I was able to have a successful datapump export by modifying my par file as follows
userid='/ as sysdba'
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT
In the beginning I mentioned that I have a daily full datapump export which is running without any error for last one year with userid=/ and not with userid='/ as sysdba'. What a mystery. I was also able to reproduce this error in another 10.2.0.4 Oracle instance. Does anyone has any answer why I don’t get this error with full export?
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
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.
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 14:05:45
ORA-39014: One or more workers have prematurely exited.
My export par file was as follows
$ cat special_dp_export_pjda1_tables.par
userid=/
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT
And datapump export command was
$ expdp parfle=special_dp_export_pjda1_tables.par
I did some Metalink search and found out that this is a bug in 10.2.0.4 and their solution was to upgrade to 11g which is not possible for me to do due to production nature of this instance.
I was able to have a successful datapump export by modifying my par file as follows
userid='/ as sysdba'
DIRECTORY=EXPDP_DIR
DUMPFILE=special_dp_export_pjda1_tables_02082011.dmp
LOGFILE=special_dp_export_pjda1_tables_02082011.log
TABLES=STSC.ITEM,
STSC.DMDUNIT
In the beginning I mentioned that I have a daily full datapump export which is running without any error for last one year with userid=/ and not with userid='/ as sysdba'. What a mystery. I was also able to reproduce this error in another 10.2.0.4 Oracle instance. Does anyone has any answer why I don’t get this error with full export?