Catching Corruption Early: DB_BLOCK_CHECKSUM TYPICAL vs FULL on Exadata

Introduction

Few initialization parameters have such a small footprint and such a large safety payoff as DB_BLOCK_CHECKSUM. It decides whether Oracle can detect physically corrupted blocks before they quietly spread through your database, your backups and your standby. On Exadata, where Oracle has built many corruption-prevention features around the database, getting this parameter right is part of the Maximum Availability Architecture (MAA) baseline.

This post explains what the parameter does, how TYPICAL and FULL differ, what Oracle recommends for Exadata, and how to implement and verify it.

1. A Quick Look at the Types of Corruption

It helps to separate three different problems, because each has its own parameter:

• Physical corruption: the bytes of a block changed, for example because of a faulty disk, storage component or I/O path. DB_BLOCK_CHECKSUM targets this.
• Logical corruption: a block is internally inconsistent, for example because of a software bug or corruption in memory. DB_BLOCK_CHECKING targets this.
• Lost writes: the storage reports a write as complete but never persisted it. DB_LOST_WRITE_PROTECT targets this.

2. What DB_BLOCK_CHECKSUM Does

The parameter accepts the values OFF (or FALSE), TYPICAL (or TRUE) and FULL. The default is TYPICAL, and it can be changed dynamically with ALTER SESSION or ALTER SYSTEM.

The mechanism is simple:

1. When a block is written to disk, the database writer (or the direct loader) calculates a checksum from the block’s bytes and stores it in the block header.
2. When the block is read back, the checksum is recalculated and compared with the stored one. A mismatch means the block was damaged somewhere between the write and the read.

Verification on read happens only if the parameter is TYPICAL or FULL and the last write of that block stored a checksum. Oracle’s MAA guidance summarizes the purpose as detecting physical block corruption by checksumming every database and redo block written to disk.

A checksum can show that a block’s bytes are unchanged, but it cannot prove that the contents are logically consistent. That is why it is not a replacement for DB_BLOCK_CHECKING.

3. TYPICAL vs FULL

The overhead figures come from Oracle’s older 10.2 reference documentation, so treat them as a rough guide only. The real cost depends on your workload, and the more update and insert activity you have, the more it matters. Test on a representative system before deciding.

4. Oracle’s Recommendations for Exadata

Oracle’s MAA guidance on data corruption prevention recommends DB_BLOCK_CHECKSUM set to TYPICAL or higher on both primary and standby databases. TYPICAL is the default. Another Oracle MAA document makes the same recommendation and adds that FULL may add overhead depending on the workload.

In practical terms:

1. Never turn it off on Exadata. Tuning advice from other platforms sometimes suggests disabling block checks to save a few percent. On Exadata, which is designed around corruption detection and automatic repair, that trade is not worth it.
2. Keep TYPICAL as the baseline for general workloads.
3. Consider FULL for critical databases where the added protection justifies the overhead, after testing it. This is a choice, not an Oracle requirement.
4. Use the same value on every RAC instance, so protection does not vary by node.
5. Apply the same philosophy to the standby. A standby that checks blocks well is your last line of defense against a corrupted primary.

5. The Companion Parameters

Oracle’s guidance treats these three parameters as a group, so review them together:

DB_BLOCK_CHECKING costs noticeably more than the checksum, so it deserves careful testing, particularly on write-heavy systems.

There is also a convenience parameter, DB_ULTRA_SAFE, which sets several of these in one step. In one published test, setting it to DATA_ONLY resulted in DB_BLOCK_CHECKING=MEDIUM, DB_BLOCK_CHECKSUM=FULL and DB_LOST_WRITE_PROTECT=TYPICAL. Setting the three parameters individually usually gives you clearer control and clearer documentation.

6. How Exadata Adds to the Protection

The database parameter is only one layer. Exadata and ASM add others:

• Storage redundancy: ASM mirroring keeps a second (or third) copy of each extent on a different storage cell.
• Scrubbing: Exadata regularly validates data to detect corrupted blocks and repairs them using the mirrored copies.
• Automatic repair: when a corruption is detected, a good copy is available to repair from.

The checksum is what lets the database recognize a bad block in the first place. Redundancy and scrubbing then turn a detected corruption into a repair rather than just an error. Check the Exadata documentation for your software version for the full list of which storage-level checks work together with this parameter.

7. Implementation

Check the current value

SHOW PARAMETER db_block_checksum
SHOW PARAMETER db_block_checking
SHOW PARAMETER db_lost_write_protect

Run this in every database, on the primary and on the standby.

Change it

The parameter is dynamic, so no restart is needed:

-- Set for all instances, in memory and in the spfile
ALTER SYSTEM SET db_block_checksum = FULL SCOPE = BOTH SID = '*';

Understand what changes, and when

Because verification depends on whether a block’s last write stored a checksum, changing the value does not retroactively protect blocks that are already on disk. Protection applies to blocks written after the change. If you move from OFF to TYPICAL or FULL, the coverage grows gradually as blocks are rewritten.

Test the overhead

1. Capture a baseline with a representative workload, for example using Real Application Testing or your usual benchmark.
2. Change the setting in a test environment.
3. Compare elapsed time, CPU and redo generation rates, paying attention to the peak load.
4. Only then decide whether to move production from TYPICAL to FULL.

Know what to look for if corruption is found

Detected corruptions are reported in the database or ASM alert log, typically as ORA-600 or ORA-01578 errors. Make sure your monitoring picks these up and that you know your recovery path, which on Exadata normally involves ASM mirror repair, RMAN block recovery or the standby database.

8. Choosing a Setting

9. Best Practices Checklist

1. Confirm DB_BLOCK_CHECKSUM is at least TYPICAL on every instance, primary and standby.
2. Review DB_BLOCK_CHECKING (MEDIUM or higher) and DB_LOST_WRITE_PROTECT (TYPICAL or higher) at the same time.
3. Test FULL and DB_BLOCK_CHECKING overhead before enabling them in production.
4. Use the same values on all RAC instances.
5. Remember that changes only protect blocks written after the change.
6. Monitor alert logs for ORA-600 and ORA-01578, and rehearse the recovery procedure.
7. Add the settings to your configuration audit, so they are checked after every upgrade or migration.

Conclusion

DB_BLOCK_CHECKSUM is a cheap insurance policy. TYPICAL, the default, is the right baseline on Exadata, FULL is a reasonable upgrade for critical systems once you have measured the overhead, and turning it off is the one choice to avoid. Combined with DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT and Exadata’s own redundancy and scrubbing, it gives you the chance to find corruption early and repair it before your users ever notice. Which setting do you run in your environments, and have you measured the overhead of FULL? Let me know in the comments.

References

• Oracle Database High Availability Overview and Best Practices, “Data Corruption Prevention, Detection, and Repair” (docs.oracle.com)
• Oracle Database Reference, DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING (docs.oracle.com)
• Oracle MAA, Database HA Configuration Best Practices (oracle.com)

Disclaimer: Always test parameter changes in a non-production environment first, and verify the settings and overhead figures against the Oracle documentation for your specific Exadata System Software and database versions.