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.

Import (IMPDP) Oracle Database backup from network location

Scenario:

Suppose we want to restore backup into destination database server (192.168.10.6) and backup resides in source database server (192.168.10.32)

Solution:

Step1: We need to keep the backup dump file into source database server (192.168.10.32)

Step2: We need to create a database link into source database server (192.168.10.32)

CREATE PUBLIC DATABASE LINK DPUMP
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.6)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';

Step3: Now we need to create a directory where database backup will reside 

DROP DIRECTORY data_dump;
CREATE OR REPLACE DIRECTORY data_dump AS '/back/exp/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.data_dump TO SYSTEM WITH GRANT OPTION;

Step4: Now we need to execute import (impdp) command from source database server (192.168.10.32) to restore
       backup into destination database server (192.168.10.6)

Step5: Put the schemas value which schema you want to import

impdp system/sys123 schemas=scott,hr,test network_link=DPUMP directory=data_dump dumpfile=orcl_full.dmp logfile=orcl_full.log    


  
Step6: Now the data values that you want to import..

Cheers.....