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.
Related articles.
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)
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:
- Creating and Removing PDBs with SQL*Plus : User Tablespaces
- Multitenant : All Articles
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)
Hope this helps. Regards Tim...