LOB大对象导出报错ORA-01555,ora-600[ktsplbfmb-dblfree]的解决

郑全

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.

For more information, please refer to http://docs.oracle.com/cd/B28359 ... shback.htm#i1008579

Note: If there is no corrupt lob data and the issue persists then rebuild the entire table to resolve the issue.