Wednesday, 7 June 2017

Multitenant : Metadata Only PDB Clones in Oracle Database 12c

The 12.1.0.2 patchset introduced the ability to do a metadata-only clone. Adding the NO DATA clause when cloning a PDB signifies that only the metadata for the user-created objects should be cloned, not the data in the tables and indexes.

  • Setup


Create a clean PDB, then add a new user and a test table with some data.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb10 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb10/');

ALTER PLUGGABLE DATABASE pdb10 OPEN;

ALTER SESSION SET CONTAINER = pdb10;

CREATE TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb10/users01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users;

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

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         1

SQL>

  • Metadata Clone


Perform a metadata-only clone of the PDB using the NO DATA clause.

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb11 FROM pdb10
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb10/','/u01/app/oracle/oradata/cdb1/pdb11/')
  NO DATA;

ALTER PLUGGABLE DATABASE pdb11 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb10 CLOSE;
ALTER PLUGGABLE DATABASE pdb10 OPEN READ WRITE;
Checking the contents of the test table in the new PDB show the table is present, but it is empty.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb11;

SELECT COUNT(*) FROM test.t1;

  COUNT(*)
----------
         0

SQL>

  • Restrictions


The NO DATA clause is only valid is the the source PDB doesn't contain any of the following.
  1. Index-organized tables
  2. Advanced Queue (AQ) tables
  3. Clustered tables
  4. Table clusters
If it does, you will get the following type of error.

SQL> CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb1/pdb11/')
   NO DATA;
CREATE PLUGGABLE DATABASE pdb11 FROM pdb1
*
ERROR at line 1:
ORA-65161: Unable to create pluggable database with no data

SQL>