该脚本的作用为查找损坏的块是在对象上面还是在空闲空间上.

set lines 200 pages 10000
col segment_name format a30

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

测试案例:

构造坏块

RMAN>  blockrecover datafile 4 block 20 clear;

Starting blockrecover at 28-SEP-15
using channel ORA_DISK_1
Finished blockrecover at 28-SEP-15

RMAN>  blockrecover datafile 4 block 22 clear;

Starting blockrecover at 28-SEP-15
using channel ORA_DISK_1
Finished blockrecover at 28-SEP-15

使用rman检测

RMAN> backup validate datafile 4;

Starting backup at 28-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/luda/oracle/data/user01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: -01:59:59
Finished backup at 28-SEP-15

执行该脚本查询坏块对应的对象

SQL> select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4         22          1                  0 CHECKSUM
         4         20          1                  0 CHECKSUM


SQL> set lines 200 pages 10000
SQL> col segment_name format a30
SQL>
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id < = c.block# + c.blocks - 1
 10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13  , header_block corr_start_block#
 14  , header_block corr_end_block#
 15  , 1 blocks_corrupted
 16  , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22  , greatest(f.block_id, c.block#) corr_start_block#
 23  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26  , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id < = c.block# + c.blocks - 1
 30  AND f.block_id + f.blocks - 1 >= c.block#
 31  ORDER BY file#, corr_start_block#;

OWNER                          SEGMENT_TYPE       SEGME PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ ----- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
SYS                            TABLE              LUDA                                          4                20              20                1
SYS                            TABLE              LUDA                                          4                22              22                1