This tutorial explains the steps to check for a corrupted blocks in the whole database as health monitor check and how to recover the corrupted block.
- Steps:
- Heal Monitor for the database
- Recover the corrupted blocks
Step 1: Health Monitor the database:
RMAN> validate database;
Starting validate at 11-MAY-23
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/wadodb/system01.dbf
input datafile file number=00004 name=/data/wadodb/undotbs01.dbf
input datafile file number=00003 name=/data/wadodb/sysaux01.dbf
input datafile file number=00002 name=/data/wadodb/asyw_tbs01.dbf
input datafile file number=00005 name=/data/wadodb/users02.dbf
input datafile file number=00008 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5skfdoz_.dbf
input datafile file number=00009 name=/data/wadodb/WADODB/datafile/o1_mf_wado_tbs_l5sldkgh_.dbf
input datafile file number=00007 name=/data/wadodb/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/wadodb/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/wadodb/wadodb/trace/wadodb_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-23
RMAN>
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/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63813, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63813, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63813, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63813, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Hex dump of (file 3, block 63814) in trace file /u01/app/oracle/diag/rdbms/wadodb/wadodb/trace/wadodb_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/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63814, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63814, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63814, file=/data/wadodb/sysaux01.dbf. found same corrupt data
Reread of blocknum=63814, file=/data/wadodb/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-23 Datafile 3: '/data/wadodb/sysaux01.dbf' contains one or more corrupt blocks
RMAN>
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
Failure ID Priority Status Time Detected Summary
801 HIGH OPEN 11-MAY-23 Datafile 3: '/data/wadodb/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: wadodbsb.
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/wadodb/wadodb/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/wadodb/wadodb/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-23
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-23
repair failure complete
RMAN>
RMAN>
Enjoy!
Google+
Facebook
