When data block corruption occurs in an Oracle database, you can use RMAN (Recovery Manager) to accurately identify the corrupted segments and efficiently repair them from a backup. This article explains the procedures for detection using VALIDATE DATABASE and automatic repair using RECOVER CORRUPTION LIST.
1. Detecting Corrupted Blocks
First, scan the entire database to determine if there is any physical or logical corruption.
Validating the Entire Database
RMAN> VALIDATE DATABASE;
This command automatically registers corrupted block information into the V$DATABASE_BLOCK_CORRUPTION view.
Checking Corruption Status
To verify the details of the corruption (such as file numbers and block numbers), execute the following SQL:
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Example Output:
RMAN> VALIDATE DATABASE;
Starting validate at 25-03-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=/u01/app/oracle/oradata/V19/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/V19/undotbs01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/V19/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/V19/test_tbs.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/V19/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 18147 149764 2273952
File Name: /u01/app/oracle/oradata/V19/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81289
Index 0 13395
Other 0 36929
(--- abbreviated ---)
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 4609 12800 2273949
File Name: /u01/app/oracle/oradata/V19/test_tbs.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7935
Index 0 0
Other 1 256
One or more corrupted blocks were found during validation.
Please refer to trace file /u01/app/oracle/diag/rdbms/v19/v19/trace/v19_ora_2652.trc for details.
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 646
Finished validate at 25-03-10
RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
5 140 1 0 ALL ZERO 0
RMAN> RECOVER CORRUPTION LIST; ★Repair corrupted blocks
Starting recover at 25-03-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=101 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/013k1688_1_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013k1688_1_1_1 tag=TAG20250310T195656
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block(s) restoration complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-03-10
RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
no rows selected
2. Repairing Corrupted Blocks (Automatic Repair)
You can repair all corrupted blocks listed in the V$DATABASE_BLOCK_CORRUPTION view in one batch using the backups held by RMAN.
Executing Automatic Repair
RMAN> RECOVER CORRUPTION LIST;
Executing this command causes RMAN to automatically perform a restore from backup and media recovery for all corrupted blocks found in the view.
Verifying Repair Results
After the repair is complete, check the view again. If it displays “no rows selected,” the blocks have been successfully repaired.
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
3. Other Repair Procedures
If RECOVER CORRUPTION LIST cannot resolve the issue, use the following commands depending on the situation:
- Repairing specific blocks only:
RMAN> RECOVER DATAFILE 4 BLOCK 100; - Recovering by datafile (for severe damage):
RMAN> RESTORE DATAFILE 4; RMAN> RECOVER DATAFILE 4;
4. Preventive Measures
To minimize the risk of corruption, we recommend the following parameter settings and operations:
Parameter Settings
DB_BLOCK_CHECKSUM: Enables corruption detection during write operations.SQLALTER SYSTEM SET DB_BLOCK_CHECKSUM=TYPICAL SCOPE=BOTH;DB_BLOCK_CHECKING: Enables consistency checks within blocks.SQLALTER SYSTEM SET DB_BLOCK_CHECKING=TYPICAL SCOPE=BOTH;
Periodic Operations
- Regular RMAN Backups:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
5. Frequently Asked Questions (FAQ)
Q1. Is it safe to run RECOVER CORRUPTION LIST in a production environment? A. Yes. Since it only repairs the necessary blocks, you do not need to take the entire file offline. However, as it places an I/O load on the system, we recommend running it during off-peak hours.
Q2. What should I do if the repair fails? A. Perform a full restore of the datafile (RESTORE) and apply archived logs for recovery.
Q3. The V$DATABASE_BLOCK_CORRUPTION view is not empty. A. There are blocks that have not been repaired yet. Try using the individual RECOVER DATAFILE ... BLOCK ... command.
Summary
- Scan for corruption using
VALIDATE DATABASEand check details via the view. - Perform automatic repairs using backups with
RECOVER CORRUPTION LIST. - Manage corruption risk proactively through parameter settings and periodic backups.
This article targets Oracle Database 19c (other versions may have different screens or default values).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント