实验环境
搭建平台：VMware Workstation
OS：RHEL 6.10
Grid&DB：Oracle 11.2.0.4
SQL参考 说明：如果没有在RMAN执行命令“backup check logical validate database”，就不会在视图 v$database_block_corruption中灌入数据，则 以下SQL也就可能不会有任何输出！ --如果使用sqlplus查询先要格式化 set lines 200 pages 10000 col segment_name format a30SELECT 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#;
