More

    Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No LOB Corruption

    While exporting a LOB table, we often face issue of undo_retention as follows.

     ORA-31693: Table data object ""."" 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 <segment_number> with name "" too small

    Make sure to check the LOB is not corrupted by following the steps from Note:452341.1 or Note:787004.1 before following the below steps. If LOB is corrupted(logical corruption) you have to re-create the table.

    REASON:

    The issue occurs because of incorrect setting of LOB table.

    We can verify the same using following command.

    SQL> show parameter undo;
     NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 900
    undo_tablespace string UNDOTBS1
     
    SQL> select max(maxquerylen) from v$undostat;
     MAX(MAXQUERYLEN)
    ----------------
    16331
    
    SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>' and table_name='<TABLE_NAME>';
     RETENTION
    ----------
    900
    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 16331 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:

    Set a large enough value for undo_retention parameter using below steps.

    1. set the UNDO_RETENTION to 16500 using following command.
    SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';
    Note: For versions =< 10.2.0.5, an instance restart is required because LOB retention does not change when undo_retention is changed. See further details from Document 563470.1 which shows that retention is not changed until after a restart.

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

    SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (pctversion 5);
    Table altered.
    SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (retention);
    Table altered.

    3. Query the lob retention again to verify that the change has taken hold:

    SQL> select retention from dba_lobs where owner='<SCHEMA_NAME>’ and table_name='<TABLE_NAME>’;

    RETENTION
    ———-
    16500

    4. Perform the datapump export again.

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox