[23ai] Shadow Tablespaces

If you prefer to read in Spanish Spanish version.

The otherr day, while I was reading the Oracle’s website, 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:

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> 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> CREATE BIGFILE TABLESPACE STTab001 DATAFILE SIZE 10M LOST WRITE PROTECTION;

Tablespace created.

After that, we enable shadow lost write protection for the PDB:

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> 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> 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> 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> 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> 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> 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.