Shadow Tablespaces in Oracle 18c

A shadow tablespace is a bigfile tablespace intended for shadow lost write protection.

Purpose of Shadow Tablespaces

Shadow lost write protection provides fast detection and immediate response to a lost write.

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write even though the writers did not occur or when a former image of the block overwrites the current image.

An undetected lost write can result in data corruption because the incorrect data can be used for other DML transactions. For example, a transaction can read old and incorrect data from one table, and then update hundreds of other tables based on this data. In this way, data corruption can spread throughout the database.

Shadow lost write protection provides the following benefits:

• It detects a lost write before it is consumed for standard DML, SQL*Loader conventional path load, direct path load, and RMAN backups.
• No standby database is necessary, as in the lost write protection introduced in Oracle Database 11g.
• You can enable shadow lost write protection for specific tablespaces and data files. You do not need to track all data.
• You can replace one shadow tablespace with another to change its configuration or location.
• You can suspend and resume shadow lost write protection for a tablespace or data file.
• You can enable or disable it for the entire non-CDB or PDB with a single ALTER DATABASE … LOST WRITE  PROTECTION statement

How Shadow Tablespaces Work

Creating Shadow Tablespaces for Shadow Lost Write Protection
To create a shadow tablespace for shadow lost write protection, issue a CREATE BIGFILE TABLESPACE statement with the LOST WRITE PROTECTION clause.

Note: For creating shadow tablespaces, the database compatibility level must be 18.0.0 or higher.

If we don’t create bigfile tablespace, then we are not able to enable lost write protection mode.

SQL> ALTER DATABASE ENABLE LOST WRITE PROTECTION;
ALTER DATABASE ENABLE LOST WRITE PROTECTION
*
ERROR at line 1:
ORA-65491: A lost write bigfile tablespace must exist before attempting to enable lost write

Creating bigfile tablespace for shadowing

SQL> CREATE BIGFILE TABLESPACE shadowtbs DATAFILE '/u01/app/oracle/oradata/CDB18C/shadowtbs1.df' SIZE 10M LOST WRITE PROTECTION;

Tablespace created.

SQL>

Enabling Shadow Lost Write Protection for a Database

SQL> ALTER DATABASE ENABLE LOST WRITE PROTECTION;

Database altered.

SQL>

Enabling Shadow Lost Write Protection for Tablespaces

SQL> ALTER TABLESPACE users ENABLE LOST WRITE PROTECTION;

Tablespace altered.

SQL> select tablespace_name,LOST_WRITE_PROTECT from dba_tablespaces;

TABLESPACE_NAME     LOST_WR
------------------- -------
SYSTEM              OFF
SYSAUX              OFF
UNDOTBS1            OFF
TEMP                OFF
USERS               ENABLED
SHADOWTBS           OFF

Remove or suspend shadow lost write protection for a tablespace

SQL> ALTER TABLESPACE users REMOVE LOST WRITE PROTECTION;

Tablespace altered.

SQL> select tablespace_name,LOST_WRITE_PROTECT from dba_tablespaces;

TABLESPACE_NAME        LOST_WR
---------------------- -------
SYSTEM                 OFF
SYSAUX                 OFF
UNDOTBS1               OFF
TEMP                   OFF
USERS                  OFF
SHADOWTBS              OFF

6 rows selected.

Disabling Shadow Lost Write Protection for a Database

SQL> ALTER DATABASE DISABLE LOST WRITE PROTECTION;

Database altered.

SQL>

Stay tuned for more articles on Oracle 18c 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp