What is a Lost Write ?
A Lost Write, is as the name implies a database write which is lost, that is, the database writes a data block to the storage platform, receives an acknowledgment back from the storage but it was never actually persisted to disk or flash storage.
There are a number of reasons why Lost Writes can occur, and the root cause can be extremely hard to diagnose. Some examples include memory failures, disk controller failures, HBA firmware bugs, Operating System and network issues…
How Does Oracle Define a Lost Write ?
The Oracle 19c documentation provides the following definition:
A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.
How Does Oracle protect against Lost Writes ?
Since Oracle Database 11.1, Oracle has provided the DB_LOST_WRITE_PROTECT database parameter to help detect and protect against Lost Writes providing the following values { TYPICAL | FULL | NONE }
- FULL: on the primary database, the instance logs reads for read-only tablespaces and read/write tablespaces.
- TYPICAL: on the primary database, the instance logs buffer cache reads for read/write tablespaces in the redo log, which is necessary for detection of lost writes.
- NONE: on either the primary database or the standby database, no lost write detection functionality is enabled.(DEFAULT)
What’s Changed ?
The recent Oracle Database RU 19.26 release introduces a new DB_LOST_WRITE_PROTECT value ‘AUTO’.
This is now the default setting for Oracle Database 19.26 onwards, including Oracle Database 23ai.
DB_LOST_WRITE_PROTECT = { AUTO | TYPICAL | FULL | NONE }
From the Oracle Database 19c Documentaion:
AUTO
When this parameter is set to AUTO on a primary database, the instance automatically decides whether it logs buffer cache reads in the redo log or not, depending on the status of the standby databases.
Specifically, the primary database only logs buffer cache reads if physical standby databases with real time redo apply exist.
When this parameter is set to AUTO on a standby database, the instance will automatically decide whether it incurs additional performance overhead to perform lost write detection or not, depending on whether apply is keeping up.
If apply lag is beyond the reasonable threshold, the standby database will skip lost write protection temporarily until redo apply catches up with primary again, to ensure the lowest Data Guard role transition timings.
The reasonable threshold is determined as follows:
- If Fast Start Failover (FSFO) is configured through Data Guard Broker, based on apply lag threshold, then the threshold is the lesser of 60 seconds and two-thirds of the FSFO apply lag threshold.
- Otherwise, the threshold is 60 seconds.
What Can I Do ?
If you unfortunate to experience an Oracle Database Error ORA-00752 – recovery detected a lost write of a data block.
Cause: A data block write to storage was lost during normal database operation on the primary database.
Action: Shutdown the primary database and activate the physical standby database to failover. Call Oracle Support Services. See the Data Guard documentation for details. Unless directed by Oracle Support Services, do not attempt to bypass this error by copying blocks or data files from the primary database to the standby database because that would propagate the lost write from the primary to the standby database.
First don’t panic, read the Oracle Data Guard Scenarios guide for Recovering From Lost-Write Errors on a Primary Database and raise an Oracle SR.
Prepare to Test
As with all database parameter changes, the introduction of the ‘AUTO’ value and the change from ‘NONE’ to ‘AUTO’ may have an impact on your database, so perform all the necessary testing to understand impact within your environment.
If you are seeing and unexpected increase in redo generation, you may want to review the MOS Note: Database Generating Large Amount Of Redo After Applying Jan 2025 DBRU (37260974)
The post Oracle Database 19c Lost Write Protection – What’s New appeared first on Ron Ekins' – Oracle Technology, DevOps and Kubernetes Blog.
