April 26, 2016

EXPDP/IMPDP fails with ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037 in RAC

While doing import using impdp i am encountering the below issue.

Error Details:
*************
ORA-31693: Table data object "sivaram"."TITAN":"SYS_P20689" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "/u01/refresh/expdp_TABLES_First_Set_03.dmp" for read
ORA-19505: failed to identify file "/u01/refresh/expdp_TABLES_First_Set_03.dmp"
ORA-27037: unable to obtain file status

But i have checked the file status in  server level it is present. But impmort unble to identify the file status. Also checked the permissions on directory, it looks fine.

[oracle@prod02 refresh]$ ls -lhrt /u01/refresh/expdp_TABLES_First_Set_03.dmp
-rw-r----- 1 oracle dba 5.6G Apr 15 20:42 /u01/refresh/expdp_TABLES_First_Set_03.dmp

Parfile for Import:
*******************
$ more impdp_TABLES.par
directory=refresh
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

The problem is, impdp process is looking for the dump file on the other node in the cluster, eventhough i started from the local node only due to i mentioned parallel in the par file. Prior to 11.2, once you establish the connection to the database, an instance selected and the master process and all the worker process run on that instance only.
From 11.2, CLUSTER parameter introduced

CLUSTER=Y|N (Default: Y)
Purpose: Whether Datapump can use RAC resources and start workers on other instances in cluster.

Solution:
*********
1. Add the CLUSTER=N in parfile, so all the worker process will start in local instance only
2. Remove the parallel option in parfile

I have followed here 1st Solution. After changes done to the parfile import completed successfully.

Parfile After Changes:
***********************
$ more impdp_TABLES.par
directory=refresh
CLUSTER=N
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

Reference: MOS 1173765.1

Happy Learning :)

No comments:

Post a Comment