In Oracle Database operations, data corruption is a situation you want to avoid; however, should the need arise, the Oracle Data Recovery Advisor (DRA) serves as a powerful solution. This article explains the procedures for identifying and automatically repairing data block corruption using the DRA.
Data Recovery Advisor is one of Oracle’s self-diagnostic features. It automatically detects database corruption and works in conjunction with Recovery Manager (RMAN) to suggest and execute the optimal recovery method. It minimizes the need for complex manual command operations and supports rapid recovery.
Quick Steps for Recovery Using Data Recovery Advisor
These are the minimal steps from failure occurrence to recovery:
- Check Status: Verify the presence of corruption using
VALIDATE DATABASE. - Identify Failure: List the corruption locations using
LIST FAILURE. - Diagnose and Suggest: Display recommended repair methods using
ADVISE FAILURE. - Execute Repair: Apply automatic repair using
REPAIR FAILURE. - Confirm Completion: Execute
LIST FAILUREagain to ensure the failure is cleared.
1. Confirmation and Diagnosis of Data Corruption
If corruption is suspected, first visualize the current corruption status.
SQL for Checking Corrupted Blocks
Query the V$DATABASE_BLOCK_CORRUPTION view with the following SQL:
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Verification via RMAN
Perform a full database verification using RMAN to identify the location of corrupted blocks.
# Connect to RMAN
rman target /
# Execute verification
RMAN> VALIDATE DATABASE;
If corruption is found during verification, ORA-01578 errors will also be recorded in the alert log (alert_<SID>.log).
RMAN> VALIDATE DATABASE;
Starting validate at 25-03-10
using channel ORA_DISK_1
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=00003 name=/u01/app/oracle/oradata/V19/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/V19/undotbs01.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 18146 149764 2276316
File Name: /u01/app/oracle/oradata/V19/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81290
Index 0 13395
Other 0 36929
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 23005 93450 2276451
File Name: /u01/app/oracle/oradata/V19/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7162
Index 0 2820
Other 0 60453
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 416 92800 2276453
File Name: /u01/app/oracle/oradata/V19/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 92384
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
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 101 641 1252497
File Name: /u01/app/oracle/oradata/V19/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 60
Index 0 15
Other 0 464
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: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file in validation
including current SPFILE in backup set
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
Additionally, check the alert log to obtain detailed information about the data corruption:
tail -f $ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace/alert_<INSTANCE_NAME>.log
[Alert Log Output Example]
2025-03-10T20:11:51.450587+09:00
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/v19/v19/trace/v19_ora_2641.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during user buffer read
Reading datafile '/u01/app/oracle/oradata/V19/test_tbs.dbf' for corrupt data at rdba: 0x0140008c (file 5, block 140)
Reread (file 5, block 140) found same corrupt data (no logical check)
2025-03-10T20:11:51.532326+09:00
Corrupt Block Found
TIME STAMP (GMT) = 03/10/2025 20:11:50
CONT = 0, TSN = 6, TSNAME = TEST_TBS
RFN = 5, BLK = 140, RDBA = 20971660
OBJN = 74185, OBJD = 74185, OBJECT = TAB24, SUBOBJECT =
SEGMENT OWNER = TEST1, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/v19/v19/trace/v19_ora_2641.trc (incident=12441):
ORA-01578: ORACLE data block corrupted (file # 5, block # 140)
ORA-01110: data file 5: '/u01/app/oracle/oradata/V19/test_tbs.dbf'
Incident details in: /u01/app/oracle/diag/rdbms/v19/v19/incident/incdir_12441/v19_ora_2641_i12441.trc
2025-03-10T20:11:53.566649+09:00
... (snip) ...
2. Recovery Procedures Using Data Recovery Advisor
This is the specific recovery flow utilizing the DRA.
Step 2.1: Identifying Failures
Confirm the failures currently occurring.
RMAN> LIST FAILURE;
RMAN> LIST FAILURE;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
41 HIGH OPEN 25-03-10 Datafile 5: '/u01/app/oracle/oradata/V19/test_tbs.dbf' contains one or more corrupted blocks
Step 2.2: Suggesting Repair Methods
Have the DRA analyze recommended repair methods.
RMAN> ADVISE FAILURE;
“Automatic repair options” will be displayed in the output. Block Media Recovery is typically suggested.
RMAN> ADVISE FAILURE;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
41 HIGH OPEN 25-03-10 Datafile 5: '/u01/app/oracle/oradata/V19/test_tbs.dbf' contains one or more corrupted blocks
Analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
Automatic repair analysis complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automatic Repair Options
========================
Option Repair Description
------ ------------------
1 Perform block media recovery of block 140 (file 5)
Strategy: The repair includes complete media recovery that does not lose data
Repair script: /u01/app/oracle/diag/rdbms/v19/v19/hm/reco_3085682433.hm
Step 2.3: Executing Repair
Execute the suggested script to perform the repair.
RMAN> REPAIR FAILURE;
When you enter YES at the confirmation prompt, the necessary restore and media recovery will be executed automatically.
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery that does not lose data
Repair script: /u01/app/oracle/diag/rdbms/v19/v19/hm/reco_3085682433.hm
Contents of repair script:
# block media recovery
recover datafile 5 block 140;
Do you want to execute the above repair (enter YES or NO)? YES
Executing repair script
Starting recover at 25-03-10
using channel ORA_DISK_1
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) restore complete, elapsed time: 00:00:01
Starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-03-10
Repair of failure complete
Step 2.4: Confirming Repair Results
Check the failure list again to confirm that the status has been cleared.
RMAN> LIST FAILURE;
RMAN> LIST FAILURE;
Database Role: PRIMARY
No failures found that match your criteria
3. Operational Considerations and FAQ
Risks in Recovering Corrupted Data
- Necessity of Backup: Recovery via DRA requires a backup taken before the corrupted blocks occurred. If no backup exists, recovery may not be possible.
- How to Revert: Since this procedure performs data recovery (repair) only, a rollback (undo) due to incorrect repair is typically not performed. Always take the latest backup before starting operations.
FAQ
Q1: Does DRA always repair automatically? A1: No. Depending on the corruption status, manual intervention may be required. However, because DRA suggests “what is happening” and “what to do,” it significantly reduces the possibility of operational errors.
Q2: If ORA-01578 occurs, can I immediately use REPAIR FAILURE? A2: It is recommended to first execute ADVISE FAILURE to formulate a restore/recovery plan and confirm the optimal path.
Q3: What should I do if corruption occurs frequently? A3: It may be a sign of a disk failure. In addition to the LIST FAILURE results, check OS-level logs and perform storage health checks.
4. Summary
By utilizing the Data Recovery Advisor, you can minimize command assembly errors and safely recover corrupted blocks.
- LIST FAILURE: Find failures.
- ADVISE FAILURE: Confirm the path to recovery.
- REPAIR FAILURE: Safely repair.
Taking regular backups using RMAN is the fundamental prerequisite for effectively utilizing this powerful tool.
This article is intended for Oracle Database 19c (screens and default values may vary in other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント