In Oracle Database operations, data block corruption not only compromises data integrity but can also lead to critical issues such as business downtime. This article explains the primary tools for detecting physical and logical corruption (DBVERIFY, DBMS_REPAIR, and ANALYZE) and the procedure for block-level repair using RMAN.
1. Data Block Corruption Detection Tools
Three primary tools are used to detect corruption, depending on the purpose.
| Tool Name | Features | Usage |
| DBVERIFY | OS command-line tool | Checking physical corruption of datafiles |
| DBMS_REPAIR | PL/SQL package | Identifying, skipping, and managing corrupted blocks |
| ANALYZE | SQL command | Verifying object (table/index) structures |
1.1 Physical Consistency Check with DBVERIFY
The dbv command performs physical consistency checks directly on datafiles, regardless of whether the database is running.
$ dbv file=/u01/oradata/ORCL/users01.dbf
If corruption is detected, an error is output, such as Page X is marked corrupt.
1.2 Management and Repair with DBMS_REPAIR
This tool allows you to manage corrupted blocks in a table or configure the database to ignore corrupted blocks during SQL execution.
Create a table to identify corrupted blocks:
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
ACTION => DBMS_REPAIR.CREATE_ACTION
);
END;
/
Execute corruption check for an object:
DECLARE
v_corrupt_count NUMBER;
BEGIN
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => v_corrupt_count
);
END;
/
Configure to skip corrupted blocks during SQL execution:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
FLAGS => DBMS_REPAIR.SKIP_FLAG
);
END;
/
1.3 Utilizing ANALYZE VALIDATE STRUCTURE
This validates the consistency of a specific object.
ANALYZE TABLE SCOTT.EMP VALIDATE STRUCTURE;
Verification results can be confirmed in the DBA_TAB_CORRUPTIONS view.
SELECT * FROM DBA_TAB_CORRUPTIONS;
2. Methods for Repairing Data Block Corruption
When corruption is confirmed, select the appropriate recovery method based on the importance of the data.
2.1 Block Recovery using RMAN (Recommended)
Because you can repair only the corrupted blocks from a backup without restoring the entire datafile, service downtime can be minimized.
Command to repair corrupted blocks:
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 100,101;
Example: Confirming and repairing current corruption:
RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
5 140 1 0 ALL ZERO 0
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 140;
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/013k0ttf_1_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013k0ttf_1_1_1 tag=TAG20250310T173439
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:03
Finished recover at 25-03-10
RMAN> VALIDATE DATAFILE 5;
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=00005 name=/u01/app/oracle/oradata/V19/tbs24.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 4609 12800 2273917
File Name: /u01/app/oracle/oradata/V19/tbs24.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7936
Index 0 0
Other 0 255
Finished validate at 25-03-10
2.2 Object Recreation via Export/Import
In cases of logical corruption, or if the above method is not applicable, logically extract the data and reconstruct the object.
Data extraction:
$ expdp scott/password tables=emp directory=DATA_PUMP_DIR dumpfile=emp.dmp
After exporting the data, DROP and recreate the target table, then import it using impdp.
3. Frequently Asked Questions (FAQ)
Q1. Can DBVERIFY be executed while the database is running?
A. Yes. DBVERIFY is a tool executed offline against datafiles; however, consistency checks are possible even on database files that are online.
Q2. Are blocks configured to be skipped by DBMS_REPAIR deleted?
A. No. They are merely skipped while remaining corrupted, so recovery using RMAN or similar tools is always required for physical repair.
Q3. If corruption is detected by multiple tools, which one should be prioritized?
A. For physical corruption, prioritize repair using RMAN BLOCKRECOVER. For logical corruption, consider DBMS_REPAIR or recreating the object.
Summary
- DBVERIFY is optimal for early detection of physical corruption.
- DBMS_REPAIR is effective for failure investigation and operational avoidance (skipping).
- RMAN BLOCKRECOVER is faster than datafile-level repair and is recommended.
- Ensure operations include a combination of regular backups and validation (
VALIDATE).
This article targets Oracle Database 19c (other versions may have different screens or default values).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント