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

ORA-31693 ORA-02354 ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small in expdp

Problem: Processing object type DATABASE_EXPORT/AUDIT
ORA-31693: Table data object "MICR"."OUTWDCLR" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small

select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER='MICR' and TABLE_NAME='OUTWDCLR';

COLUMN_NAME SECUREFILE PCTVERSION RETENTION

IMAGE_FRONT NO 10
IMAGE_REAR NO 10


SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>


SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
            3291

SQL>

We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION,
but the maxquery length is 3291 seconds.


When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

Solution:::

 1. Modify the current UNDO_RETENTION for the database:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 4500 scope=both sid='*';

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_FRONT) (retention);
Table altered.

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_REAR) (retention);
Table altered.


3. Query the lob retention again to verify that the change has taken hold:
SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;

COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
IMAGE_FRONT                NO                  4500
IMAGE_REAR                 NO                  4500

4. Perform the export again.

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 Schema in Oracle

Note: Backup taken in Oracle 10g

exp system/@STLBAS owner=FIUINT file=/home/oracle/fiuint.dmp STATISTICS=NONE

Note: Backup restore in Oracle 11g

imp system/ file=/home/oracle/fiuint.dmp fromuser=FIUINT touser=FIUINT