Oracle Database
Lost Write Protection
Shadow Lost Write Protection vs. DB_LOST_WRITE_PROTECT
Technical Paper | Oracle Database
19c, 26ai | Database Integrity, Data Guard and Storage Reliability
Abstract
A lost write is one of the most
dangerous forms of database corruption because the database storage subsystem
may acknowledge a successful write even though the data was never permanently
persisted. Oracle Database may therefore continue operating under the
assumption that a data block has been updated successfully, while a subsequent
read returns an older version of that block.
This creates a serious
data-integrity risk, particularly in mission-critical environments such as
banking, financial services, and high-availability database platforms.
Oracle provides two independent mechanisms to address this problem:
1.
Shadow Lost Write Protection: Uses
dedicated shadow tablespaces to maintain block-level SCN tracking information
and detect lost writes during protected block reads. It does not require Oracle
Data Guard.
2.
Data Guard Lost Write Detection: Uses
the DB_LOST_WRITE_PROTECT initialization parameter to record relevant buffer
cache reads in redo. A physical standby uses this information during redo apply
to detect inconsistencies between the primary and standby databases.
Although both mechanisms address
lost writes, they operate differently, have different architectural
requirements, and provide complementary protection.
This paper examines the underlying
problem, Oracle's protection mechanisms, implementation procedures,
configuration commands, operational considerations, performance implications,
recovery scenarios, and recommendations for production environments.
1.
Understanding Lost Writes
1.1
What is a lost write?
A lost write occurs when an I/O
subsystem acknowledges the completion of a data block write, but the write does
not actually reach persistent storage.
In other words:
- Oracle requests a data block write.
- The operating system or storage subsystem
reports that the write completed successfully.
- The database assumes that the updated
block has been persisted.
- However, the storage system retains the
older version of the block.
- A subsequent read returns stale data.
This is different from a
conventional write failure. In a conventional failure, the database generally
receives an I/O error. With a lost write, the storage subsystem reports
success, concealing the failure.
Possible causes include:
|
Component |
Potential failure |
|
Storage controllers |
Firmware bugs or incorrect
write acknowledgments |
|
SSD/HDD devices |
Internal controller or persistence failures |
|
SAN infrastructure |
Controller failures or
inconsistent write handling |
|
Storage cache |
Volatile cache loses acknowledged writes |
|
HBA and drivers |
Firmware or driver defects |
|
Operating system |
I/O handling or synchronization problems |
|
Storage replication |
Incorrect ordering or
inconsistent replica state |
A lost write is particularly
dangerous because the database may continue processing transactions without
immediately detecting the underlying problem.
1.3
A practical example: Banking transaction
Consider a banking application
with two accounts:
- Savings account: $10,000
- Checking account: $2,000
A customer transfers $5,000 from
savings to checking.
The database executes:
SQL> UPDATE accounts SET
balance = 5000 WHERE account_id = 100;
SQL> UPDATE accounts SET
balance = 7000 WHERE account_id = 200;
SQL> COMMIT;
Suppose the storage subsystem
acknowledges the first data block write but fails to persist it.
The database might subsequently
retrieve the original savings balance of $10,000, while the checking account
correctly contains $7,000.
The transaction has logically
committed, but the persisted data is inconsistent.
This can lead to:
- Incorrect account balances.
- Financial inconsistencies.
- Corruption spreading into reporting and
downstream systems.
- Recovery challenges when the original
primary database is no longer trustworthy.
The banking scenario is
illustrative; the actual behavior depends on the I/O sequence, block state, and
recovery history.
2.
Why Redo Logs Alone Are Not Sufficient
Oracle uses redo logging to
guarantee transaction recoverability.
However, redo logging and lost
write detection solve different problems.
Consider the normal transaction
lifecycle:
1.
A
database block is modified in the Database Buffer Cache.
2.
Oracle
generates redo records describing the changes.
3.
Redo
records are placed in the Redo Log Buffer.
4.
LGWR
writes redo to the Online Redo Logs.
5.
Once
the required commit redo is persisted, Oracle acknowledges the commit.
6.
DBWR
writes modified data blocks to datafiles asynchronously.
The important architectural
distinction is that Oracle does not need to persist the modified data block in
its datafile before acknowledging COMMIT. The durable redo record provides the
information required for recovery, assuming the storage system honors its
persistence guarantees.
However, if DBWR receives a false
successful acknowledgment, and a subsequent read retrieves the old block,
Oracle must detect that inconsistency.
Redo logs describe database
changes; they are not, by themselves, a continuous validation mechanism for
every block returned by normal database reads.
|
Component |
Primary responsibility |
|
Online Redo Logs |
Record changes required for
recovery |
|
Datafiles |
Store database blocks |
|
Database Buffer Cache |
Cache blocks being read or
modified |
|
LGWR |
Persist redo records |
|
DBWR |
Write modified database
blocks |
|
Shadow Tablespaces |
Maintain SCN tracking information for lost write detection |
|
Data Guard Lost Write
Detection |
Compare block history using
redo and standby recovery |
This distinction is fundamental to
understanding why Oracle provides separate lost write protection mechanisms.
3. Oracle's Two Lost Write
Protection Mechanisms
Oracle provides two independent
mechanisms. Understanding this distinction is essential before configuring
either one.
3.1 Shadow Lost Write Protection
Does not require Data Guard
Shadow Lost Write Protection was
introduced in Oracle Database 18c.
It uses dedicated shadow
tablespaces to store SCN-related tracking information for protected data
blocks.
The mechanism works as follows:
1.
Oracle
tracks updates to protected data blocks.
2.
Corresponding
tracking information is maintained in shadow tablespaces.
3.
When
a tracked block is subsequently read, Oracle checks its SCN against the
recorded tracking information.
4.
If
the block is older than the expected version, Oracle can identify a lost write.
5.
An
error is raised instead of allowing the stale block to be silently consumed.
An important point: a shadow
tablespace does not store a second copy of the original database table or the
complete data blocks. It maintains tracking information.
Important characteristics
- Does not require a physical standby
database.
- Can be enabled at database, PDB,
tablespace, or datafile level.
- Uses dedicated BIGFILE shadow
tablespaces.
- Supports detection during normal
protected database operations.
- Also checks blocks read during RMAN
backups.
- Can be suspended without deleting
existing tracking information.
Oracle documents that shadow lost
write protection is independent of the DB_LOST_WRITE_PROTECT parameter.
3.2 Data Guard Lost Write
Detection
Designed for primary/standby
environments
The second mechanism uses:
DB_LOST_WRITE_PROTECT
This is an initialization
parameter that controls lost write detection through redo information.
Unlike shadow lost write
protection, it does not require shadow tablespaces.
Its architecture relies on the
relationship between a primary database and a physical standby database.
How it works
1.
The
primary database reads a data block into its buffer cache.
2.
Depending
on the configured protection mode, Oracle records relevant buffer cache read
information in redo.
3.
Redo
is transported to the physical standby.
4.
The
standby applies redo during managed recovery.
5.
Oracle
compares the block SCNs and associated redo information.
6.
If
an inconsistency indicates a lost write, Oracle raises an error and stops the
affected recovery process.
There are two important scenarios:
|
Detection scenario |
Error |
Meaning |
|
Lost write on primary |
ORA-00752 |
The primary database has
lost a disk write |
|
Lost write on standby |
ORA-00600 [3020] |
A standby block is inconsistent with the redo being applied |
The exact recovery diagnosis
matters: not every ORA-00600 [3020] indicates a lost write. Oracle recommends
involving Oracle Support before treating that error as definitive evidence of a
lost write.
3.3 The architectural difference
|
Characteristic |
Shadow Lost Write
Protection |
DB_LOST_WRITE_PROTECT |
|
Architecture |
Local block tracking |
Redo-based primary/standby
detection |
|
Requires Data Guard |
No |
Standby comparison requires physical standby |
|
Shadow tablespace |
Required |
Not required |
|
Additional redo for block-read tracking |
Not its tracking mechanism |
Yes, depending on mode |
|
Tracking information |
Shadow tablespaces |
Redo information |
|
Detection |
Protected block reads and RMAN backup reads |
Standby redo apply |
|
Configuration |
SQL DDL commands |
Initialization parameter |
|
Configuration scope |
Database, PDB, tablespace, datafile |
Database/instance parameter; PDB-modifiable in supported
releases |
|
Can coexist |
Yes |
Yes |
The most important distinction:
Ø ALTER DATABASE ENABLE LOST WRITE PROTECTION does not configure
DB_LOST_WRITE_PROTECT.
They are not alternative syntaxes
for the same feature.
The former enables Shadow Lost
Write Protection, while the latter is a Data Guard-oriented initialization
parameter.
4. Understanding
DB_LOST_WRITE_PROTECT
4.1 Parameter definition
The parameter is documented in the
Oracle Database Reference:
Its syntax is:
DB_LOST_WRITE_PROTECT = { AUTO | TYPICAL | FULL | NONE }
|
Property |
Value |
|
Parameter type |
String |
|
Default in current 26ai reference |
AUTO |
|
Modifiable |
ALTER SYSTEM |
|
Modifiable in a PDB |
Yes, starting with RU 23.7 |
|
Oracle RAC |
System-wide parameter |
The supported settings are:
4.2 TYPICAL
When configured as:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = TYPICAL
SCOPE = BOTH SID = '*';
Oracle records buffer cache reads
for read/write tablespaces in redo.
Read-only tablespaces are
excluded.
This provides lost write detection
information while avoiding the additional coverage of FULL.
4.3 FULL
When configured as:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = FULL
SCOPE = BOTH SID = '*';
Oracle also records buffer cache
reads for read-only tablespaces.
This provides broader
read-tracking coverage than TYPICAL, with potentially higher overhead.
4.4 NONE
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = NONE
SCOPE = BOTH SID = '*';
Disables the parameter-based lost
write detection mechanism.
This is not equivalent to
disabling Shadow Lost Write Protection. The two features are independent.
4.5 AUTO
Recommended starting point for
Data Guard in current Oracle guidance
AUTO makes Oracle determine when
to generate additional redo information on the primary and when to perform lost
write detection on the standby.
On the primary database:
- Oracle checks the status of physical
standby databases.
- Relevant buffer cache reads are logged
when physical standbys with real-time redo apply are available.
On the standby database:
- Oracle evaluates whether redo apply is
keeping up.
- When apply lag exceeds the applicable
threshold, lost write protection checks can temporarily be skipped.
- Checks resume when apply catches up.
The threshold is determined as
follows:
|
Data Guard configuration |
Threshold |
|
FSFO configured with an
apply-lag threshold |
Lesser of 60 seconds and
two-thirds of the FSFO apply-lag threshold |
|
No applicable FSFO threshold |
60 seconds |
The purpose is to avoid additional
standby processing overhead when the standby is already lagging, helping
preserve role-transition readiness.
4.6 Version considerations
It is important not to assume that
every Oracle release has the same default.
|
Oracle release |
Consideration |
|
11g and later |
Data Guard lost write
detection mechanism available |
|
18c |
Shadow Lost Write Protection introduced |
|
19c before RU 19.26 |
Do not assume AUTO is the
default |
|
19c RU 19.26 onwards |
AUTO introduced and made the default for the documented release
update |
|
23ai |
AUTO is available;
PDB-level parameter modification is supported starting RU 23.7 |
|
26ai |
AUTO, TYPICAL, FULL and NONE are documented |
Always check the actual RU and
parameter value on the installed system rather than relying solely on the
database major version.
https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/DB_LOST_WRITE_PROTECT.html
5. Configuring
DB_LOST_WRITE_PROTECT
This section provides a practical
configuration for an Oracle RAC + Data Guard environment.
Assume the following architecture:
- Primary: PRODDB
- Physical standby: STBYDB
- Oracle RAC: Two instances on the primary.
- Standby: Physical standby with real-time
redo apply.
5.1 Check the current
configuration
Execute on the primary and
standby:
SHOW PARAMETER
DB_LOST_WRITE_PROTECT;
Alternatively:
SELECT INST_ID,
NAME, VALUE, ISDEFAULT, ISMODIFIED FROM GV$PARAMETER
WHERE NAME =
'db_lost_write_protect';
5.2 Configure AUTO
For Oracle versions supporting
AUTO, the recommended starting point is:
On the primary:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = AUTO SCOPE = BOTH SID = '*';
On the physical standby:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = AUTO SCOPE = BOTH SID = '*';
In RAC, the parameter is
system-wide. Using SID='*' explicitly configures the shared SPFILE setting.
Important: AUTO does not mean that
protection is continuously active regardless of standby conditions. It
dynamically adjusts its behavior according to standby availability and apply
lag.
5.3 Configure TYPICAL
If you require lost write
detection without AUTO's adaptive behavior:
Primary:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = TYPICAL SCOPE = BOTH SID = '*';
Standby:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = TYPICAL SCOPE = BOTH SID = '*';
With TYPICAL:
- The primary records relevant reads for
read/write tablespaces.
- The standby performs lost write detection
during recovery.
- Standby detection is performed regardless
of higher redo apply lag.
5.4 Configure FULL
For broader read-tracking
coverage:
ALTER SYSTEM SET
DB_LOST_WRITE_PROTECT = FULL
SCOPE = BOTH SID = '*';
Configure this on both primary and
standby.
FULL includes read-only
tablespaces in the primary's read-tracking information.
Production consideration: FULL
should be selected only after evaluating its additional redo-generation and
recovery-processing overhead.
6. Configuring Shadow Lost Write
Protection
This is a separate implementation
procedure.
Unlike DB_LOST_WRITE_PROTECT,
Shadow Lost Write Protection requires a dedicated shadow tablespace.
6.1 Prerequisites
Before implementation, consider
the following:
|
Requirement |
Description |
|
Database compatibility |
18.0.0 or higher |
|
Shadow tablespace |
At least one must exist |
|
Tablespace type |
BIGFILE |
|
Required privilege |
CREATE TABLESPACE |
|
Enablement |
Database/PDB before
individual tablespaces or datafiles |
|
Storage capacity |
At least 2% of the protected datafile space as a planning
baseline |
Oracle automatically assigns
shadow tablespaces to protected datafiles. You do not manually choose which
shadow tablespace tracks an individual datafile.
The 2% figure is Oracle's
documented sizing guideline, not a guarantee that every workload will fit
within that amount.
https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/DB_LOST_WRITE_PROTECT.html
6.2 Step 1 – Create a shadow
tablespace
Example for Linux:
CREATE BIGFILE TABLESPACE
SHADOW_LWP
DATAFILE
'/u03/oradata/PRODDB/shadow_lwp01.dbf'
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE
50G LOST WRITE PROTECTION;
For ASM-based storage:
CREATE BIGFILE TABLESPACE
SHADOW_LWP
DATAFILE '+DATA' SIZE 10G AUTOEXTEND
ON NEXT 1G
MAXSIZE 50G LOST WRITE PROTECTION;
These are illustrative sizes.
Actual sizing must be determined from the protected datafile footprint,
workload, and observed growth.
Why a BIGFILE tablespace?
Oracle requires the shadow
tablespace to be a BIGFILE tablespace. Its purpose is to provide the storage
area for lost write tracking information.
6.3 Step 2 – Enable Shadow Lost
Write Protection at database level
For a non-CDB or CDB root:
ALTER DATABASE ENABLE LOST WRITE
PROTECTION;
For a PDB:
ALTER PLUGGABLE DATABASE ENABLE
LOST WRITE PROTECTION;
This step enables the feature at
the database or PDB level.
It does not automatically mean
that every application tablespace has been individually enabled for protection.
6.4 Step 3 – Enable protection for
selected tablespaces
For example:
ALTER TABLESPACE USERS
ENABLE LOST WRITE PROTECTION;
ALTER TABLESPACE APP_DATA
ENABLE LOST WRITE PROTECTION;
ALTER TABLESPACE APP_INDEX
ENABLE LOST WRITE PROTECTION;
When enabled at tablespace level,
all existing and subsequently added datafiles in that tablespace are included.
This is useful when you want to
protect a complete application storage domain.
6.5 Step 4 – Enable protection at
datafile level
If you do not want to enable
protection for an entire tablespace, you can configure individual datafiles.
For a datafile belonging to the
CDB root:
ALTER DATABASE DATAFILE
'/u02/oradata/PRODDB/users01.dbf' ENABLE
LOST WRITE PROTECTION;
For a datafile belonging to a PDB:
ALTER PLUGGABLE DATABASE DATAFILE '/u02/oradata/PRODDB/pdb01_app01.dbf'
ENABLE LOST WRITE PROTECTION;
The database or PDB must already
be enabled for Shadow Lost Write Protection, and a shadow tablespace must
exist.
6.6 Understanding the
configuration hierarchy
6.7 Suspending, removing and
disabling protection
Oracle provides different
operations for managing protection.
Suspend protection at tablespace
level:
ALTER TABLESPACE USERS SUSPEND
LOST WRITE PROTECTION;
Resume protection:
ALTER TABLESPACE USERS ENABLE LOST
WRITE PROTECTION;
When protection is suspended,
existing tracking information is preserved, but no new tracking information is
collected and protected block checks stop during the suspension.
Remove protection:
ALTER TABLESPACE USERS REMOVE LOST
WRITE PROTECTION;
Unlike suspension, removal deletes
the tracking information associated with that tablespace or datafile.
Disable database-level protection:
ALTER DATABASE DISABLE LOST WRITE
PROTECTION;
For a PDB:
ALTER PLUGGABLE DATABASE DISABLE
LOST WRITE PROTECTION;
Disabling database-level
protection does not delete the existing shadow tablespace or its tracking data.
To remove the storage itself, you must drop the shadow tablespace after
ensuring it is no longer needed.
7. What Happens When Oracle
Detects a Lost Write?
Detection is not equivalent to
automatic repair.
The objective is to prevent an
inconsistent block from being silently consumed and to provide the DBA with an
opportunity to investigate and recover the affected data.
7.1 Shadow Lost Write Protection
When Oracle detects an
inconsistent tracked block, it raises an error.
For example, the source document
describes ORA-6435 in its shadow lost write scenario.
The exact error and reporting
behavior should be checked against the relevant Oracle release and operation.
When the problem is detected
during an RMAN backup, Oracle terminates the backup operation rather than
continuing to back up the affected block as if it were valid.
7.2 Data Guard lost write
detection
Two important errors are:
|
Error |
Description |
|
ORA-00752 |
Recovery detected a lost
write on the primary |
|
ORA-00600 [3020] |
Recovery encountered a block/redo inconsistency that may
indicate a lost write |
An example of a primary lost write
detected during standby recovery:
ORA-00752: recovery detected a
lost write of a data block
ORA-10567: Redo is inconsistent
with data block
ORA-10564: tablespace TBS_2
ORA-01110: data file 7
The standby's managed recovery
process can stop to prevent propagation of the inconsistency.
7.3 Recovery after a primary lost
write
When a lost write on the primary
has been confirmed through Data Guard recovery, the physical standby may
provide the trustworthy database copy.
The general recovery approach is:
1.
Stop
and isolate the affected primary.
2.
Determine
the affected SCN and datafiles.
3.
Evaluate
the standby's consistency and recovery state.
4.
Follow
the Oracle-supported failover procedure.
5.
Back
up the new primary immediately.
6.
Rebuild
or reinstate the former primary as a standby using an appropriate recovery
procedure.
Oracle documents the following
activation command in its example recovery procedure:
ALTER DATABASE ACTIVATE STANDBY
DATABASE;
This is an example of an
activation-based failover procedure, not a universal replacement for the Data
Guard Broker failover workflow.
Important: Do not execute this
command simply because an ORA-00752 or ORA-00600 has appeared. First confirm
the failure scenario and determine the appropriate recovery procedure with
Oracle Support.
7.4 Recovery when the standby has
a lost write
If the lost write is on the
standby, the standby may need to be rebuilt or its affected files restored from
a valid backup.
The recovery method depends on the
scope of corruption and whether the primary remains healthy.
7.5 Can Shadow Lost Write
Protection repair the block?
No. It detects the problem; it is
not a replacement for RMAN recovery, Data Guard, or other recovery mechanisms.
Potential recovery approaches
include:
- RMAN block media recovery.
- RMAN datafile recovery.
- Restoring affected datafiles.
- Recovering from a healthy Data Guard
copy.
- Rebuilding a standby when necessary.
The correct method depends on
whether the corruption is isolated, whether the primary or standby is affected,
and whether other blocks have already been contaminated.
Why we need shadow lost write
protection.docx+1
8. Performance Implications and
Operational Challenges
Enabling lost write protection
introduces additional work. The overhead profile is different for the two
mechanisms.
8.1 Performance impact of
DB_LOST_WRITE_PROTECT
The parameter-based mechanism
introduces additional redo-generation and redo-apply processing.
Potential impacts include:
|
Area |
Potential impact |
|
Primary CPU |
Additional processing for
tracking buffer cache reads |
|
Primary redo generation |
Increased redo volume |
|
LGWR |
Additional redo workload |
|
Network |
Potential increase in redo transport traffic |
|
Standby CPU |
Additional lost write
detection processing |
|
Standby I/O |
Additional block reads during recovery |
|
Redo apply |
Increased apply workload
and possible apply lag |
TYPICAL and FULL have different
coverage. FULL includes read-only tablespaces and can therefore increase
overhead.
There is no universal performance
percentage that applies to every database. The actual impact depends on read
workload, cache behavior, redo throughput, storage latency, and Data Guard
configuration.
8.2 Performance impact of Shadow
Lost Write Protection
Shadow Lost Write Protection has a
different overhead profile.
It requires:
- Maintaining shadow tracking information.
- Additional I/O activity associated with
tracking.
- Shadow tablespace capacity.
- Validation when protected blocks are
read.
- Additional work during RMAN backup
operations.
Potential operational challenges
include:
|
Challenge |
Explanation |
|
Shadow storage sizing |
Insufficient space can
prevent complete tracking |
|
Storage contention |
Shadow tablespace I/O can compete with database I/O |
|
High DML activity |
Tracking maintenance can
increase overhead |
|
Backup duration |
Additional block validation can affect RMAN performance |
|
Datafile growth |
Tracking storage may need
to grow as protected datafiles grow |
|
Flashback Database |
Shadow tracking information is removed during database flashback
and must be repopulated |
|
Maintenance |
Suspend/remove operations
have different consequences for historical tracking information |
Oracle specifically warns that
when protected datafiles grow, insufficient shadow tablespace space can cause
tracking warnings and leave some data untracked.
8.3 A particularly important
challenge: Storage failure-domain independence
Consider an environment with:
- Oracle datafiles on ASM.
- Online redo logs on ASM.
- Shadow tablespaces on the same storage
infrastructure.
Although Shadow Lost Write
Protection provides additional detection, placing all these components in the
same storage failure domain may reduce the independence of the protection
architecture.
My recommendation is to evaluate
the placement of shadow tablespaces carefully.
Where the infrastructure permits,
use storage resources and paths that reduce correlated failure risks.
This is an infrastructure design
recommendation, not an Oracle requirement that shadow tablespaces must reside
on separate physical storage.
8.4 What should be benchmarked? Before
enabling protection on a production database, establish a baseline. I recommend
comparing:
|
Metric |
Before |
After |
|
Transactions per second |
Baseline |
Measured |
|
Average transaction latency |
Baseline |
Measured |
|
P95/P99 response time |
Baseline |
Measured |
|
Redo generation per second |
Baseline |
Measured |
|
LGWR write latency |
Baseline |
Measured |
|
DBWR write activity |
Baseline |
Measured |
|
Data Guard transport lag |
Baseline |
Measured |
|
Data Guard apply lag |
Baseline |
Measured |
|
CPU utilization |
Baseline |
Measured |
|
Shadow tablespace utilization |
N/A |
Measured |
|
RMAN backup duration |
Baseline |
Measured |
For Oracle RAC, also examine
interconnect behavior and instance-level workload distribution so that the
protection overhead is not confused with unrelated cluster bottlenecks.
9. Best Practices for Production
Environments
The following recommendations
combine Oracle's documented behavior with practical database infrastructure
design considerations.
9.1 For standalone Oracle
databases
Recommended approach: Shadow Lost
Write Protection
If you do not have Data Guard,
Shadow Lost Write Protection is particularly valuable because it does not
require a standby database.
Recommendations:
1.
Create
dedicated shadow tablespaces.
2.
Enable
protection at database or PDB level.
3.
Prioritize
business-critical application tablespaces.
4.
Monitor
shadow tablespace capacity.
5.
Include
protected blocks in regular RMAN backup testing.
6.
Benchmark
DML and read-intensive workloads.
7.
Maintain
reliable RMAN backups and recovery procedures.
9.2 For Oracle RAC without Data
Guard
RAC provides instance-level
availability, but it does not independently protect against a lost write in
shared storage.
If a shared storage subsystem
returns stale blocks to the database, multiple RAC instances may be exposed to
the same underlying problem.
Recommendations:
- Consider Shadow Lost Write Protection.
- Evaluate storage redundancy and
controller behavior.
- Monitor ASM and storage latency.
- Test recovery procedures.
- Do not consider RAC redundancy a
replacement for lost write detection.
9.3 For Oracle RAC with Data Guard
Recommended approach: Evaluate
both mechanisms
For mission-critical systems,
consider enabling both:
Layer 1 – Shadow Lost Write
Protection
Provides local tracking and
validation without depending on standby redo apply.
Layer 2 – Data Guard Lost Write
Detection
Provides an additional detection
mechanism by comparing primary and standby block history during recovery.
The two mechanisms complement each
other rather than duplicate the same implementation.
9.4 For banking and financial
databases
For a critical financial platform,
I would consider the following:
- Shadow Lost Write Protection for critical
application tablespaces.
- DB_LOST_WRITE_PROTECT=AUTO on supported
Data Guard configurations.
- Reliable synchronous or asynchronous Data
Guard architecture according to the required RPO.
- Regular RMAN validation and restore
testing.
- Independent storage failure-domain
assessment.
- Alerting for ORA-00752 and relevant
ORA-00600 errors.
- Capacity monitoring for shadow
tablespaces.
- Documented primary and standby recovery
runbooks.
Remember that lost write
protection does not replace Data Guard, RMAN, storage redundancy, or
application-level consistency controls.
10. Recommended Implementation
Architecture
For a high-availability banking
database, I would use a layered approach.
|
Protection layer |
Mechanism |
Purpose |
|
Database integrity |
Shadow Lost Write
Protection |
Local lost write detection |
|
Primary/standby integrity |
DB_LOST_WRITE_PROTECT=AUTO |
Redo-based lost write detection |
|
Database recovery |
RMAN |
Restore and recover
corrupted data |
|
High availability |
Oracle RAC |
Instance and node availability |
|
Disaster recovery |
Oracle Data Guard |
Standby database and role
transition |
|
Storage reliability |
ASM and enterprise storage redundancy |
Reduce storage failure risks |
|
Operational control |
Monitoring and alerting |
Detect failures and
capacity problems |
The important architectural
principle is that each layer addresses a different failure category.
For example, RAC may protect
against an instance failure, Data Guard may protect against site failure, and
Shadow Lost Write Protection may identify a stale block returned by storage.
No single mechanism replaces the
others.
11. Implementation Checklist and
Validation
I recommend implementing the
feature in a controlled sequence rather than enabling it across every
production tablespace immediately.
11.1 Pre-implementation checklist
- Confirm Oracle version and Release
Update.
- Confirm database compatibility level.
- Identify primary and standby roles.
- Check current DB_LOST_WRITE_PROTECT
configuration.
- Confirm real-time redo apply status.
- Check current redo generation and apply
lag.
- Identify critical application
tablespaces.
- Allocate shadow tablespace capacity.
- Review ASM disk groups and storage
failure domains.
- Capture performance baselines.
- Verify RMAN backup and restore
procedures.
11.2 Suggested SQL validation
commands
Check database role:
SELECT DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE,
PROTECTION_MODE,
PROTECTION_LEVEL FROM V$DATABASE;
Check parameter configuration:
SELECT INST_ID, NAME, VALUE FROM GV$PARAMETER
WHERE NAME =
'db_lost_write_protect';
Check tablespaces:
SELECT TABLESPACE_NAME, BIGFILE, STATUS, CONTENTS FROM
DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;
Check shadow tablespace capacity:
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024,
2) AS SIZE_GB
FROM DBA_DATA_FILES GROUP BY
TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;
Check Data Guard transport and
apply lag:
SELECT NAME, VALUE, UNIT, TIME_COMPUTED FROM
V$DATAGUARD_STATS
WHERE NAME IN (‘transport lag’, ‘apply
lag');
These queries provide useful
configuration and capacity information. They do not, by themselves, prove that
every datafile is actively protected by Shadow Lost Write Protection. That must
be validated against the supported protection configuration and operational
records for the installed release.
11.3 Suggested testing strategy
I would divide the implementation
into four phases.
|
Phase |
Activities |
Expected result |
|
1. Lab |
Configure both mechanisms
and execute representative workloads |
Validate functionality |
|
2. Performance |
Run read/write and RMAN workloads |
Measure overhead |
|
3. Pilot |
Enable protection for
selected production tablespaces |
Validate operational
behavior |
|
4. Production |
Extend according to business criticality |
Establish ongoing protection |
Do not attempt to simulate a lost
write by deliberately corrupting production storage or manipulating database
blocks.
Use a controlled Oracle lab and
Oracle-supported test procedures.
12. Important Technical
Considerations and Common Misunderstandings
There are several points worth
emphasizing.
1.
Shadow Lost Write Protection does not require Data Guard.
You
can use it in standalone databases and RAC environments.
2.
DB_LOST_WRITE_PROTECT is not a shadow tablespace feature.
It
uses redo-based tracking and standby recovery for detection.
3.
ALTER DATABASE ENABLE LOST WRITE PROTECTION does not configure
DB_LOST_WRITE_PROTECT.
They
enable independent mechanisms.
4.
Both mechanisms can coexist.
For
critical databases, evaluate their combined benefits and overhead.
5.
Detection is not automatic repair.
You
still need a valid recovery strategy.
6.
A standby database does not automatically guarantee that every lost write will
be detected immediately.
Detection
depends on the required redo information, standby configuration, recovery
progress, and blocks being checked.
7.
AUTO is adaptive.
It
can temporarily skip protection checks on a lagging standby. Therefore, AUTO
should not be interpreted as unconditional continuous checking.
8.
Shadow tablespace capacity is operationally important.
Insufficient
capacity can compromise tracking coverage.
9.
Database flashback has an important consequence.
Oracle removes shadow lost write
tracking information during a database flashback. Tracking is subsequently
repopulated as blocks are tracked again.
10. Do not confuse block
corruption detection with lost write detection.
DB_BLOCK_CHECKING,
DB_BLOCK_CHECKSUM, RMAN validation, and lost write protection address different
aspects of database integrity.
13. Final Comparison: Which
Mechanism Should You Choose?
|
Environment |
Recommended consideration |
|
Standalone database |
Shadow Lost Write
Protection |
|
RAC without Data Guard |
Shadow Lost Write Protection |
|
Physical Data Guard |
DB_LOST_WRITE_PROTECT=AUTO
or an explicitly selected mode |
|
RAC + Data Guard |
Evaluate both mechanisms |
|
Critical banking database |
Consider both, with
performance and recovery testing |
|
Read-only data workloads |
Evaluate FULL if using parameter-based detection and broader
coverage is required |
My recommendation for a
mission-critical Oracle infrastructure is to avoid thinking of these mechanisms
as competing solutions.
They provide different detection
capabilities.
Shadow Lost Write Protection
offers local block tracking, while Data Guard Lost Write Detection provides an
additional comparison mechanism through redo and standby recovery.
Combining them can provide defense
in depth, provided the associated storage, redo, CPU, and recovery overheads
are properly evaluated.
14. Conclusion
Lost writes are particularly
dangerous because they can remain invisible to the database even after the
storage subsystem has reported successful I/O completion.
Oracle provides two independent
mechanisms to address this problem:
- Shadow Lost Write Protection, which
maintains SCN-related tracking information in dedicated shadow
tablespaces.
- Data Guard Lost Write Detection, which
uses DB_LOST_WRITE_PROTECT to provide redo-based lost write detection
during standby recovery.
For database infrastructure teams,
the most important lesson is that database availability and database integrity
are not the same thing.
An Oracle RAC cluster can remain
available while sharing an underlying storage problem. A Data Guard standby can
provide an alternative database copy, but it also needs mechanisms to identify
inconsistencies. And RMAN can restore data, but it cannot guarantee that a
backup created after an undetected lost write is free from that corruption.
High availability is not
sufficient without data integrity.
A resilient Oracle architecture
should therefore combine availability, recoverability, and proactive corruption
detection.
15. Official Oracle Documentation
and References
The following Oracle documentation
should be used as the primary technical references when implementing these
features.
1. Oracle Database 26ai Reference
– DB_LOST_WRITE_PROTECT: Parameter syntax, values, AUTO behavior and defaults.
https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/DB_LOST_WRITE_PROTECT.html
2. Oracle Database 26ai
Administrator's Guide – Managing Tablespaces: Shadow Lost Write Protection
configuration, sizing and management.
https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/managing-tablespaces.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html
3. Oracle Database 26ai SQL
Language Reference – ALTER DATABASE: Database and datafile lost write
protection commands.
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-DATABASE.html
4. Oracle Database 26ai SQL
Language Reference – CREATE TABLESPACE: Creating shadow tablespaces.
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLESPACE.html
5. Oracle Database 26ai Backup and
Recovery User's Guide – Shadow Lost Write Protection: Interaction with RMAN
backups and lost write detection.
6. Oracle Database 26ai – Data
Corruption Prevention, Detection, and Repair: Oracle high-availability
recommendations.
7. — For investigating ORA-00752
and ORA-00600 [3020] recovery scenarios.
No comments:
Post a Comment