IMPDP Error : RAC Environment Fails With Errors ORA-29913 ORA-31640 ORA-19505 ORA-27037

Scenario Preview : 

Recently we faced an error during impdp operation  migration of multiple schema from standalone database to 2 node RAC database. We face some issue on some of the tables of particular schemas but . Same script used for standalone DB were working  fine also other schema of using same expdp dumpfile. 

Reason was instead using TAF services that we have registered. We  were doing impdp on particular instance services level.

Error: ORA-31693 | ORA-31640 | ORA-19505

Scenario Error: 

After troubleshooting we found we were using parallel option  parallel=3 during  import   

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Command used during import 

oracle@01:/u04/MasterDB/expdp/expdp_smrprod> impdp \"sys/xxx@SMRPROD2 as sysdba\" dumpfile=EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp directory=EXPDPPROD logfile=impdpSMRPRODB-$(date +%Y-%m-%d_%H-%M-%S).log schemas=infodba JOB_NAME=FULL_EXPDP_SMRPRODB parallel=3

Import: Release 12.1.0.2.0 - Production on Sun Nov 8 09:34:11 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

Master table "SYS"."FULL_EXPDP_SMRPRODB" successfully loaded/unloaded

Starting "SYS"."FULL_EXPDP_SMRPRODB":  "sys/********@SMRPROD2 AS SYSDBA" dumpfile=EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp directory=EXPDPPROD logfile=impdpSMRPRODB-2020-11-08_09-34-11.log schemas=infodba JOB_NAME=FULL_EXPDP_SMRPRODB parallel=3

Processing object type DATABASE_EXPORT/SCHEMA/USER

ORA-31684: Object type USER:"INFODBA" already exists

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

. . imported "INFODBA"."POM_BACKPOINTER"                 605.0 MB 14101046 rows

. . imported "INFODBA"."PIMANFILE"                       266.9 MB 1335204 rows

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

. . imported "INFODBA"."PFND0WORKFLOWAUDIT"              855.8 MB 1759632 rows

. . imported "INFODBA"."PPSOCCURRENCE"                   147.2 MB  588268 rows

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

. . imported "INFODBA"."PWORKSPACEOBJECT"                256.4 MB 1802823 rows

. . imported "INFODBA"."PFND0GENERALAUDIT"               110.8 MB  491362 rows

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

ORA-31640: unable to open dump file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp" for read

ORA-19505: failed to identify file "/u04/MasterDB/expdp/expdp_smrprod/EXPDP-SMRPRODB-2020-11-08_09-02-28.dmp"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

. . imported "INFODBA"."PEPMTASK"                        64.87 MB  331746 rows

. . imported "INFODBA"."PFND0SYNC_CHECKSUMS_3"           66.24 MB  887437 rows

. . imported "INFODBA"."PREF_LIST_0"                     30.97 MB  805308 rows

. . imported "INFODBA"."PPROJECT_LIST"                   28.16 MB  736249 rows

. . imported "INFODBA"."PFND0SECURITYAUDIT"              25.59 MB   85555 rows

. . imported "INFODBA"."PPROPAGATION_OBJ_LIST"           28.12 MB  729337 rows

. . imported "INFODBA"."PREVISIONS"                      21.79 MB  562331 rows

. . imported "INFODBA"."PREF_NAMES_0"                    25.36 MB  805308 rows

. . imported "INFODBA"."PREF_TYPES"                      18.68 MB  805308 rows

^C

Import> stop_job

Are you sure you wish to stop this job ([yes]/no): yes

Solution:

DataPump Import With PARALLEL > 1 In RAC Environment Fails With Errors ORA-29913 ORA-31640 ORA-19505 ORA-27037 (Doc ID 1173765.1)

Use cluster=N to run the import from a single node of the RAC cluster which has file access.

or

Run the Data Pump import job with parallel=1 (default)