Identificando os Objetos corrompidos reportados pelo RMAN

1 - Identificando os Objetos corrompidos: (este comando popula a view v$database_block_corruption)

RMAN> backup validate check logical database;

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
6 10 1 8183236781662 LOGICAL
6 42 1 0 FRACTURED
6 34 1 0 CHECKSUM
6 50 1 8183236781952 LOGICAL
6 26 1 8183242041269 LOGICAL

5 rows selected.

2 - Identificando os segmentos corrompidos:

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
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;
/

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

OWNER SEGMENT_NAME PARTIION_NAME SEGMENT_TYPE
-------- ------------ --------------- ------------
SCOTT CASE1 TABLE
SCOTT CASE3 TABLE
SCOTT CASE4 TABLE
SCOTT CASE2 TABLE
SCOTT CASE5 TABLE PARTITION TABLE

5 rows selected.