34 Data Corruption Prevention, Detection, and Repair

Data corruption, defined as the unintentional alteration of data caused by hardware, software, or human errors, poses a critical threat to the integrity of database environments. To combat this pervasive concern, Oracle provides robust mechanisms built on three fundamental pillars: Prevention, Detection, and Repair.

Data Corruption Types

Summary of Data Corruption Types

Type Typical Source/Cause Detected by Symptom
Physical corruption Hardware/storage Oracle block reads and meta data checks and checksum Read errors, corrupted structure
Logical corruption Software/memory Oracle consistency checks including checking internal data structures Data exists, but is inconsistent/logically invalid
Lost write corruption Storage, I/O path Data Guard real time apply or recovery and Oracle sophisticated block versioning checks The block version is older than the redo expectations

Physical Block Corruption

These errors occur when a database block's structure is physically damaged or rendered unreadable. Such corruption is typically a result of hardware failures (for example: disk errors, controller faults, faulty cables).

Examples:

  • Incorrect or missing block header/footer

  • Mismatch in data checksums or internal "magic numbers"

  • Blocks containing entirely zeros or random, unrecognizable data patterns

Characteristics:

  • Detected by Oracle when attempting to read the corrupted block from disk

  • Manifests as severe errors such as ORA-01578: ORACLE data block corrupted

  • Can be repaired using block media recovery or data restoration from a valid backup


Description of corruption-physical.png follows
Description of the illustration corruption-physical.png

Logical Block Corruption

In contrast to physical corruption, logical corruption arises when a block's physical structure is intact and readable, but its internal contents are inconsistent with Oracle's expected logical rules or metadata. These are commonly indicative of software bugs or memory-related issues. Logical block corruptions can be intra-block logical corruptions where the logical corruption is self-contained in single data block, and inter-block logical corruptions where data inconsistencies between 2 or more independent data blocks.

Examples:

  • Index entries referencing non-existent rows is an example of inter-block logical corruption.

  • Table rows exhibiting inconsistencies with their associated metadata within the same data block is an example of intra-block logical corruption.

  • Incorrect internal pointers, such as those within row piece chains can be intra-block or inter-block logical corruption depending on how many data blocks are involved.

Characteristics:

  • The block is physically readable, and its checksums and structure are typically valid.

  • Detection requires logical consistency checks, often performed with RMAN VALIDATE CHECK LOGICAL, SQL command ANALYZE ... VALIDATE STRUCTURE, or exposed during application queries.

  • In some cases, repair is possible using block media recovery or without full restoration from backup, for example, by rebuilding affected indexes or using DBMS_REPAIR to work around the corruption so the remaining data can be accessed.


Description of corruption-logocal.png follows
Description of the illustration corruption-logocal.png

Lost Write Corruption

A lost write corruption occurs when a database block modification is acknowledged as successfully written by the storage sub-system, but the actual data is never persistently stored on disk or I/O being "lost". This results in the on-disk block image being older than the database expects, creating a crucial consistency mismatch.

Examples:

  • A defect in the I/O sub-system or system firmware resulting in false acknowledged I/Os

  • Following a system crash or any hardware failure, a data block on disk does not reflect recently committed changes that were believed to be durable.

  • The redo log contains entries expecting a newer block version than what is physically present on disk.

Characteristics:

  • This is a data consistency issue, where the data block is stale or older version than expected.

  • Primarily detected by advanced features like Oracle Data Guard or recovery using advanced metrics found in block read redo and comparing with data block metadata

  • Leads to data divergence, where the primary and standby databases become out of sync, or where the redo stream does not match the actual state of data files


Description of corruption-lostwrite.png follows
Description of the illustration corruption-lostwrite.png

Summary of Oracle Data Corruption Resiliency Capabilities

Proactive prevention, vigilant detection, and proper use of Oracle’s built-in repair features form a comprehensive defense against data corruption in Oracle databases.

Pillar Features, Tools, and Best Practices Purpose
Prevention

Software Update Practices, Testing, Use Recommended Database Parameters

Oracle Active Data Guard, Exadata, Recovery Appliance

For details see Preventing Data Corruption

 Avoid corruption from happening or avoid corruption from impacting your application
Detection

Recommended Database Parameters, Database and System Alerting, RMAN

Oracle Active Data Guard, Recovery Appliance, Exadata

For details see Detecting Data Corruption

 Find corruption early, notify, and take action
Repair

Oracle Active Data Guard, or GoldenGate, ASM and Exadata, RMAN

For details see Repairing Data Corruption

 Minimize impact; restore data and automatically repair (that is, Automatic Block Repair), aligning the solution with the lowest downtime and data loss

Preventing Data Corruption

Prevention aims to proactively stop corruption before it can occur.

Best Practices and Oracle Features

  • Maintain up-to-date software

  • Use dedicated test systems

    • Thoroughly evaluate all software, hardware, database, or application changes in a test environment before deploying to production. This practice helps detect potential new data corruptions and verifies the effectiveness of existing corruption fixes.

    • Employing a representative workload that accurately mimics production is crucial, because some corruptions manifest only under specific or extreme application conditions.

  • Employ reliable hardware

    • Implement enterprise-grade storage solutions equipped with advanced error-correcting features.

    • Exadata and Oracle ASM offer numerous integrated hardware and software features specifically designed to prevent data corruption from impacting applications and databases.

  • Implement storage redundancy

    • Use RAID configurations and redundant SAN/NAS solutions for physical storage resilience.

    • Oracle ASM and normal or high redundancy protects from data corruptions.

      If a block on one ASM disk is physically corrupted (for example, due to hardware failure), ASM can:

      • Serve an uncorrupted mirrored copy from another disk, shielding the database from encountering corrupted data.
      • Automatically recover the corrupted copy by reading the valid mirrored copy and rewriting it to replace the corrupt one if possible.

    • Exadata inherently provides built-in redundancy, along with advanced corruption prevention and automated repair capabilities. Exadata does regular data validation (scrubbing) to detect and repair corrupted blocks using mirrored copies.

  • Run regular backups

    • Schedule frequent, automated RMAN backups and ensure continuous archiving of redo logs.

    • Zero Data Loss Recovery Appliance (ZDLRA) offers automatic backup validation and leverages Exadata's corruption prevention and repair benefits.

Key Database Configuration Parameters for Data Corruptions Protection

  • DB_BLOCK_CHECKSUM

    • Purpose: Detects physical block corruptions by enabling checksumming for every database and redo block written to disk.

    • Recommendation: TYPICAL or higher for both primary and standby databases.

    • Default: TYPICAL

  • DB_BLOCK_CHECKING:

    • Purpose: Detects logical corruptions and prevents them from being written to disk.

    • Recommendation: MEDIUM or higher for primary or standby databases.

    • Default: FALSE

    • Note: Performance overhead can vary. A common compromise is to enable DB_BLOCK_CHECKING on the standby database to ensure logical corruptions are not propagated.

  • DB_LOST_WRITE_PROTECT

    • Purpose: Detects lost writes on standby databases and actively prevents/detects lost writes on primary databases.

    • Recommendation: AUTO for both primary and standby databases.

    • Default: TYPICAL, NONE, or AUTO depending on the database release. Starting in Oracle Database 19c (19.26), the default changed to AUTO for Data Guard configuration

    • While TYPICAL provides strong lost-write protection, it can increase redo generation on the primary database and add redo apply overhead on the standby database, including additional CPU and physical I/O. For some workloads, this overhead may contribute to increased standby apply lag.
    • With AUTO, lost-write protection is enabled when a physical standby database is current and real-time apply is present. If standby apply lag becomes too high, lost-write checks are temporarily suspended to help preserve standby readiness and role-transition performance.
    • Starting with Oracle Database 19c, automatic block repair of lost writes using a current block from either primary or standby can occur.

Oracle Integrity Tools and Features

  • RMAN backups: Ensure the integrity of your backups and facilitate recovery.

  • Avoid unreliable storage: Never place Oracle database files on improperly configured NFS mounts or other unreliable storage devices.

  • Use Oracle Active Data Guard: When configured with a physical standby database and real-time apply, Oracle Active Data Guard automatically detects all types of data corruption. It also provides automatic block repair for physical data block corruptions and some lost write corruptions on both primary and standby databases, preventing application impact. Contact Oracle Support to investigate the root cause of any detected corruption.

  • Enable Flashback Database: Flashback database logs can be leveraged for automatic block repair and RMAN block repair of data corruptions.

  • Use ASM redundancy and Exadata for additional corruption prevention, detection, and repair.

Detecting Data Corruption

Corruption detection focuses on identifying corruption as soon as it occurs or before it can cause widespread damage.

The following are best practices and Oracle features to detect data corruption.

  • Configure database parameters

    • Ensure that DB_BLOCK_CHECKSUM, DB_BLOCK_CHECKING, and DB_LOST_WRITE_PROTECT are configured for proactive block and write verification. Recommendations for these parameters are described in detail in Preventing Data Corruption.

  • Monitor database and system alerts

    • Regularly monitor Oracle AI Database, Oracle Clusterware, or Exadata alert logs and system logs for any corruption-related errors or warnings. Exadata provides additional specialized alerts if corruptions are detected within its storage layer.

  • Query database views

    • Refer to V$DATABASE_BLOCK_CORRUPTION for a comprehensive list of known corrupted blocks within the database.

  • Leverage RMAN validation

    • Perform RMAN backups, which include checks for physical corruption by default.

    • Use BACKUP VALIDATE or VALIDATE DATABASE commands to periodically check for corruption without creating a full backup.

    • Use BACKUP VALIDATE CHECK LOGICAL DATABASE to detect logical corruptions while performing your backups.

    • Refer to RMAN Continuous Block Corruption Checker (KB145527).

  • Use Oracle Active Data Guard and recommended database parameters for data corruptions protection

    • With Active Data Guard and a physical standby database operating with real-time apply, Data Guard automatically detects all types of data corruption from physical, logical and lost write data corruptions.

    • It further facilitates auto block repair for physical data block corruptions and some lost write corruptions on either primary or standby databases, effectively preventing corruption from impacting applications.

    • Always contact Oracle Support to understand the root cause of any data corruption.

  • Use Zero Data Loss Recovery Appliance (ZDLRA)

    • The Recovery Appliance provides automatic and continuous backup validation, detects and alerts you about backup integrity issues, and offers additional corruption prevention and repair benefits.

  • Detect LOB segment corruption

  • Run ANALYZE ... VALIDATE STRUCTURE CASCADE ONLINE

    • The ANALYZE command can be used to check for structural and referential inconsistencies in tables or indexes.
    • The specific clause for checking for inconsistencies is VALIDATE STRUCTURE CASCADE ONLINE.
    • These types of inter-logical block corruptions may not be detected by recommended database parameters.
  • Use ASM redundancy and Exadata for additional corruption prevention, detection and repair.
    • Any detected corruptions are in relevant log files, and both ASM and Exadata automatically recover the corrupted copy by reading the valid mirrored copy and rewriting it to replace the corrupt one (if possible). This may not work for logical corruption or even all mirrored copies that have been corrupted or compromised.

  • Use Oracle Enterprise Manager (OEM)

    • Provides automated monitoring capabilities for corruption events and overall backup health

Repairing Data Corruption

Repair strategies focus on restoring or correcting corrupted data after it has been identified, minimizing impact or working around the corrupted data, and attempting to re-establish data integrity.

The following are best practices and Oracle features to repair data corruption.

  • RMAN block media recovery

    • This highly efficient method restores only the corrupted blocks from backup, significantly minimizing recovery time and application downtime.

    • Primarily effective for physical data corruption but can help in some logical data block and lost block corruptions when following our recommended practices

    • See References for articles and documentation topics.

  • RMAN full/incremental restore and recover

    • Provides comprehensive recovery options to restore entire data files, tablespaces, or the entire database to a consistent, uncorrupted state

  • Oracle Active Data Guard automatic repair and failover

    • Automatic Block Repair: Oracle Active Data Guard can automatically repair physical data corruptions by fetching a good block from its synchronized standby database (or vice versa).

      • Starting with Oracle Database 19c (19.27) and Oracle AI Database 26ai, detected standby lost write corruptions use the Automatic Block Repair feature for transparent repair.

      • For physical block corruptions (such as ORA-1578), primary database auto-block repairs (that is, a corrupt block is seen on the primary database and Oracle internally fixes it by fetching good block from the standby database) requires standby container database with a minimum apply lag to be open read-only regardless of the standby PDB state. For standby auto-BMR (that is, a corrupt block is seen on the standby database and Oracle internally fixes it by fetching good block from the primary) requires at least a mounted or open standby with a minimum apply lag.
      • If standby lost write corruption is detected on the standby database, the standby lost write results in an ORA-753 error. Oracle automatically marks this is a soft corrupted data block which triggers Automatic Block Repair. If the block cannot be repaired, it remains soft corrupted, and once the database is open, reads on that block will raise ORA-01578.
      • For Oracle Cloud Data Guard environments and for primary database lost write on index block, Automatic Block Repair addresses this scenario automatically without the need to create the index.

    • Fast-Start Failover (FSFO) with lost write protection: To enable automatic failover when a standby detects a lost write corruption, configure Data Guard Broker with FSFO enabled, set DB_LOST_WRITE_PROTECT to TYPICAL or higher, and set Data Guard broker property PrimaryLostWriteAction.

      Options for Data Guard broker property PrimaryLostWriteAction:

      • FAILOVER: If FSFO is enabled in Maximum Availability or Maximum Performance mode, and the lag is either zero with Maximum Availability or within FastStartFailoverLagLimit for Maximum Performance, the observer initiates a failover.

      • FORCEFAILOVER: If FSFO is enabled in Maximum Performance, Maximum Availability, or Maximum Protection mode), the observer initiates a failover, which may result in data loss.

  • Flashback Database

    • Enables rapid restoration of the database to a point in time before the corruption, significantly reducing recovery time objectives (RTO)

    • Flashback logs can also contribute to RMAN block media recovery.

  • Targeted table/partition recovery

    • For specific object-level corruption, RMAN table or tablespace point-in-time recovery, or Data Pump export/import (to extract and load healthy data) can be used.

  • Index rebuild

    • If an index block is corrupted, rebuilding the affected index can often resolve the issue without requiring full database or tablespace recovery.

  • LOB segment corruption repair

  • Failover to standby database or GoldenGate replica (Lowest RTO/RPO)

    • To achieve the lowest possible Recovery Time Objective (RTO) and Recovery Point Objective (RPO) when the primary database is experiencing corruptions, initiate a failover to a healthy Data Guard standby database or a GoldenGate replica. This ensures business continuity by transitioning to a database without any data corruptions.

  • DBMS_REPAIR package
    • The DBMS_REPAIR package in Oracle is designed to identify and address corruptions in database tables and indexes, especially when physical or logical corruption prevents normal queries or operations from completing. It does not physically fix the corrupt blocks, but instead helps you to isolate and work around them, so that the rest of your data can be accessed or managed.

References