Tuesday, September 22, 2015

Oracle Impdp Failed on RAC with ORA-31640, ORA-19505, ORA-27037, Linux-x86_64 Error: 2: No such file or directory

We were doing a import on 3 node RAC (exadata)using IMPDP.We obviously used prallelism, but faced below error.First look indicated that its related to OS/Filesystem. But its related to how impdp worked on a RAC node.

Additional information: 3
ORA-31693: Table data object "ORACLE_ORASDPM"."MESSAGE" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/oracle/oracle_soa_schemas_exp.dmp" for read
ORA-19505: failed to identify file "/oracle/oracle_soa_schemas_exp.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Cause:
When you are using a cluster database, it is possible for datapump workers to start on any of the available nodes when using parallelism. In our case dump file was local to node1, so node2 and 3 were unable to access it. If the nodes that start the workers do not have access to the datapump directory they will be unable to process the files.

Solution:
Impdp seems to have option "Cluster=N" .
This will remove the other nodes from the impdp process.