Oracle 11.2 Datapump ORA-31617, ORA-19505, ORA-27037

On a RAC setup, when I was performing an expdp export, it error out with following stack

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp schemas=test parallel=4

Multiple errors similar to following

ORA-31693: Table data object "TEST"."TEST_15" failed to load/unload 
and is being skipped due to error: 
ORA-31617: unable to open dump file "/datadir/temp_expdp
/TEST_test_03.dmp" for write 
ORA-19505: failed to identify file "/datadir/temp_expdp
/TEST_test_03.dmp"
 ORA-27037: unable to obtain file status

Then after some investigation found that, the directory (DBA_DIRECTORY physical path) is missing from one of the nodes.

So tried with another CLI, where I connected the using a service, which is only available on one node of the cluster

expdp user/pwd@node1svr  dumpfile=test_%u.dmp logfile=test.log 
directory=temp_expdp schemas=test parallel=4

But datapump export failed again with the same error

Again executed the CLI & checked the session details from gv$session view.

       SID MACHINE      INST_ID

———- ————————————   ———–      

1029    rac-srv-01oracle@rac-srv-01 (DM00)     1        

21        rac-srv-01oracle@rac-srv-01 (DW02)     1      

1534   rac-srv-01oracle@rac-srv-01 (DW00)     1        

18       rac-srv-02oracle@rac-srv-02 (DW00)     2      

521     rac-srv-02oracle@rac-srv-02 (DW01)     2

 

This output shows that, connections are distributed to both the nodes, hence datapump error out.

So what are the options

1. Create a directory object on clustered file system

2. Export without using parallel clause

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp 
schemas=test

3. Use CLUSTER option in expdp CLI

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp 
schemas=test cluster=n

This option will to constrain the job to run on the instance where it is started