In Oracle Database, physical or logical corruption of data blocks can trigger errors such as ORA-01578, leading to severe business impact. This article explains the roles and recommended settings for the initialization parameters DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING, which are used to detect block corruption early and strengthen data protection.
1. What is Block Corruption?
Block corruption is a state where data blocks can no longer be read or written to correctly.
Main Causes
- Physical factors: Disk failure, memory bit flipping, storage faults.
- Logical factors: OS bugs, incorrect file operations at the OS level, network failures.
Impact of Corruption
- ORA-01578: Failure to access the affected block.
- Business downtime: SQL queries terminating abnormally due to access to corrupted data.
- Difficult restore: Inconsistency in backup data that includes corrupted segments.
2. Corruption Detection with DB_BLOCK_CHECKSUM
DB_BLOCK_CHECKSUM detects corruption at the hardware or storage layer by calculating and verifying checksums during data block write and read operations.
Recommended Settings
- TYPICAL (Recommended): Applies checksums to data file blocks.
- FULL: Applies to all blocks, including redo logs, in addition to data blocks.
- NONE: Disabled (Not recommended).
Configuration Steps
-- Check current setting
SHOW PARAMETER DB_BLOCK_CHECKSUM;
-- Change to TYPICAL
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;
Note: Since the impact of TYPICAL on performance is extremely low, it is strongly recommended to keep it enabled at all times in production environments.
3. Consistency Checks with DB_BLOCK_CHECKING
DB_BLOCK_CHECKING performs “logical checks” to verify that there are no inconsistencies within the internal data structures of a block when data is updated.
Comparison of Settings
| Setting | Detection Level | Performance Impact |
| NONE | None (Default) | None |
| LOW | Basic data block check | Slight |
| MEDIUM | Checks majority of block consistency | Moderate |
| FULL | Detailed check of all blocks | High |
Configuration Steps
-- Change setting to MEDIUM (Recommended)
ALTER SYSTEM SET DB_BLOCK_CHECKING = MEDIUM SCOPE=BOTH;
Note: Specifying FULL will significantly increase CPU overhead. Use LOW or MEDIUM for normal operations.
4. Troubleshooting and Operational Precautions
Response Workflow for Corruption
- Identify Error: Determine the time of ORA-01578 occurrence and the target object in the alert log (
alert.log). - Investigate Impact: Check the
V$DATABASE_BLOCK_CORRUPTIONview. - Recovery: Perform media recovery for only the affected block, or restore the tablespace.
Operational Best Practices
- Backup: During RMAN backups, block corruption is automatically verified.
- Monitoring: By regularly monitoring
V$DATABASE_BLOCK_CORRUPTION, you can discover corruption before users encounter errors.
5. Frequently Asked Questions (FAQ)
Q1. Are existing data checked when I change these parameters?
A. No. Since these parameters perform checks during “write” or “read” operations, validation is performed sequentially starting from blocks accessed after the parameter change.
Q2. I am concerned about performance impact. Which should I choose?
A. DB_BLOCK_CHECKSUM=TYPICAL is mandatory. For DB_BLOCK_CHECKING, if you have sufficient CPU headroom, MEDIUM is recommended; in high-load environments, consider starting with LOW.
Q3. If corruption is found, how can I restore it?
A. There is no SQL command to directly repair block corruption. The standard approach is to use RMAN for block-level recovery (BLOCKRECOVER).
Summary
- Always set
DB_BLOCK_CHECKSUM=TYPICALto detect physical corruption. - Setting
DB_BLOCK_CHECKING=MEDIUMis recommended for logical consistency checking. - In addition to parameter configuration, RMAN backups and validation are the cornerstones of data protection.
This article targets Oracle Database 19c (other versions may have different screens or default values).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント