12 Replies Latest reply: Apr 5, 2013 1:40 PM by moreajays RSS

    Block Corruption in Oracle database 11g R2

    Pankaj Kumar Mandal
      Hello guys,

      I have Oracle 11g R2 database in RHEL 5 machine from past few days I am getting block corruption error in particular datafile, first time I try to recover it as I had a valid RMAN backup all the blocks got recovered by blockrecover command.

      But now the problem is some block got corrupted again and also RMAN backup has corruption now
      SQL> select * from v$backup_corruption;

      RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
      ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------ --- ---------
      1 811689211 811688959 346 1 7 22929 1 0 YES CORRUPT
      2 811689211 811688959 346 1 7 22934 1 0 YES CORRUPT


      SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

      FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
      ---------- ---------- ---------- ------------------ ---------
      7 25461 1 0 CORRUPT
      7 25465 1 0 CORRUPT
      7 25469 1 0 CORRUPT
      7 25456 1 0 CORRUPT
      7 25452 1 0 CORRUPT
      7 25448 1 0 CORRUPT
      7 25444 1 0 CORRUPT
      7 25440 2 0 CORRUPT
      7 25436 2 0 CORRUPT
      7 25432 1 0 CORRUPT
      7 25428 1 0 CORRUPT
      7 25424 1 0 CORRUPT
      7 25420 1 0 CORRUPT
      7 25416 1 0 CORRUPT
      7 25414 1 0 CORRUPT
      7 25412 1 0 CORRUPT

      As the CORRUPTION type IS CORRUPT so it Block is wrongly identified or is not a data block (for example, the data block address is missing)

      And also I checked in which table the block is corrupted I drop that table and re created it. But the number of block corrupted in V$DATABASE_BLOCK_CORRUPTION was showing 181 records first after table drop and re creation it is showing me 176 records. So please help me guys..