ORA-01555: SNAPSHOT TOO OLD ERROR BECAUSE OF LOB CORRUPTION
When attempting to export tables that contain lobs, the following errors occur:
Usually, the ORA-01555 error is related to both the undo retention, and the undo guarantee parameters. Or when an undo tablespace is inadequately sized. But the cause of this error could be a LOB corruption as well.
To fix the ORA-01555 error when it relates to a LOB corruption you have to identify the corrupted rows and reinsert, or delete, or exclude, or empty them.
In this demo, I perform a table export where there is one corrupted row of LOBORA-01555 error.
[oracle@dbpilot ~] expdp \"/ as sysdba\" tables="ISUGDP"."TEXT_FOR_ECP" directory=DROPZONE dumpfile=table.dmp
logfile=table.log
Export: Release 11.2.0.4.0 - Production on Mon Feb 5 10:48:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "sys/******** AS SYSDBA" tables=ISUGDP.TEXT_FOR_ECP directory=DROPZONE
dumpfile=table.dmp logfile=table.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
ORA-31693: Table data object "ISUGDP"."TEXT_FOR_ECP" 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 with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/oradumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Mon Feb 5 10:49:01 2018 elapsed 0 00:00:45
For instance, my table export completed with the following error
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
The reason for this error is not related to UNDO at all. It happened because the "ISUGDP"."TEXT_FOR_ECP" table has a corrupted row containing LOB datatype.
COL COLUMN_NAME FOR A15
COL DATA_TYPE FOR A15
SELECT COLUMN_NAME, DATA_TYPE
FROM DBA_TAB_COLUMNS
WHERE OWNER='ISUGDP' AND TABLE_NAME='TEXT_FOR_ECP';
COLUMN_NAME DATA_TYPE
--------------- ---------------
DOCUMENT_ID NUMBER
USER_ID NUMBER
USER_NAME VARCHAR2
REQUEST_ID NUMBER
CREATED DATE
MODIFIED DATE
IS_REPLICON NUMBER
TEXT CLOB <== This column
a. Create a new temporary table for storing all rowids of the corrupted rows
SQL> CREATE TABLE CORRUPTED_ROWS (CORRUPTED_ROWID ROWID, ERROR_NUMBER NUMBER);
Table created.
b. The following PL/SQL block identifies all corrupted rows in a table and adds them to the table created in step a. It might take a while as it scans the whole table.
SET TIMING ON
DECLARE
ERROR_1578 EXCEPTION;
ERROR_1555 EXCEPTION;
ERROR_22922 EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_1578, -1578);
PRAGMA EXCEPTION_INIT(ERROR_1555, -1555);
PRAGMA EXCEPTION_INIT(ERROR_22922, -22922);
N NUMBER;
BEGIN
FOR ROW IN (SELECT ROWID, TEXT FROM ISUGDP.TEXT_FOR_ECP)
LOOP
BEGIN
N:=DBMS_LOB.INSTR(ROW.TEXT, HEXTORAW('889911'));
EXCEPTION
WHEN ERROR_1578 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1578);
COMMIT;
WHEN ERROR_1555 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1555);
COMMIT;
WHEN ERROR_22922 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 22922);
COMMIT;
END;
END LOOP;
END;
/
Adjust a table name for your needs
c. When step b is completed, query the CORRUPTED_ROWS table to list all corrupted ROWIDs.
SQL> SELECT * FROM CORRUPTED_ROWS;
CORRUPTED_ROWID ERROR_NUMBER
------------------ ------------
AAAWJ9AAGAAPJTWAAJ 1555
d. If you query rows found in step c you can easily confirm the existence of ORA-01555 error. The error arises when a row with a corrupted LOB is touched.
-- Querying corrupted rows excluding LOB column
SQL> SELECT DOCUMENT_ID FROM ISUGDP.TEXT_FOR_ECP WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);
DOCUMENT_ID
-----------
700061427
-- Querying corrupted rows including LOB column
SQL> SELECT DOCUMENT_ID, TEXT FROM ISUGDP.TEXT_FOR_ECP WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);
ERROR:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Retry a table export excluding corrupted rows. It will complete successfully.
[oracle@dbpilot ~] expdp \"/ as sysdba\" tables="ISUGDP"."TEXT_FOR_ECP" directory=DROPZONE dumpfile=table.dmp
logfile=table.log QUERY=\"WHERE rowid NOT IN \(SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS\)\"
Export: Release 11.2.0.4.0 - Production on Mon Feb 5 11:37:24 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=ISUGDP.TEXT_FOR_ECP directory=DROPZONE
dumpfile=table.dmp logfile=table.log QUERY="WHERE rowid NOT IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS)"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "ISUGDP"."TEXT_FOR_ECP" 4.489 GB 1368214 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/oradumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 5 11:38:05 2018 elapsed 0 00:00:40
LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)
Tags In
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Sessions
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum