导出失败,并显示错误ORA-2354 ORA-1555 ORA-22924,以及如何使用导出实用程序确认LOB段损坏

APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Following errors encountered during export:

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old
 

CHANGES
 

CAUSE
LOB segment corruption.

How To Confirm LOB Segment Corruption Using Export Utility

- Identify the table rowid's referencing the corrupted LOB segment blocks by running the PL/SQL procedure mentioned in the Note 787004.1.

As there is already a similar PL/SQL procedure indicated in various notes such as: Note 452341.1 or Note 253131.1, the reason for this article is to propose a method to confirm the LOB segment corruption using export utility by exporting the corrupted rowids.

For example:

If you encounter the error “ORA-1555: snapshot too old: rollback segment number with name " " too small” during export of table (<TABLE_NAME>) with a LOB column (<LOB_COLUMN>), then run the PL/SQL procedure mentioned in the Note 787004.1.

Note: Run the PL/SQL procedure after creating a temporary table “CORRUPTED_LOB_DATA”. This table is used to spool the corrupted rowids.

create table corrupted_lob_data (corrupted_rowid rowid);

set concat off

declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
num number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/ 

After running the above procedure, it prompts for:

Enter value for lob_column    : <LOB_COLUMN>
Enter value for table_owner   : <SCHEMA_NAME>
Enter value for table_with_LOB: <TABLE_NAME>
Like this, we can check the corruption in all the LOB columns.

In this example, the output of the table “CORRUPTED_LOB_DATA” is showing three rowid’s referencing the corrupted lob segment

select * from corrupted_lob_data;

CORRUPTED_ROWID
---------------------
AAEWBsAAGAAACewAAC
AAEWBsAAGAAACewAAF
AAEWBsAAGAAACewAAG

3 rows selected

Confirm the LOB corruption using Datapump:

Leave a Reply