8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)

The 12.1.0.2 patchset introduced the concept of PDB subset cloning, which allows a subset of all the tablespaces in a PDB to be cloned. Excluding tablespaces can be useful when you want to build a PDB to test a specific piece of functionality, which doesn't require the whole database. It is also useful when splitting instances that were used for consolidation into their individual functional areas.

Setup
PDB Subset Cloning

Setup

To see this feature working we will create a clean PDB, then add 3 new tablespaces, each with a default user and a single object in them. This will mimic a situation where a single database has been used to consolidate three different applications.

CONN / AS SYSDBA

-- Create a new PDB
CREATE PLUGGABLE DATABASE pdb20 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb20/');

ALTER PLUGGABLE DATABASE pdb20 OPEN;

ALTER SESSION SET CONTAINER = pdb20;

-- Create first TS, User, Table.
CREATE TABLESPACE ts1
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts101.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test1 IDENTIFIED BY test1
  DEFAULT TABLESPACE ts1
  QUOTA UNLIMITED ON ts1;

CREATE TABLE test1.t1 (
  id NUMBER
);
INSERT INTO test1.t1 VALUES (1);
COMMIT;


-- Create second TS, User, Table.
CREATE TABLESPACE ts2
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts201.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test2 IDENTIFIED BY test2
  DEFAULT TABLESPACE ts2
  QUOTA UNLIMITED ON ts2;

CREATE TABLE test2.t2 (
  id NUMBER
);
INSERT INTO test2.t2 VALUES (1);
COMMIT;


-- Create third TS, User, Table.
CREATE TABLESPACE ts3
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb20/ts301.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test3 IDENTIFIED BY test3
  DEFAULT TABLESPACE ts3
  QUOTA UNLIMITED ON ts3;

CREATE TABLE test3.t3 (
  id NUMBER
);
INSERT INTO test3.t3 VALUES (1);
COMMIT;

We can see the separation between the schema in the following query.

COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20
COLUMN tablespace_name FORMAT A20

SELECT owner, table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('T1','T2','T3')
ORDER BY owner;

OWNER                TABLE_NAME           TABLESPACE_NAME
-------------------- -------------------- --------------------
TEST1                T1                   TS1
TEST2                T2                   TS2
TEST3                T3                   TS3

SQL>

PDB Subset Cloning

PDB subset cloning is made possible using the USER_TABLESPACES clause, which allows you to specify the user-defined tablespaces to be included in the clone in one of several ways.

One of more named tablespaces in a comma separated list.
NONE : No user-defined tablespaces are included in the clone.
ALL : All user-defined tablespaces are included in the clone. This is the same as omitting the clause completely.
ALL EXCEPT : Exclude one or more named user-defined tablespaces as a comma separated list.

The following example creates a clone including a named list of tablespaces.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb21 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb21/')
  USER_TABLESPACES=('ts1', 'ts2');

ALTER PLUGGABLE DATABASE pdb21 OPEN;

ALTER SESSION SET CONTAINER = pdb21;

If we query the list of tablespaces, it appears all of them are present.

SELECT tablespace_name from dba_tablespaces;

TABLESPACE_NAME
--------------------
SYSTEM
SYSAUX
TEMP
TS1
TS2
TS3

6 rows selected.

SQL>

If we try to access the objects from each schema, we see this is not the case.

SQL> SELECT * FROM test1.t1;

        ID
----------
         1

SQL> SELECT * FROM test2.t2;

        ID
----------
         1

SQL> SELECT * FROM test3.t3;
SELECT * FROM test3.t3
                    *
ERROR at line 1:
ORA-00376: file 30 cannot be read at this time
ORA-01111: name for data file 30 is unknown - rename to correct file
ORA-01110: data file 30:
'/u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00030'


SQL>

As requested, the datafile for the TS3 tablespace has not been cloned, so we should do some post-clone clean up to make the PDB look consistent.

DROP TABLESPACE ts3 INCLUDING CONTENTS AND DATAFILES;
DROP USER test3 CASCADE;

The following example creates a clone with none of the user-defined tablespaces present.

CREATE PLUGGABLE DATABASE pdb22 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb22/')
  USER_TABLESPACES=NONE;

ALTER PLUGGABLE DATABASE pdb22 OPEN;

The following example clones all the user-defined tablespaces, which is the same as omitting the USER_TABLESPACES clauses.

CREATE PLUGGABLE DATABASE pdb23 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb23/')
  USER_TABLESPACES=ALL;

ALTER PLUGGABLE DATABASE pdb23 OPEN;

The ALL EXCEPT variant allows you to list those tablespaces to be excluded.

CREATE PLUGGABLE DATABASE pdb24 FROM pdb20
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb20/','/u01/app/oracle/oradata/cdb1/pdb24/')
  USER_TABLESPACES=ALL EXCEPT('ts3');

ALTER PLUGGABLE DATABASE pdb24 OPEN;

In all cases, you will need to perform the post-clone clean up operations to make the databases look consistent.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.