backup validate check logical database;step 2. Check whether there are any corrupt blocks:
select count(1) from V$DATABASE_BLOCK_CORRUPTION;step 3. investigate which objects these blocks belong to
create table block_corr as select file#,block# from V$DATABASE_BLOCK_CORRUPTION where 1=0;
create unique index bcorr_pk on block_corr(file#,block#);
insert into block_corr select file#,block# from V$DATABASE_BLOCK_CORRUPTION order by file#,block#;step 4. create a table to store faulted segments:
create table corr_objects as
SELECT de.segment_type, de.owner, de.segment_name, de.PARTITION_NAME
FROM dba_extents de, block_corr bc
WHERE de.file_id = bc.file#
and bc.block# between block_id AND block_id + blocks - 1
group by de.segment_type, de.owner, de.segment_name, de.PARTITION_NAME;
set lines 300 pages 999
col segment_name for a30
col PARTITION_NAME for a30
select * from corr_objects;
step 5. segment header block might be corrupt causing dba_extents not returning rows
select ds.owner, ds.segment_name, ds.segment_type, bc.file#, bc.block#
from dba_segments ds, block_corr bc
where ds.header_file=bc.file# and ds.header_block = bc.block#; step 6. If it doesn't belong to an object, double check if it does exists in dba_free_space to check if the block belongs to file space usage bitmap.
select * from dba_free_space where file_id= &AFN
and &CBN between block_id and block_id + blocks -1;list all corr blocks displaying whether it belongs to segment or free space
col is_used_block for a4
col is_free_space for a4
col file_name for a60
col file# for 999
select bc.file#, bc.block#
, DECODE(
(select de.block_id from dba_extents de where de.file_id = bc.file# and bc.block# between de.block_id and de.block_id + de.blocks -1 )
,null,'NO','YES') is_used_block
, DECODE(
(select dfs.block_id from dba_free_space dfs where dfs.file_id = bc.file# and bc.block# between dfs.block_id and dfs.block_id + dfs.blocks -1)
,null,'NO','YES') is_free_space
,ddf.file_name
from block_corr bc, dba_data_files ddf
where bc.file# = ddf.file_id
order by bc.file#, bc.block#;step 7. if the blocks belong to indexes or index partitions, then rebuild them using ONLINE option (EE)
--to rebuild indexes and index partitions:
select 'alter index '||t.owner||'.'||t.segment_name||' REBUILD ' ||decode(t.segment_type,'INDEX PARTITION','PARTITION '||t.partition_name,'') || ' ONLINE;' from (
SELECT de.segment_type, de.owner, de.segment_name, de.partition_name
FROM dba_extents de, block_corr bc
WHERE de.file_id = bc.file#
and bc.block# between block_id AND block_id + blocks - 1
) t where t.segment_type IN ('INDEX','INDEX PARTITION')
group by t.owner,t.segment_type,t.segment_name,t.partition_name
/step 8. Note 782137.1 If the block belongs to a datafile of a tablespace which has been dropped, then V$DATABASE_BLOCK_CORRUPTION is not going to be cleared by a subsequent rman backup. This is fixed in 11g. For 10g the workaround is:
execute dbms_backup_restore.resetCfileSection(17); /** clear v$backup_corruption
execute dbms_backup_restore.resetCfileSection(18); /**clear v$copy_corruption relevant metalink notes:
How to Find All the Corrupted Objects in Your Database.
V$Database_block_corruption Shows Entries for Non-existent Datafile
RMAN Does not Report a Corrupt Block if it is not Part of Any Segment
