Oracle rman 检测坏块

xifenfei

1. 
   
2. rman target /
   
3. 
   
4. RMAN> configure device type disk parallelism 4;
   
5. RMAN> backup validate check logical database;
   
6. 
   
7. 
   
8. sqlplus / as sysdba
   
9. spool /tmp/xifenfei.txt
   
10. set pagesize 2000
    
11.          set linesize 250
    
12.      SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
    
13.      , greatest(e.block_id, c.block#) corr_start_block#
    
14.      , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
    
15.      , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
    
16.        - greatest(e.block_id, c.block#) + 1 blocks_corrupted
    
17.      , null description
    
18.   FROM dba_extents e, v$database_block_corruption c
    
19. WHERE e.file_id = c.file#
    
20.    AND e.block_id <= c.block# + c.blocks - 1
    
21.    AND e.block_id + e.blocks - 1 >= c.block#
    
22. UNION
    
23. SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
    
24.      , header_block corr_start_block#
    
25.      , header_block corr_end_block#
    
26.      , 1 blocks_corrupted
    
27.      , 'Segment Header' description
    
28.   FROM dba_segments s, v$database_block_corruption c
    
29. WHERE s.header_file = c.file#
    
30.    AND s.header_block between c.block# and c.block# + c.blocks - 1
    
31. UNION
    
32. SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
    
33.      , greatest(f.block_id, c.block#) corr_start_block#
    
34.      , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
    
35.      , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
    
36.        - greatest(f.block_id, c.block#) + 1 blocks_corrupted
    
37.      , 'Free Block' description
    
38.   FROM dba_free_space f, v$database_block_corruption c
    
39. WHERE f.file_id = c.file#
    
40.    AND f.block_id <= c.block# + c.blocks - 1
    
41.    AND f.block_id + f.blocks - 1 >= c.block#
    
42. order by file#, corr_start_block#;
    
43. /
    
44. spool off

复制代码