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

ORACLE 11.2. Datapump ORA-31617 ORA-19505 ORA-27037

Scenario:

In RAC, when you start export with PARALLEL parameter by using SCAN-NAME as connection string then oracle tries to connect to both the nodes and tries to create/find dump files to both nodes.

After starting export, the dump files get created on the node from where the export command was executed but the connection which has been made with second node after some time and tries to find the file to update, then we see this error.

ORA-31693: Table data object "TRIP"."TRIP_15" failed to load/unload and is being skipped due to error:

ORA-31617: unable to open dump file "/u01/expdp/TRIP_test_03.dmp" for write

ORA-19505: failed to identify file "/u01/expdp/TRIP_test_03.dmp"

ORA-27037: unable to obtain file status

To troubleshoot this error, execute SELECT on gv$session view:

SID       MACHINE                                INST_ID

------  ---------------------------------------  --------

125     rac-01oracle@rac-01 (DM00)              1  

24      rac-01oracle@rac-01 (DW02)              1   

134     rac-01oracle@rac-01 (DW00)              1   

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

2252    rac-02oracle@rac-02 (DW01)              2    

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

There are below three options to avoid this error:

1. Create a directory object on clustered file system

2. Export without using parallel clause.

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

3. Use CLUSTER option in expdp CLI. This option will to constrain the job to run on the instance where it is started.

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