How can we find the corrupted object list?
One of my database, blocks are corrupted few months ago.
At that time i have written a dynamic script for each block id & find the corrupted block list.
Query in the article:
Solutions:
1.If corruption occurred in temporary segments, kindly ignore the block corruption. (Block corruption in temporary segment, everyday block id will change)
2.Corrupted blocks in index segments Drop & recreate the index segment
3.Using DBMS_REPAIR to Repair Data Block Corruption fix_corrupt_blocks procedure
a.CHECK_OBJECT – Detects and reports corruptions in a table or index
b.FIX_CORRUPT_BLOCKS – Marks blocks (that were previously identified by the CHECK_OBJECT procedure) as software corrupt
c.DUMP_ORPHAN_KEYS – Reports index entries (into an orphan key table) that point to rows in corrupt data blocks
d.REBUILD_FREELISTS – Rebuilds the free lists of the object
e.SEGMENT_FIX_STATUS – Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO
f.SKIP_CORRUPT_BLOCKS – When used, ignores blocks marked corrupt during table and index scans. If not used, you get error ORA-1578 when encountering blocks marked corrupt.
g.ADMIN_TABLES – Provides administrative functions (create, drop, purge) for repair or orphan key tables.
Note: These tables are always created in the SYS schema.
4.Using RMAN backup block media recovery (oracle 10g database)
a.Select * from v$database_block_corruption; –> Return any rows follow below steps
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19; –> Multi block corrupted
BLOCKRECOVER DATAFILE 8 BLOCK 13; –>Single block corrupted
b.backup validate datafile 8;
c.Select * from v$database_block_corruption; –> no rows returned
5.Using Physical Managed backup/Server Managed backup
a.Offline the particular datafile.
b.Restore the datafile from previous backup.(before ensure backup is valid)
c.Bring the datafile online.
d.Recover the datafile.
Note:
Scenario 5 applicable for oracle 9i/10g physical managed backup and 9i server managed backup. ( In 10g server managed backup we will go block media recovery)
6.Using Logical backup.
There is no backup strategy in our environment. Only we have logical backup.
1.Try to export the corrupted table. –> also failed
2.Create table temp_corrupted as select * from ; –> also failed
3.Drop the corrupted table & import the table from previous logical backup.( Data loss)
I Hope this article helped to you. Suggestions are welcome.
