# [23ai] Shadow Tablespaces *If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/23ai-shadow-tablespaces)*.* The otherr day, while I was reading the Oracle’s website[,](https://dbaenlasombra.com/ogg-lentitud-de-replicat) I came across a type of tablespace that I wasn’t familiar with. It’s called “**Shadow Tablespaces”**. It’s not a feature in **23ai,** so it was introduced in **18c**. Before we get our hands dirty with a small example, I want to clarify that **Shadow tablespace** is not related to the parameter *DB\_LOST\_WRITE\_PROTECT*. ## **Purpose** **Shadow Tablespace** is a kind of ***tablespace***. In 23ai,it’s always bigfile. It provides fast detection againts lost writes for ***tablespaces*** or ***datafiles***, minimize the time required in order to repair a database. ## How does a Shadow Tablespace work? In order to use this functionality, we need both a **shadow tablespace** and **non-shadow tablespace**. Let’s check the following figure: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1759578208948/10488d8d-3acd-46d3-b3ee-43fec08a70d1.png align="center") Here, we can see that the **shadow tablespace “*STTab001*”** tracks several **tablespaces.** These tablespaces are different: on one hand, one is a **bigfile** tablespace **“*Tab002*”**, and on the other hand, the other is a **smallfile** tablespace **“*SmallTab001*”.** How come both tablespaces are different? Because I want to show you how this functionality can be used at different levels: tablespace and datafile. So, let's get down to business! Let’s set up our PDB. First of all, we’re going to create the “**shadow tablespace“** before enabling it. This step is very important because if we enable before creating the tablespace, we will receive the following error: ```sql SQL> ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION; ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION * ERROR at line 1: ORA-65491: A lost write bigfile tablespace must exist before attempting to enable lost write. Help: https://docs.oracle.com/error-help/db/ora-65491/ ``` Let’s create the tablespace *STTab001*: ```sql SQL> CREATE BIGFILE TABLESPACE STTab001 DATAFILE SIZE 10M LOST WRITE PROTECTION; Tablespace created. ``` After that, we enable shadow lost write protection for the PDB: ```sql SQL> r 1* select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME='NEW_LOST_WRITE' no rows selected SQL> ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION; Pluggable database altered. SQL> r 1* select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME='NEW_LOST_WRITE' PROPERTY_NAME PROPERTY_VALUE -------------------- -------------------- NEW_LOST_WRITE TRUE ``` If we had tried to enable from **CDB$Root** in order to apply it to all PDB\*\*,\*\* we would have received the following error: ```sql SQL> alter session set container=cdb$root; Session altered. SQL> CREATE BIGFILE TABLESPACE STTab001 DATAFILE SIZE 10M LOST WRITE PROTECTION; Tablespace created. SQL> ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION; ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION * ERROR at line 1: ORA-65046: operation not allowed from outside a pluggable database Help: https://docs.oracle.com/error-help/db/ora-65046/ ``` Let’s create both tablespaces: ```sql SQL> CREATE BIGFILE TABLESPACE Tab002 DATAFILE SIZE 10M; Tablespace created. SQL> CREATE SMALLFILE TABLESPACE SmallTab001 DATAFILE SIZE 1M ; Tablespace created. SQL> ALTER TABLESPACE SmallTab001 ADD DATAFILE SIZE 1M: Tablespace altered. ``` Let’s verify the previous step: ```sql SQL> r 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 OFF STTAB001 LOST WRITE PROTECTION OFF TAB002 PERMANENT OFF SMALLTAB001 PERMANENT OFF SQL> r 1 Select Tablespace_Name,LOST_WRITE_PROTECT ,File_Name, File_Id 2 From Dba_Data_Files 3 Where Tablespace_Name in ('STTAB001','TAB002','SMALLTAB001') 4* order by file_id TABLESPACE_NAME LOST_WR FILE_NAME FILE_ID ------------------------------ ------- ---------------------------------------------------------------------------------------------------- ---------- STTAB001 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/sttab001.280.1213616675 13 TAB002 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/tab002.281.1213625663 14 SMALLTAB001 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.282.1213625677 15 SMALLTAB001 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.283.1213625699 16 ``` Now, let’s enable **Shadow Lost Write Protection** for the tablespace “*TAB002”* and the datafile “*SMALLTAB001”*: ```sql SQL> ALTER TABLESPACE TAB002 ENABLE LOST WRITE PROTECTION; Tablespace altered. SQL> ALTER DATABASE DATAFILE '+DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.283.1213625699' ENABLE LOST WRITE PROTECTION; Database altered. ``` Let’s verify again: ```sql SQL> 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 OFF STTAB001 LOST WRITE PROTECTION OFF TAB002 PERMANENT ENABLED SMALLTAB001 PERMANENT OFF 8 rows selected. SQL> r 1 Select Tablespace_Name,LOST_WRITE_PROTECT ,File_Name, File_Id 2 From Dba_Data_Files 3 Where Tablespace_Name in ('STTAB001','TAB002','SMALLTAB001') 4* order by file_id TABLESPACE_NAME LOST_WR FILE_NAME FILE_ID ------------------------------ ------- ---------------------------------------------------------------------------------------------------- ---------- STTAB001 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/sttab001.280.1213616675 13 TAB002 ENABLED +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/tab002.281.1213625663 14 SMALLTAB001 OFF +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.282.1213625677 15 SMALLTAB001 ENABLED +DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.283.1213625699 16 ``` As you can see, it’s very easy to enable it. However, if you need to disabled it, you should do the following: ```sql SQL> ALTER TABLESPACE TAB002 REMOVE LOST WRITE PROTECTION; Tablespace altered. SQL> ALTER PLUGGABLE DATABASE DATAFILE '+DATA/DB23AI_FWX_MAD/4004456EB4523134E0634C00000AA230/DATAFILE/smalltab001.283.1213625699' SUSPEND LOST WRITE PROTECTION; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE DISABLE LOST WRITE PROTECTION; Pluggable database altered. SQL> DROP TABLESPACE STTAB001 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. ``` Looking forward to seeing you in the next article.