What Is Shadow Lost Write Protection And How To Enable It

Shadow Lost Write Protection was introduced in 18c, and of course is available with 19c as well.

Data block lost write occurs when an I/O subsystem acknowledges completion of the block write, but the actual write did not occur in the persistent storage.
There are lots of layers and software (virtualization) between the Oracle database, and the actual storage where the data is written to.

Shadow Lost Write Protection can help with detecting if a lost write occurred.
The purpose of this feature is to:

• minimize data loss
• minimize time required to repair a database
• detect data corruption

How does Shadow Lost Write feature work?

The Shadow Lost Write protection is achieved by setting up a shadow tablespace, for tracking changes.

When a data block that is tracked is read from disk, the shadow lost write protection compares the SCN that is recorded in the shadow tablespace,
with the SCN of the most recent write in the data block. If the SCN entry in the shadow tablespace is greater than the SCN of the data block being read, then a lost write occurred. When this is detected, an error is returned.

Shadow lost write protection can be enabled at data file level or tablespace level. The feature is enabled for DML statements, SQL Loader conventional path load and direct path load, RMAN backups.
RMAN also checks for lost write, and raises errors if it finds any. If the lost write is not detected, then data corruption can occur, as the wrong version of the data (the incorrect data) could be used in other DML transactions.

Once the feature is enabled, Oracle assigns a tracked data file to a specific shadow tablespace automatically. You cannot explicitly specify which shadow tablespace is used for which data file, if you have multiple shadow tablespaces. The amount of space in the shadow tablespace is at least 2% of the space used by the data file you enabled shadow lost write protection for.

What is a shadow tablespace?
There are some constraints on the shadow tablespace. This tablespace has to be a bigfile tablespace, that contains system change numbers (SCN) for tracked data files. The tablespace is flexible to administer.

Steps to enable Shadow Lost Write Protection

1) Create a shadow tablespace with the lost write protection clause, that will contain the SCN numbers for tracked data files

CREATE BIGFILE TABLESPACE LOST_PROTECTION_TBS DATAFILE 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.dbf' size 10M autoextend on maxsize unlimited LOST WRITE PROTECTION;

2) Enable shadow lost write protection for the database. You can enable it for non-CDB, CDB, or pluggable database. No lost write protection is started yet at this point, the feature is only enabled for the database.

For a non-CDB or the CDB root:

ALTER DATABASE ENABLE LOST WRITE PROTECTION;

For a PDB:

ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION;

The privileges you need to be able to perform these actions are: alter database or alter pluggable database.

To be able to do perform this action, the following have to be true:

• the compatibility level for the database has to be at least 18.0.0
• a shadow lost write tablespace has to already exists in the database. This was created in Step1. If the tablespace is missing, you will receive the following error: ORA-65491: A lost write bigfile tablespace must exist before attempting to enable lost write

3) Enable shadow lost write protection for a tablespaces or for specific data files.
To enable shadow lost write for a tablespace use the alter tablespace enable lost write protection clause:

ALTER TABLESPACE users ENABLE LOST WRITE PROTECTION;

To enable shadow lost write for a datafile use the alter database datafile enable lost write protection clause:

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF' ENABLE LOST WRITE PROTECTION;

4) You can verify if the shadow lost write is enabled for the database:

select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME='NEW_LOST_WRITE';

PROPERTY_NAME PROPERTY_VALUE
--------------- --------------
NEW_LOST_WRITE TRUE

You can verify which tablespace is the one that tracks the information, and which tablespaces are tracked:

select tablespace_name, contents, lost_write_protect from dba_tablespaces;

TABLESPACE_NAME CONTENTS LOST_WR
------------------------------ --------------------- -------
SYSTEM PERMANENT OFF
SYSAUX PERMANENT OFF
UNDOTBS1 UNDO OFF
TEMP TEMPORARY OFF
USERS PERMANENT ENABLED
LOST_PROTECTION_TBS LOST WRITE PROTECTION OFF

5) You can disable shadow lost write protection the same way you enabled it.

ALTER TABLESPACE users DISABLE LOST WRITE PROTECTION;

6) You can remove or suspend the lost write protection.

When you suspend the lost write protection, no new tracking data is collected, however the existing tracking data is preserved.
When you remove the lost write protection, no new tracking data is collected, and the existing tracking data is removed as well.

ALTER TABLESPACE users REMOVE LOST WRITE PROTECTION;
ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF' SUSPEND LOST WRITE PROTECTION;

Putting it all together, here is a mind map about this feature:

A great article on this subject has been also written by Franck Pachot, and you can read it here.

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!