Thursday, 15 June 2017

Multitenant : PDB Subset Cloning in Oracle Database 12c

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


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.