What is lost write detection?
In the world of database integrity, there is a silent killer known as a Lost Write. It is one of the most terrifying forms of data corruption because, for a long time, the database doesn't even know it has happened.
Lost Write Detection is the set of features Oracle uses to catch these "ghost" errors before they wreck your business data.
A Lost Write occurs when the database issues a write command to the storage subsystem, the storage says "OK, I've saved it," but in reality, it never actually wrote the data to the disk.
The Trap: The database thinks the block is safe on disk.
The Reality: The disk still contains the old version of the block.
The Crisis: Later, the database reads that block. It looks physically healthy (the checksums match!), but the data is "stale." This is logical corruption, and it can lead to incorrect financial balances, broken indexes, and mismatched reports.
Oracle’s primary defense against this is found in Data Guard. Since the Primary and Standby databases are both receiving the same redo logs, they can act as "checks and balances" for each other.
The Setup: You enable DB_LOST_WRITE_PROTECT on both the Primary and Standby.
The Record: When the Primary writes a block to disk, it records a small "version marker" in the redo log.
The Comparison: When the Standby applies that redo, it looks at its own version of that block.
The Detection: If the Standby sees that the Primary's "version marker" is older than it should be (indicating the Primary thought it wrote something but actually didn't), the Standby raises an ORA-00752 error and shuts down to prevent the corruption from spreading.
What if you don't have Data Guard? Oracle introduced Shadow Lost Write Protection.
Instead of a Standby database, Oracle uses a Shadow Tablespace. It stores a tiny "signature" of every data block in this separate area. Every time a block is read from the main disk, Oracle compares its signature to the one in the "Shadow" area. If they don't match, a Lost Write is detected.
You can set DB_LOST_WRITE_PROTECT to three levels:
| Level | Description |
| NONE | No detection. Fastest performance, but highest risk. |
| TYPICAL | Only records writes for "Read-Write" tablespaces. Good balance for most. |
| FULL | Records writes for all tablespaces, including "Read-Only." Safest option. |
It’s rarely the database’s fault. The culprits are usually:
Buggy Disk Controllers: A firmware error in the storage array.
Aggressive Caching: A storage controller "lies" about a write being finished to speed up performance.
Virtualization Layers: A hypervisor failing to pass a write through to the physical hardware.
Lost Write Detection doesn't prevent the hardware failure, but it stops the clock. It alerts you the moment the data diverges, allowing you to fail over to a healthy standby or restore from a backup before the "stale" data is used to make business decisions.
Peer Tip: Lost write detection does add a small amount of overhead to your redo generation (about 5-10%). However, if you are running a high-stakes financial or medical database, that "insurance premium" is well worth the cost to ensure your data is actually what you think it is.