Introduction

We know that, with Oracle 12c we can clone a pluggable database to another pluggable database using the CREATE PLUGGABLE DATABASE statement. However, with Oracle 12c release 12.1.0.2; we can also clone a subset of the pluggable database to another pluggable database rather than cloning the entire pluggable database. This type of cloning is termed as PDB Subset Cloning. In the PDB subset cloning, we can decide which of the user defined (non system) tablespaces to be included or excluded while cloning the source PDB to another PDB. To facilitate the PDB subset cloning, Oracle has introduced a new clause called USER_TABLESPACES in the CREATE PLUGGABLE DATABASE statement.

We can use the USER_TABLESPACES clause to specify which of the tablespaces from source pluggable database to be cloned to the target pluggable database. USER_TABLESPACES clause can be defined in any of the following ways.

  • None: None of the user defined tablespaces will be included in the clone. Clone PDB will have only system tablespaces.
  • ALL: All of the user defined tablespaces will be included in the clone. Same as the default clone operation.
  • ALL EXCEPT: All of the user defined tablespaces will be included excluding the tablespaces listed
  • A list of comma separated tablespaces to be included in the clone

We will cover all of these options in the upcoming sections.

Demonstration

Let's go through a quick demonstration to understand, how this feature works. In the following demonstration, I have a pluggable database called CDB2_PDB_1 with few user defined tablespaces as shown below.

---//
---// source pluggable database with user defined tablespaces //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB_1                     READ WRITE NO


SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_1

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
EVENT_TS
MYAPP_TS
SPLEX_TS
SYSAUX
SYSTEM
TEMP
USERS

7 rows selected.

---//
---// list of objects from each user defined tablespaces //---
---//
SQL> select owner,segment_name,segment_type,tablespace_name
  2  from dba_segments
  3  where tablespace_name in ('MYAPP_TS','EVENT_TS','SPLEX_TS') order by  4,1,3,2;

OWNER           SEGMENT_NAME                   SEGMENT_TYPE         TABLESPACE_NAME
--------------- ------------------------------ -------------------- ------------------------------
EVENTS          EVENT_MSG_PK                   INDEX                EVENT_TS
                EVENT_MSG                      TABLE                EVENT_TS
MYAPP           EMP_INFO_PK                    INDEX                EVENT_TS
                EMP_INFO                       TABLE                EVENT_TS
                IDX_T_PART_AYSNC_G             INDEX                MYAPP_TS
                T_MYAPP_ORDERS_PK
                T_MYAPP_PRODUCTS_PK
                T_MYAPP_USERS_PK
                T_PART_AYSNC_PK                INDEX PARTITION      MYAPP_TS
                T_PART_AYSNC_PK
                T_PART_AYSNC_PK
                T_MYAPP_ORDERS                 TABLE                MYAPP_TS
                T_MYAPP_PRODUCTS
                T_MYAPP_USERS
                T_PART_AYSNC                   TABLE PARTITION      MYAPP_TS
                T_PART_AYSNC
                T_PART_AYSNC
SPLEX           SHAREPLEX_OBJMAP_I1            INDEX                SPLEX_TS
                SHAREPLEX_ROUTES_I1
                SHAREPLEX_ROUTES_I2
                SHAREPLEX_ACTID                TABLE                SPLEX_TS
                SHAREPLEX_CONFIG
                SHAREPLEX_JOBS_CONFIG
                SHAREPLEX_MARKER
                SHAREPLEX_OBJMAP
                SHAREPLEX_ROUTES
                SHAREPLEX_TRANS

27 rows selected.

As we can see we have objects from MYAPP schema spanning across multiple tablespaces namely EVENT_TS and MYAPP_TS. We will explore the effect of PDB subset cloning when we clone only one of these tablespaces in the upcoming sections.

Clone only the system tablespaces

Let's try out the first method of PDB subset cloning by using the NONE option for the USER_TABLESPACES clause as shown below. In this method none of the user defined tablespaces will be cloned to the target pluggable database.

---//
---// example 1: Clone none of the user defined tablespaces //---
---//
SQL> CREATE PLUGGABLE DATABASE cdb2_pdb_2 FROM cdb2_pdb_1
  2  FILE_NAME_CONVERT=('/data/oracle/orpcdb2/cdb2_pdb_1/','/data/oracle/orpcdb2/cdb2_pdb_2/')
  3  USER_TABLESPACES=NONE
  4  ;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE cdb2_pdb_2 OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB_1                     READ WRITE NO
         4 CDB2_PDB_2                     READ WRITE NO

We have performed a PDB subset cloning of pluggable database CDB2_PDB_1 to a new pluggable database CDB2_PDB_2 by skipping all of the user defined tablespaces. Let's take a look into the cloned pluggable database CDB2_PDB_2 and validate if the user defined tablespaces are skipped.

---//
---// validating tablespaces in the cloned PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_2

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
EVENT_TS
MYAPP_TS
SPLEX_TS
SYSAUX
SYSTEM
TEMP
USERS

7 rows selected.

---//
---// validate objects in the cloned PDB //---
---//
SQL> select owner,segment_name,segment_type,tablespace_name
  2  from dba_segments
  3  where tablespace_name in ('MYAPP_TS','EVENT_TS','SPLEX_TS') order by  4,1,3,2;

OWNER           SEGMENT_NAME                   SEGMENT_TYPE         TABLESPACE_NAME
--------------- ------------------------------ -------------------- ------------------------------
EVENTS          EVENT_MSG_PK                   INDEX                EVENT_TS
                EVENT_MSG                      TABLE                EVENT_TS
MYAPP           EMP_INFO_PK                    INDEX                EVENT_TS
                EMP_INFO                       TABLE                EVENT_TS
                IDX_T_PART_AYSNC_G             INDEX                MYAPP_TS
                T_MYAPP_ORDERS_PK
                T_MYAPP_PRODUCTS_PK
                T_MYAPP_USERS_PK
                T_PART_AYSNC_PK                INDEX PARTITION      MYAPP_TS
                T_PART_AYSNC_PK
                T_PART_AYSNC_PK
                T_MYAPP_ORDERS                 TABLE                MYAPP_TS
                T_MYAPP_PRODUCTS
                T_MYAPP_USERS
                T_PART_AYSNC                   TABLE PARTITION      MYAPP_TS
                T_PART_AYSNC
                T_PART_AYSNC
SPLEX           SHAREPLEX_OBJMAP_I1            INDEX                SPLEX_TS
                SHAREPLEX_ROUTES_I1
                SHAREPLEX_ROUTES_I2
                SHAREPLEX_ACTID                TABLE                SPLEX_TS
                SHAREPLEX_CONFIG
                SHAREPLEX_JOBS_CONFIG
                SHAREPLEX_MARKER
                SHAREPLEX_OBJMAP
                SHAREPLEX_ROUTES
                SHAREPLEX_TRANS

27 rows selected.

Even though we had skipped all of the user defined tablespaces, looks like none of them were skipped during the cloning process and all the objects from the user defined tablespaces are cloned to the new PDB. However, that is not the case; if we try to query objects from the user defined tablespaces, we will come to know about that fact as shown below.

---//
---// query objects from skipped user defined tablespaces //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_2

SQL> select count(*) from MYAPP.EMP_INFO;
select count(*) from MYAPP.EMP_INFO
                           *
ERROR at line 1:
ORA-00376: file 20 cannot be read at this time
ORA-01111: name for data file 20 is unknown - rename to correct file
ORA-01110: data file 20: '/app/oracle/product/12.1.0.2/dbs/MISSING00020'


SQL> select count(*) from EVENTS.EVENT_MSG;
select count(*) from EVENTS.EVENT_MSG
                            *
ERROR at line 1:
ORA-00376: file 20 cannot be read at this time
ORA-01111: name for data file 20 is unknown - rename to correct file
ORA-01110: data file 20: '/app/oracle/product/12.1.0.2/dbs/MISSING00020'


SQL>  select count(*) from SPLEX.SHAREPLEX_ACTID;
 select count(*) from SPLEX.SHAREPLEX_ACTID
                            *
ERROR at line 1:
ORA-00376: file 18 cannot be read at this time
ORA-01111: name for data file 18 is unknown - rename to correct file
ORA-01110: data file 18: '/app/oracle/product/12.1.0.2/dbs/MISSING00018'

As we can observe, when we try to query the objects from user defined tablespaces; we encounter the errors indicating that the underlying data file can't be read. This because when we use the PDB subset cloning, only the datafiles belonging to the subset tablespaces are copied. If we look into the status of the tablespaces in the cloned PDB, we could see that the skipped tablespaces are marked as OFFLINE. Further, if we query the v$datafile view from the cloned PDB, we could see all the skipped datafiles are OFFLINE and are pointing to the default datafile location ($ORACLE_HOME/dbs) with the dataffile name prefixed as MISSING as shown below.

---//
---// skipped user defined tablespaces are marked as OFFLINE //---
---//
SQL>  select tablespace_name,status from dba_tablespaces order by 2;

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          OFFLINE
MYAPP_TS                       OFFLINE
SPLEX_TS                       OFFLINE
EVENT_TS                       OFFLINE
TEMP                           ONLINE
SYSAUX                         ONLINE
SYSTEM                         ONLINE

---//
---// skipped datafiles are marked as OFFLINE and missing //---
---//
SQL> select name,status from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ -------
/data/oracle/orpcdb2/undotbs01.dbf                           ONLINE
/data/oracle/orpcdb2/cdb2_pdb_2/system01.dbf                 SYSTEM
/data/oracle/orpcdb2/cdb2_pdb_2/sysaux01.dbf                 ONLINE
/app/oracle/product/12.1.0.2/dbs/MISSING00017                OFFLINE
/app/oracle/product/12.1.0.2/dbs/MISSING00018                OFFLINE
/app/oracle/product/12.1.0.2/dbs/MISSING00019                OFFLINE
/app/oracle/product/12.1.0.2/dbs/MISSING00020                OFFLINE

7 rows selected.

During the PDB subset cloning, the objects belonging to the skipped tablespaces are not deleted from the data dictionary and therefore we could see all the objects are listed in the cloned PDB. This implies that, when we perform a PDB subset cloning we need to perform some post cloning clean up to get rid of the missing objects which were not cloned during PDB subset cloning.

Let's perform the post cloning clean up by dropping all the skipped (OFFLINE) tablespace from the cloned PDB and subsequently dropping any database objects which are pointing to the OFFLINE tablespaces.

---//
---// dropping all the OFFLINE (skipped) tablespaces //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_2

SQL> DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES;
Enter value for tablespace_name: MYAPP_TS
old   1: DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES
new   1: DROP TABLESPACE MYAPP_TS INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

SQL> /
Enter value for tablespace_name: SPLEX_TS
old   1: DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES
new   1: DROP TABLESPACE SPLEX_TS INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

SQL> /
Enter value for tablespace_name: EVENT_TS
old   1: DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES
new   1: DROP TABLESPACE EVENT_TS INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

SQL> /
Enter value for tablespace_name: USERS
old   1: DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES
new   1: DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES

DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

---//
---// create a new tablespace and set it as default //---
---//
SQL> CREATE TABLESPACE PDB2_USER DATAFILE '/data/oracle/orpcdb2/cdb2_pdb_2/pdb2_users01.dbf' size 100M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE PDB2_USER;

Database altered.

---//
---// drop the OFFLINE (skipped) default tablespace //---
---//
SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

---//
---// validate tablespace and datafile status //---
---//
SQL> select tablespace_name,status from dba_tablespaces order by 2;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
PDB2_USER                      ONLINE
TEMP                           ONLINE
SYSAUX                         ONLINE

SQL> select name,status from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ -------
/data/oracle/orpcdb2/undotbs01.dbf                           ONLINE
/data/oracle/orpcdb2/cdb2_pdb_2/system01.dbf                 SYSTEM
/data/oracle/orpcdb2/cdb2_pdb_2/sysaux01.dbf                 ONLINE
/data/oracle/orpcdb2/cdb2_pdb_2/pdb2_users01.dbf             ONLINE

---//
---// validate skipped objects are not present in data dictionary //---
---//
SQL> select owner,segment_name,segment_type,tablespace_name
  2  from dba_segments
  3  where tablespace_name in ('MYAPP_TS','EVENT_TS','SPLEX_TS') order by  4,1,3,2;

no rows selected

---//
---// drop the users belonging to OFFLINE (skipped) tablespaces //---
---//
SQL> select 'DROP USER '||username||' CASCADE;' from dba_users where default_tablespace in ('MYAPP_TS','EVENT_TS','SPLEX_TS');

'DROPUSER'||USERNAME||'CASCADE;'
------------------------------------------
DROP USER SPLEX CASCADE;
DROP USER MYAPP CASCADE;
DROP USER EVENTS CASCADE;

SQL> DROP USER SPLEX CASCADE;

User dropped.

SQL> DROP USER MYAPP CASCADE;

User dropped.

SQL> DROP USER EVENTS CASCADE;

User dropped.

We have performed the post cloning clean up and the cloned PDB is now in a VALID state. In the first method of PDB subset cloning, we have explored, how we can skip all the user defined tablespaces during the cloning process and the clean up operations that we need to perform after doing a PDB subset cloning.

Clone a [sub]set of tablespaces

We can also perform PDB subset cloning to include or excluded one or many user defined tablespaces during the cloning process using the following methods.

---//
---// example 2: clone all user defined tablespaces except SPLEX_TS and EVENT_TS //---
---//
SQL> CREATE PLUGGABLE DATABASE cdb2_pdb_3 from cdb2_pdb_1
  2  FILE_NAME_CONVERT=('/data/oracle/orpcdb2/cdb2_pdb_1/','/data/oracle/orpcdb2/cdb2_pdb_3/')
  3  USER_TABLESPACES=ALL EXCEPT('SPLEX_TS','EVENT_TS')
  4  ;

Pluggable database created.

---//
---// example 3: clone user defined tablespaces USERS and MYAPP_TS //---
---//

SQL> CREATE PLUGGABLE DATABASE cdb2_pdb_4 from cdb2_pdb_1
  2  FILE_NAME_CONVERT=('/data/oracle/orpcdb2/cdb2_pdb_1/','/data/oracle/orpcdb2/cdb2_pdb_4/')
  3  USER_TABLESPACES=('USERS','MYAPP_TS')
  4  ;
  
Pluggable database created.


SQL> alter pluggable database CDB2_PDB_3, CDB2_PDB_4 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB_1                     READ WRITE NO
         4 CDB2_PDB_2                     READ WRITE NO
         5 CDB2_PDB_3                     READ WRITE NO
         6 CDB2_PDB_4                     READ WRITE NO
     

The point to note here is that, when we perform a PDB subset cloning to exclude (skip) a set of user defined tablespaces, we must perform the post cloning clean up to get rid of all the OFFLINE (skipped) objects from the cloned pluggable database as demonstrated earlier.

If we have a schema which has objects spanned over multiple tablespaces and we are skipping one of those tablespaces during the PDB subset cloning, then the objects from the OFFLINE (skipped) tablespaces will inaccessible within the cloned pluggable database. As in our example 2 and 3, where we have cloned only the USERS and MYAPP_TS tablespaces. However, the schema MYAPP had objects spanned across MYAPP_TS and EVENT_TS tablespaces in the source pluggable database as listed earlier. Now, when we try to access the MYAPP schema objects from the OFFLINE (skipped) tablespace, we will encounter errors as shown below.

---//
---// validate the cloned PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB_3

SQL> select table_name from dba_tables where owner='MYAPP';

TABLE_NAME
------------------------
EMP_INFO
T_MYAPP_USERS
T_MYAPP_PRODUCTS
T_MYAPP_ORDERS
T_PART_AYSNC

---//
---// query MYAPP tables //--
---//
SQL> select count(*) from MYAPP.T_MYAPP_USERS;

  COUNT(*)
----------
         3

---//
---// not able to access MYAPP table from skipped tablespace //--
---//
         
SQL> select count(*) from MYAPP.EMP_INFO;
select count(*) from MYAPP.EMP_INFO
                           *
ERROR at line 1:
ORA-00376: file 27 cannot be read at this time
ORA-01111: name for data file 27 is unknown - rename to correct file
ORA-01110: data file 27: '/app/oracle/product/12.1.0.2/dbs/MISSING00027'

As expected, trying to query EMP_INFO table from MYAPP schema resulted into errors as the table was located in a OFFLINE (skipped) tablespace.

Clone all of the tablespaces

By default the CREATE PLUGGABLE DATABASE statement clones everything from the source pluggable database to the target pluggable database. However, we also explicitly mention this using the ALL option of USER_TABLESPACES clause as shown below.

---//
---// example 4: clone all user defined tablespaces (default cloning behaviour) //---
---// 
SQL> CREATE PLUGGABLE DATABASE cdb2_pdb_5 from cdb2_pdb_1
  2  FILE_NAME_CONVERT=('/data/oracle/orpcdb2/cdb2_pdb_1/','/data/oracle/orpcdb2/cdb2_pdb_5/')
  3  USER_TABLESPACES=ALL
  4  ;

Pluggable database created.  

SQL>  alter pluggable database CDB2_PDB_5 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB2_PDB_1                     READ WRITE NO
         4 CDB2_PDB_2                     READ WRITE NO
         5 CDB2_PDB_3                     READ WRITE NO
         6 CDB2_PDB_4                     READ WRITE NO
         7 CDB2_PDB_5                     READ WRITE NO

This is just a customary option and behaves same as the default cloning method for a pluggable database. In this case, we don't need to perform any post cloning cleanup setps as we have included all the tablespaces for cloning.

Conclusion

PDB subset cloning is very important and useful feature especially for the scenarios where we need to clone a part of the pluggable database. We can do that with a single command (courtesy to PDB subset cloning) without needing to go through multiple steps to have the desired subset in the the cloned pluggable database. However, we need to be little cautious if we have a schema which has objects spanning over multiple tablespaces as we may end up cloning only part of the schema objects (which may not be intended requirement). It would be a good practise to consolidate the schema objects under the desired subset of tablespaces before performing a PDB subset cloning. Further, when we clone a subset of tablespaces from source to target, we must  manually get rid of the skipped (OFFLINE) tablespaces and their associated objects.