Managing Lost Write Protection with Shadow Tablespaces

Managing Lost Write Protection with Shadow Tablespaces

Lost Write of data block is occurred when an I/O subsystem acknowledges the completion of the block write even when the write did not occurred.An undetected lost write may result in corruption of data.It is used to prevent data corruption of Sensitive data. It is not important to use it to track all data.
Shadow lost write protection can protect against lost writes for tablespaces or data files.

Shadow lost write protection can minimize data loss and the time required to repair a database.You can enable it at tablespace or datafiles level.

Note: This feature present in Oracle 18c

For configured it, We need to follow the following steps:
Note: For enable Shadow Lost Writer Protection the compatibility level must be 18.0.0 or higher

1. Creating Shadow Tablespaces for Shadow Lost Write Protection

CREATE BIGFILE TABLESPACE Shadowtablespace DATAFILE 'C:\oracle\oradata\shadow_tablespace01.dbf' SIZE 10M LOST WRITE PROTECTION;

2. Enable the Shadow Lost Write Protection for a Database

-- Fon Non CDB or CDB Root
ALTER DATABASE ENABLE LOST WRITE PROTECTION;

-- FOR PDB
ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION;

3. Enable for Databata files or Tablespace as you needed
Note: Database Lost Write must be enabled before tablespace or Datafile

-- At tablespace level:
ALTER TABLESPACE users ENABLE LOST WRITE PROTECTION;

-- At CDB root or NON CDB level
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

-- At Pluggable database
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

4. Disabling Shadow Lost Write Protection
Note: When shadow lost write protection is disabled then tracking data is persevered in the tablespace,

--FOR CDB or NON CDB
ALTER DATABASE DISABLE LOST WRITE PROTECTION;

--FOR PDB
ALTER PLUGGABLE DATABASE DISABLE LOST WRITE PROTECTION;

5. Remove the Shadow lost write protection
Note: Remove shadow lost write protection for a data file or a tablespace, then its tracking data is deleted.

-- at tablespace level
ALTER TABLESPACE users REMOVE LOST WRITE PROTECTION;

--Data File Used by a Non-CDB
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

--FOR PDB datafiles
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

6. Drop tablespace of Shadow lost write protection

DROP TABLESPACE Shadowtablespace INCLUDING CONTENTS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.