how to deal with block corruption

The block corruption is a tricky issue to deal with. You can usually see the corrupted blocks with the following query:


select * from v$database_block_corruption


If there are logical corruptions they can be fixed with restore-recover from RMAN or other many ways.

If there are physical corruptions, first fix the storage, and after this you can clear the corrupted blocks.




I found a SQL that determines which objects are affected by the corrupted blocks on this post:

http://toddlerdba.blogspot.com/2010/09/block-corrouption.html

It creates “segment_corrupted” table and inserts into it the objects affected by the corrupted blocks returned by the query above.


create table segment_corrupted (owner varchar2(10), segment_name varchar2(50), segment_type varchar(20));

-----------------------------------------------------------------------------------------------------------------------------
truncate table segment_corrupted;

----------------------------------------------------------------------------------------------------------

declare
begin
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from dba_extents where file_id=i.file# and i.block# between block_id and block_id + blocks -1;
commit;
end;
end loop;
commit;
end;
/

The segment_name is the Object Name and the segment_type is the Object Type.  After this script ends the Segment_corrupted table will have the objects affected by the corrupted blocks.

Sometimes a corrupted block may not affect any object.



There are many ways of dealing with corrupted blocks.


If there are indexes affected the simplest way is to drop and recreate the index.

If there are other types of objects, and you have a valid backup, you can drop them and recover from that backup.

 For tables, sometimes works rebuilding them using Toad (in fact toad renames the table, creates another one with the same data and recreates indexes, constraints, triggers, and other objects, but this doesn’t always work).

If the corrupted block does not affect any object, another solution besides recovering the datafile from backup, is to fill the tablespace and empty it (create a huge table with random data and drop it after) but this is a time consuming solution.


Do not forget to take a full database backup after you have cleared the corrupted blocks.