Whenever you see block corruptions listed in the alert.log, you should do the following:
1) RMAN> validate check logical database;
This will check all blocks for corruption in an online operation and update v$database_block_corruption
2) RMAN> recover corruption list;
This will recover all the detected corrupted blocks from step 1) in an online operation.
(1) and (2) will not affect availability of the database further than the corruption did anyways.
3) find the root cause for the corruption
It would be a good idea to contact Oracle Support with this, of course.
db_block_buffers 0
db_block_checksum TRUE
db_block_size 3
db_file_multiblock_read_count 98
db_block_checking FALSE
Below is the query to know which objects are corrupted,
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
Below query is to know which tablespace and how many blocks corrupted in each tablespace,
select b.tablespace_name, count(BLOCK#) from v$database_block_corruption a, dba_data_files b where a.FILE#=b.FILE_ID group by b.tablespace_name;
SELECT
A.FILE#,A.BLOCK#,A.BLOCKS,A.CORRUPTION_CHANGE#,A.CORRUPTION_TYPE,B.NAME FROM V$DATABASE_BLOCK_CORRUPTION A, V$DATAFILE B WHERE A.FILE#=B.FILE#;
For any further queries and support to resolve any database block corruptions you are facing, then email me samiora@gmail.com
