Wikipedia

Search results

ORA-1555





ORA-1555: SNAPSHOT TOO OLD DUE TO CORRUPTED LOBS

It's interesting, spotting the issue during DB/schema export backup will take quite a time :-), but if get to the point then there you go👉,  During schema(s) export or querying a table containing a clob column will give 'snapshot too old' error!


ORA-01555: snapshot too old: rollback segment number with name "" too small

Fix :

Usually the ORA-01555 error is related either to the undo retention and the undo guarantee parameters, or to an inadequate size of an undo tablespace.

But there is also another case when you’ll face the same error and modifying of undo parameters or even increasing an undo tablespace size won’t help you. It happens when a column of LOB datatype has corrupted row(s).

You have several options to fix the issue: reinsert, delete, exclude or empty corrupted LOBs.

TESTCASE :

In this test case I’ll try to perform a table export where there is one corrupted row of LOB data type.

This directory will be used for a table dump

SQL> create or replace directory EXPDIR as '/oracle/dumps';
  Directory created.

Here - starting the export of a table with a corrupted row of LOB data type.  

[oracle@akb ~] expdp \"/ as sysdba\" tables="AKISP"."TEXT_FOR_UK" directory=EXPDIR dumpfile=table.dmp logfile=table.log
Export: Release 12.2.0.1.0 - Production on Mon Jul 8 10:48:14 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/******** AS SYSDBA" tables=AKISP.TEXT_FOR_UK directory=EXPDIR 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 "AKISP"."TEXT_FOR_UK" 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/dumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Mon Feb 8 10:49:01 2020 elapsed 0 00:00:45

The undo retention parameter is big enough in my case and the retention guarantee is also set for the UNDO tablespace. The UNDO tablespace size is big enough too.

The reason why it happens in the AKISP.TEXT_FOR_UK table, which has corrupted row of LOB datatype

COL COLUMN_NAME FOR A15
COL DATA_TYPE FOR A15
SELECT COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE OWNER='AKISP' AND TABLE_NAME='TEXT_FOR_UK';

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

The next steps help you out to find out all corrupted rows in the table

a. Create a new temporary table for storing all rowids of the corrupted LOBs

SQL> CREATE TABLE CORRUPTED_ROWS (CORRUPTED_ROWID ROWID, ERROR_NUMBER NUMBER);
   Table created.

b. Run the following PL/SQL block to get a list of ROWID(s) for all corrupted rows in the table. It would take a while as it reads 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 AKISP.TEXT_FOR_UK)
   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;
/

NOTE: Modify this piece of code for your needs

(SELECT ROWID, TEXT FROM AKISP.TEXT_FOR_UK)
as
(SELECT ROWID, YOUR_LOB_COLUMN FROM YOUR_OWNER.YOUR_TABLE)

c. When step b is completed, query found corrupted ROWID(s) from the table of step a.

SQL> SELECT * FROM CORRUPTED_ROWS;

CORRUPTED_ROWID    ERROR_NUMBER
------------------ ------------
AKWJ9AAGAAPJTWDEC        1555


d. Now if you query rows with ROWID(s) from step c you can easily confirm existence of ORA-01555. The error arises when a corrupted row is queried.

-- Without the LOB column (TEXT)

SQL> SELECT DOCUMENT_ID FROM AKISP.TEXT_FOR_UK WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);

DOCUMENT_ID
-----------
  900061435

-- With the LOB column (TEXT)

SQL> SELECT DOCUMENT_ID, TEXT FROM AKISP.TEXT_FOR_UK 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


Next, If you exclude all corrupted ROWID(s) from an export then it becomes possible to perform a new export successfully. Use the QUERY option of the datapump utility to exclude all unwanted rows.

[oracle@akb ~] expdp \"/ as sysdba\" tables="AKISP"."TEXT_FOR_UK" directory=EXPDIR dumpfile=table.dmp logfile=table.log QUERY=\"WHERE rowid NOT IN \(SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS\)\"

Export: Release 12.2.0.2.0 - Production on Mon Feb 8 11:37:24 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Release 12.2.0.2.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=AKISP.TEXT_FOR_UK directory=EXPDIR 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 "AKISP"."TEXT_FOR_UK"                     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/dumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 8 11:38:05 2020 elapsed 0 00:00:40


REFERENCES

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)
Posted by at
Labels ,
Location: Coral Sea

1 comment:

Subscribe to: Post Comments (Atom)