Expdp fails with ORA-31693, ORA-01555

Sometimes, expdp export will fail with following error stack.

SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 
ORA-31693: Table data object "SCOTT"."TAB5" 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 7 with name
 "_SYSSMU7_601426619$" too small

Normally this error is seen in two scenarios.

  •  LOB corruption
  •  Export when LOB handlers are still open

To check the problem, we can use the script provided by metalink [ID 787004.1]

After using the script, corrupt_lobs (or table used in the script) will have the ROWID’s of problem rows.

SQL> select count(*) from  SCOTT.corrupt_lobs;   
COUNT(*) 
----------         
 2

SQL> select * from   SCOTT.corrupt_lobs; 
CORRUPT_ROWID
------------------ 
AAAbMPAAIAAL5xzAAN 
AAAbMPAAIAAL5xzAAO

Now to check if these rows are corrupt or the LOB’s are erroring out due to open handlers, we can use expdp to export rowid’s returned in above select .

expdp  directory=SCOTT_DATAPUMP dumpfile=test_blob_corr.dmp 
logfile=test_blob_corr.log tables=SCOTT.TAB5 
query=\"where rowid in \(\'AAAbMPAAIAAL5xzAAN\',
\'AAAbMPAAIAAL5xzAAO\'\)\" 
Export: Release 11.2.0.3.0 - Production on Wed Jul 18 03:34:37 2012 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  
All rights reserved. Username: / as sysdba Connected to: 
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 
64bit Production With the Partitioning,
Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA 
directory=SCOTT_DATAPUMP dumpfile=test_blob_corr.dmp 
logfile=test_blob_corr.log tables=SCOTT.TAB5 query="where 
rowid in ('AAAbMPAAIAAL5xzAAN','AAAbMPAAIAAL5xzAAO')" 

Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 61.02 GB Processing 
object type TABLE_EXPORT/TABLE/TABLE Processing object type 
TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
.............
.............
.............
. . exported "SCOTT"."TAB5"                         196.4 KB       
2 rows Master table "SYS"."SYS_EXPORT_TABLE_01" 
successfully loaded/unloaded 
******************************************************************
 Dump file set for SYS.SYS_EXPORT_TABLE_01 is:  
 /u02/oradata/SCOTT/exp/test_blob_corr.dmp

If the rows are successfully exported, then there is no corruption.

But if they can’t be exported, then we either need to empty LOB column or delete the row. Later we can re-insert from backup.

NOTE: Before deleting or emptying the LOB, we should run the script in metalink note couple of time.  Each time it should return same ROWID’s.

Advertisements
This entry was posted in Oracle Data Pump and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s