v$database_block_corruption

Wir hatten es mal mit korrupten Blöcken zu tun. Diese kann man so finden:

-- Welche Art von Corruption:
 col CORRUPTION_CHANGE# format 999999999999
 select CORRUPTION_TYPE
            , min(CORRUPTION_CHANGE#) CORRUPTION_CHANGE#
           , count(*) Anz
    from V$DATABASE_BLOCK_CORRUPTION
   group by CORRUPTION_TYPE
   order by 1
 ;

CORRUPTIO CORRUPTION_CHANGE#        ANZ
 --------- ------------------ ----------
 NOLOGGING        31880926200       1632

— Wann ging es los

 select scn_to_timestamp(31880915605) from dual;

SCN_TO_TIMESTAMP(31880915605)
 ---------------------------------------------------------------------------
 10-APR-14 10.21.51.000000000 PM

-- Welche Blöcke
 col SEGMENT_NAME format a25
 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#;
 ;

Letzten SELECT habe ich aus „irgendeinen Link“.  Allerdings kann dies bei vielen in einer sehr großen DB lange dauern. Deswegen sollte man hier mit einer Zwischen-Tabelle arbeiten:

CREATE TABLE temp_dba_segments AS
    SELECT owner, segment_type, segment_name, file_id, block_id, blocks
      FROM dba_extents
   WHERE file_id IN (SELECT DISTINCT file# FROM v$database_block_corruption)
 ;  

CREATE INDEX IDX_temp_dba_segments ON temp_dba_segments (file_id, block_id);

SELECT e.owner, e.segment_type, e.segment_name, count(*) Anz
   FROM temp_dba_segments 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#
  GROUP BY e.owner, e.segment_type, e.segment_name
   ;

DROP TABLE temp_dba_segments;

Sollten die Blöcke nicht auf Grund von NOLOGGIN koruppt geworden sein, können diese dann auch mit RMAN recovert werden:

RECOVER CORRUPTION LIST

Gruß
Oliver