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.
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 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.
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 normal view: https://oracle-base.com/articles/12c/multitenant-pdb-subset-cloning-12cr1