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

Error:

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

Reason: 

The error is showing due to the backup dump file is not accessible from the the second node in the RAC


Solution:

Following example will help to resolve the issue.

expdp username/passwd tables=schema.table dumpfile=backup.dmp logfile=backup.log cluster=N

or

Shutdown Database on second node and execute the following command


expdp username/passwd tables=schema.table dumpfile=backup.dmp logfile=backup.lo

How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When Performing DataPump Export/Import

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Information in this document applies to any platform.

GOAL:

This article documents a resolution for errors ORA-39000, ORA-31640 and ORA-27037 when performing DataPump export/import. DataPump Import can fail with the following errors:

Import: Release 10.2.0.1.0 - Production on Friday, 30 January, 2009 15:10:33
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/oracle/u01/app/oracle/oracle/product/10.2.0/db_2/admin
/dpdump/expdat.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SOLUTION:

The parameter DIRECTORY specifies the location to which the DataPump Export or Import  is not properly created or having permission issues.
Drop  and re-create a new oracle directory object and change the expdp/impdp command to point to the new directory to resolve this issue.  You must have the DBA Privilege to create a Directory.
For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:

SQL> drop directory expdp_dir;
SQL> create directory expdp_dir as '/u01/backup/exports';
Then grant read and write permissions to the users who will be performing the data pump export and import.
SQL> grant read, write on directory expdp_dir to system, user1, user2, user3;

REFERENCES:  Doc ID 784566.1

DataPump Import (IMPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31640 ORA-27037

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.


ERROR SYMPTOMS:

DataPump import fails with the following errors:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/bkups/exports/EXPORT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

The parameters used are:

userid=system/
DIRECTORY=my_dir
DUMPFILE=EXPORT.dmp
LOGFILE=my_logdir:EXPORT.log
CHANGES

DataPump export with parameters:
userid=username/
DIRECTORY=my_dir
DUMPFILE=EXPORT.DMP
LOGFILE=EXPORT.log
content=metadata_only
VERSION=10.2.0

was successful:
Master table "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPORT.SYS_EXPORT_SCHEMA_01 is:
/spare/clone/EXPORT.DMP
Job "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:41:12

CAUSE:

At first glance, this appears to be an exact match to the note 784566.1 How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When
Performing Data Pump Export/Import

If you have already read that note, tried the solution and are still getting the errors, then the problem may be with the actual
export.dmp file.

In this case, as you can see from the export par file and import par file the name of the export dump is not the same:

Export: DUMPFILE=EXPORT.DMP
Import: DUMPFILE=EXPORT.dmp

SOLUTION:

Once the import parameter was changed to DUMPFILE=EXPORT.DMP the import completes successfully.


Reference : Doc ID 1228194.1

Export/Import specific tablespace using Data Pump in Oracle 10g Database

1.  Tablespace Export

expdp system/ TABLESPACES=USERS,UNDOTBS1 directory=DATA_PUMP_DIR dumpfile=test.dmp LOGFILE=exp.log    parallel=2  

In the above example, expdp takes a backup of the contents of USERS,UNDOTBS1. The expdp also runs quickly because of the parallel=2 option (provided there are more CPUs available in the database server). 

2. Transportable tablespace

expdp system/ transport_tablespaces=test_user_tbs transport_full_check=y directory= DATA_PUMP_DIR dumpfile=test.dmp logfile=exp.log

Transportable tablespaces export and import is manageable across platforms and only Meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that

1. source and target database must use the same character set/national character set
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.

Transportable tablespace export and import on same endian platforms 
Step 1: Find the Operating system byte order on Source and Target Database
SQL > select * from v$transportable_platform order by platform_id;

1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little

18 IBM Power Based Linux Big


3. Tablespace Import

impdp system/ TABLESPACES=USERS directory=DATA_PUMP_DIR dumpfile=test.dmp LOGFILE=imp.log

Above example imports all tables that have data in tablespaces USERS and it assumes that the tablespaces already exist.