In Oracle Database administration, detecting data block corruption in advance is critical. This article provides a detailed explanation of the VALIDATE command and the V$DATABASE_BLOCK_CORRUPTION view used to check for data block corruption.
1. What is the VALIDATE Command?
The Oracle VALIDATE command is used to check the consistency of data blocks. By combining it with the BACKUP VALIDATE command, you can discover data block corruption and verify backup integrity.
1.1. Validating Datafiles
You can check for corruption in a specified datafile by executing the following command:
RMAN> VALIDATE DATAFILE 1;
To validate all datafiles, use the following command:
RMAN> VALIDATE DATABASE;
Example Output:
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/tbs24.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/V19/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 18147 149764 2273920
File Name: /u01/app/oracle/oradata/V19/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81289
Index 0 13395
Other 0 36929
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 18718 88330 2274014
File Name: /u01/app/oracle/oradata/V19/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6626
Index 0 2677
Other 0 60299
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 651 92800 2274014
File Name: /u01/app/oracle/oradata/V19/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 92149
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 4609 12800 2273917
File Name: /u01/app/oracle/oradata/V19/tbs24.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_2876.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
1.2. Validating Backup Data
Before taking a backup, you can verify that there is no data block corruption.
RMAN> BACKUP VALIDATE DATABASE;
To verify the integrity of a specific tablespace backup:
RMAN> BACKUP VALIDATE TABLESPACE users;
To verify only a specific datafile:
RMAN> BACKUP VALIDATE DATAFILE 3;
This command reads and verifies blocks without actually creating a backup.
2. V$DATABASE_BLOCK_CORRUPTION View
When the VALIDATE command is executed, information about corrupted data blocks is recorded in the V$DATABASE_BLOCK_CORRUPTION view. By checking this view, you can identify which blocks in the database are corrupted.
2.1. Confirming Corrupted Blocks
You can retrieve information on corrupted data blocks by executing the following SQL statement:
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
This view stores the following information:
| Column Name | Description |
FILE# | Number of the datafile containing the corrupted block |
BLOCK# | Starting block number of the corrupted block |
BLOCKS | Number of affected blocks |
CORRUPTION_TYPE | Type of corruption (e.g., “CORRUPT”, “LOGICAL”, “FRACTURED”) |
Example Query Result:
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE CON_ID
---------- ---------- ---------- ------------------ --------------------------- ----------
5 140 1 0 ALL ZERO 0
2.2. Repairing Corrupted Data Blocks
If corruption is discovered, appropriate action must be taken. For example, it is possible to repair corrupted blocks using RMAN.
RMAN> RECOVER DATAFILE 1;
Alternatively, you can recover data from the latest backup:
RMAN> RESTORE DATAFILE 1;
3. Frequently Asked Questions (FAQ)
Q1. Does database performance decrease while executing the VALIDATE command?
A. Yes. Because it reads all blocks on the disk, I/O load may increase. It is recommended to execute this during off-peak hours.
Q2. What if nothing is displayed in V$DATABASE_BLOCK_CORRUPTION?
A. It indicates that no corruption has been found in the database data blocks. This is a normal state, so you can be assured.
Q3. What does it mean if the corruption type is “LOGICAL”?
A. It refers to a logical consistency error where there is a contradiction in the data content, rather than physical corruption.
4. Summary
- The
VALIDATEcommand is used to detect database or datafile corruption in advance. - Using the
BACKUP VALIDATEcommand allows you to check for corruption before performing a backup. - If corruption is detected, it is recorded in
V$DATABASE_BLOCK_CORRUPTION. - Corrupted blocks can be repaired using RMAN’s
RECOVERorRESTOREcommands. - To maintain data integrity, it is recommended to periodically execute
VALIDATE DATABASEto monitor for data block corruption.
This article targets Oracle Database 19c (other versions may have different screens or default values).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント