Check and recover corruption of LOB Segments in Oracle

Check and recover corruption of LOB Segments in Oracle

Check corruption in LOB Segments

1. Create a test table for storing all rowids of the corrupted LOBs

create table corrupt_lobs (corrupt_rowid rowid, err_num number);

2. Find the column name which having LOB segment like CLOB or BLOB columns.

Desc table_name;

Note: identified the column having type LOB columns.

3. Execute the Script for check the corrupted lob with replace tablename and lob column name type.
Note: if table has more than one LOB columns then execute for each column

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);
num number;
for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
end loop;

Put value as example:
Enter value for lob_column : EMP_XML
Enter value for table_owner : SCOTT
Enter value for table_with_LOB: EMP

4. After you execute the scripts, check the corrupted lobs:

select * from corrupt_lobs;

1. If you finds rows then it has corruption
2. if no row found then no corruption in this column of table.

Fixed the corruption if identified

1. If you have physical backup then restore and recover it for fixed the corruption.

2. If donot have backup and empty the identified row which causing problem in SELECT Query.
Note: You have rowid in corrupted_lobs table, update the table corrupted lobs with empty_blob() function.
Note: BLOB and BFILE columns use EMPTY_BLOB() and CLOB and NCLOB columns use EMPTY_CLOB()

-- Empty the affected LOBs using the UPDATE statement
update .
set = empty_blob()
where rowid in (select corrupted_rowid from corrupt_lobs);

3. Export the table with Query option where rowid not in corrupted row rowids.

expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP
query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"

4. If you have standby server then detele the corrupted rows and try to recover from standby database.

1 thought on “Check and recover corruption of LOB Segments in Oracle

  1. Pingback: Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.