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?
Even though, mine is very late reply may be it would help for others who visit your blog for solution. For them this can help, I would suggest to execute by removing the schema name in Tables.
ReplyDeleteTABLES=ITEM,DMDUNIT and add schema parameter as SCHEMA=STSC.
This might do the work successfully.
Thanks Madhu.. I was using system for backup. Used sys as sysdba instead. And it worked.
ReplyDelete