ORA-39166: Object SYS.DEMO_DUP was not found.-ORA-31694: master table “SYS”.”SYS_EXPORT_TABLE_05″ failed to load/unload-ORA-31617: unable to open dump file “/home/oracle/expdp/demo_sales_02.dmp” for write-ORA-19505: failed to identify file “/home/oracle/expdp/demo_sales_02.dmp”-ORA-27037: unable to obtain file status-Linux-x86_64 Error: 2: No such file or directoryAdditional information: 3

Cause:

EXPDP fails if i use parallelism and enable cluster parameter as cluster=Y why?

[oracle@exdbadm01 ~]$ time expdp \'/ as sysdba\' directory=EDP dumpfile=demo_sales_%U.dmp logfile=demo_sales.log tables=demo_dup,test.sales_tab cluster=Y access_method=direct_path parallel=2

Export: Release 11.2.0.4.0 - Production on Wed Apr 7 19:28:46 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_05":  "/******** AS SYSDBA" directory=EDP dumpfile=demo_sales_%U.dmp logfile=demo_sales.log tables=demo_dup,test.sales_tab cluster=Y access_method=direct_path parallel=2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 96 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "TEST"."SALES_TAB" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/home/oracle/expdp/demo_sales_02.dmp" for write
ORA-19505: failed to identify file "/home/oracle/expdp/demo_sales_02.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-39166: Object SYS.DEMO_DUP was not found.
ORA-31694: master table "SYS"."SYS_EXPORT_TABLE_05" failed to load/unload
ORA-31617: unable to open dump file "/home/oracle/expdp/demo_sales_02.dmp" for write
ORA-19505: failed to identify file "/home/oracle/expdp/demo_sales_02.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19505: failed to identify file "/home/oracle/expdp/demo_sales_02.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Job "SYS"."SYS_EXPORT_TABLE_05" stopped due to fatal error at Wed Apr 7 19:29:10 2021 elapsed 0 00:00:22

Workaround:

I have two node RAC and both nodes have same directory structure. But in 11g version of expdp, by default cluster parameter is set to ‘Y’ if parallel server is set to a value of parallel=N where N=1,2,3……and also identified as a bug.

Specify cluster=N and try export

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s