Checking logical block corruption

a) $ rman target / nocatalog

b) run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

c) select * from V$DATABASE_BLOCK_CORRUPTION ;

d)If V$DATABASE_BLOCK_CORRUPTION contains rows please run this procedure to find the objects that contains the corrupted blocks:

drop table corrupted_objects;
create table corrupted_objects as
select owner, segment_name, partition_name, segment_type, 1 file#, 1 block# from dba_extents where 1=2;
declare
cursor C1 is select file#, block# from v$database_block_corruption;
begin
for r1 in c1 loop
insert into corrupted_objects
select owner, segment_name, partition_name,segment_type, r1.file#, r1.block# from dba_extents where file_id=r1.file#
and r1.block# between block_id and block_id + blocks - 1;
if sql%notfound then /* segment header block might be corrupt causing dba_extents not returning rows. */
insert into corrupted_objects select owner, segment_name, partition_name, segment_type, r1.file#, r1.block#
from dba_segments where header_file=r1.file# and header_block = r1.block#;
end if;
end loop;
end;
/


e) Select all the objects populated in the table corrupted_objects:


SQL> select distinct owner, segment_name, partition_name, segment_type from corrupted_objects;