Home Oracle DB Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No...

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

0
77

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.