Recover a corrupted Block - 19c

 

Recover a corrupted Block (Oracle 19c)

The steps to check for a corrupted blocks in the whole database as health monitor check and how to recover the corrupted block.

Steps: Recover the corrupted blocks

Step 1: Health Monitor the database:

RMAN> validate database;
 Starting validate at 11-MAY-24
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=424 device type=DISK
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 input datafile file number=00001 name=/data/ORCL/system01.dbf
 input datafile file number=00004 name=/data/ORCL/undotbs01.dbf
 input datafile file number=00003 name=/data/ORCL/sysaux01.dbf
 input datafile file number=00002 name=/data/ORCL/asyw_tbs01.dbf
 input datafile file number=00005 name=/data/ORCL/users02.dbf
 input datafile file number=00008 name=/data/ORCL/ORCL/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
 input datafile file number=00009 name=/data/ORCL/ORCL/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
 input datafile file number=00007 name=/data/ORCL/users01.dbf
 channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
 List of Datafiles
 …..
 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
 
 3    FAILED 0              20612        84607           3571828
   File Name: /data/ORCL/sysaux01.dbf
   Block Type Blocks Failing Blocks Processed
 
   Data       0              9536
   Index      0              6038
   Other      2              48294
 ….
 validate found one or more corrupt blocks
 See trace file /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_2707.trc for details
 channel ORA_DISK_1: starting validation of datafile
 channel ORA_DISK_1: specifying datafile(s) for validation
 including current control file for validation
 Finished validate at 11-MAY-24


Note: We have two corrupted blocks with the tablespace SYSAUX.

Step 2: Check the alert file

Corrupt block relative dba: 0x00c0f945 (file 3, block 63813)
 Bad check value found during validation
 Data in bad block:
  type: 0 format: 2 rdba: 0x00c0f945
  last change scn: 0x0000.0000.00000000 seq: 0x1 flg: 0x05
  spare3: 0x0
  consistency value in tail: 0x00000001
  check value in block header: 0x5e85
  computed block checksum: 0xa00
 Reread of blocknum=63813, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63813, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63813, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63813, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63813, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Hex dump of (file 3, block 63814) in trace file /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_ora_2707.trc
 Corrupt block relative dba: 0x00c0f946 (file 3, block 63814)
 Bad check value found during validation
 Data in bad block:
  type: 0 format: 2 rdba: 0x00c0f946
  last change scn: 0x0000.0000.00000000 seq: 0x1 flg: 0x05
  spare3: 0x0
  consistency value in tail: 0x00000001
  check value in block header: 0x5e86
  computed block checksum: 0xa00
 Reread of blocknum=63814, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63814, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63814, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63814, file=/data/ORCL/sysaux01.dbf. found same corrupt data
 Reread of blocknum=63814, file=/data/ORCL/sysaux01.dbf. found same corrupt data

Step 3: Recover the corrupted blocks

RMAN> list failure;

 Database Role: PRIMARY
 List of Database Failures
 Failure ID Priority Status    Time Detected Summary
 
 801        HIGH     OPEN      11-MAY-24     Datafile 3: '/data/ORCL/sysaux01.dbf' contains one or more corrupt blocks

 RMAN> advise failure;
 
 Database Role: PRIMARY
 List of Database Failures
 Failure ID Priority Status    Time Detected Summary
 
 801        HIGH     OPEN      11-MAY-24     Datafile 3: '/data/ORCL/sysaux01.dbf' contains one or more corrupt blocks
 analyzing automatic repair options; this may take some time
 using channel ORA_DISK_1
 analyzing automatic repair options complete
 Mandatory Manual Actions
 no manual actions available
 Optional Manual Actions
 Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command.  Then perform a Data Guard role change (failover).  Available standbys: ORCLsb. 
 Automated Repair Options
 Option Repair Description
 
 1      Recover multiple corrupt blocks in datafile 3
   Strategy: The repair includes complete media recovery with no data loss
   Repair script: /u01/app/oracle/diag/rdbms/ORCL/ORCL/hm/reco_3225659642.hm

 
 RMAN> repair failure;
 
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /u01/app/oracle/diag/rdbms/ORCL/ORCL/hm/reco_3225659642.hm
 contents of repair script:
    # block media recovery for multiple blocks
    recover datafile 3 block 63813 to 63814;
 Do you really want to execute the above repair (enter YES or NO)? yes
 executing repair script
 Starting recover at 11-MAY-24
 using channel ORA_DISK_1
 finished standby search, restored 2 blocks
 starting media recovery
 media recovery complete, elapsed time: 00:00:03
 Finished recover at 11-MAY-24
 repair failure complete