Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)
In this Document
Symptoms
Changes
Cause
Solution
References
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:
#> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME1>.dmp logfile=<LOG_NAME1>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAC\'\"
#> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME2>.dmp logfile=<LOG_NAME2>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAF\'\"
#> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME3>.dmp.logfile=<LOG_NAME3>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAG \'\"
Or, confirm the LOB corruption using original export:
#> exp <USER>/<PASSWORD> file=<DUMP_NAME1>.dmp log=<LOG_NAME1>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAC\'\"
#> exp <USER>/<PASSWORD> file=<DUMP_NAME2>.dmp log=<LOG_NAME2>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAF\'\"
#> exp <USER>/<PASSWORD> file=<DUMP_NAME3>.dmp log=<LOG_NAME3>.log tables=<TABLE_NAME> query=\"where rowid = \'AAEWBsAAGAAACewAAG\'\"
If any of the above export fails then the LOB corruption confirmed.
SOLUTION
o Restore and recover the LOB segment using physical backup.
- OR -
o Empty the affected LOBs using the UPDATE statement as mentioned in the Note 787004.1:
-- NOTE: for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB, e.g.:
update <TABLE_NAME>
set <LOB_COLUMN> = empty_blob()
where rowid in (select corrupted_rowid
from corrupted_lob_data);
commit;
o Perform the export excluding the corrupted rowids.
Using DataPump export:
#> expdp <USER>/<PASSWORD> directory=data_pump_dir dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.dmp.log tables=<TABLE_NAME> query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
Using original export:
#> exp <USER>/<PASSWORD> file=<DUMP_NAME>.dmp log=<LOG_NAME>.log tables=<TABLE_NAME> query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
- OR -
o Alternatively, you could use flashback query to solve the LOB corruption. However, details are beyond the scope of this article.
Note: If there is no corrupt lob data and the issue persists then rebuild the entire table to resolve the issue.