Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 2 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c:

• PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online. In Oracle 12.1.0.1c, a pluggable database can be cloned only if it is read-only.
• PDBs can be remote cloned, i.e. the source PDB may be located in a remote container. This feature was listed in Oracle Database 12c Release 1 (12.1.0.1), but didn’t work.
• There is no need to create the directories where the database files of clone PDB will be placed. If the directory is not already present, it will be automatically created as part of the cloning operation.

In this article, I will demonstrate hot cloning of a PDB locally as well as remotely. For the purpose of this demonstration, I have two virtual machines, each with a container database.

Current scenario:

Host: host01
Container Database: cdb1 with pluggable database pdb1
Host: host02
Container Database: destcdb with pluggable database pdb1

Demonstration 1: Hot cloning of PDB locally

We will clone the pluggable database pdb1 to pdb1clone in the same CDB, i.e. cdb1 on host host01
Source host: host01
Source PDB: pdb1 in container database cdb1
Destination host: host01
Destination PDB: pdb1clone in container database cdb1
Currently, there is only one PDB called pdb1 currently open in READ WRITE mode in the container database cdb1.
CDB1>select name, cdb from v$database;

CDB1>select name, cdb from v$database;
NAME      CDB
--------- ---
CDB1      YES

CDB1>select con_id, name, open_mode from v$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE

CDB1>select name from v$datafile where con_id = 3;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

Verify that directory corresponding to data files of target PDB, i.e. pdb1clone is not present:

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/ 
total 1782620
-rw-r----- 1 oracle asmadmin  17973248 Jul 26 15:03 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 11:59 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 26 15:03 redo03.log
-rw-r----- 1 oracle asmadmin 650125312 Jul 26 15:02 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 26 15:02 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 26 15:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 26 15:01 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:18 users01.dbf

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/ | grep pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1

Using the CREATE PLUGGABLE DATABASE ... FROM command we will clone the existing PDB (pdb1) to create a new PDB (pdb1clone) in the same container database (cdb1). This statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.

CDB1>create pluggable database pdb1clone from pdb1 
     file_name_convert = ('pdb1','pdb1clone');
Pluggable database created.

We can see that the new PDB called pdb1clone is in MOUNTED state when created and is opened successfully thereafter.
CDB1>sho pdbs

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      MOUNTED

CDB1>alter pluggable database pdb1clone open;
Pluggable database altered.

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      READ WRITE NO
CDB1>alter session set container=pdb1clone;
Session altered.

CDB1>sho con_name
CON_NAME
------------------------------
PDB1CLONE

CDB1>select count(*) from hr.employees;
  COUNT(*)
----------
       107

Note that the directory for the data files of the clone PDB pdb1clone has been created automatically in the location specified using FILE_NAME_CONVERT.
[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:53 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:52 pdb1clone_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:53 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:53 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:53 system01.dbf

Hence, we have been able to hot clone a PDB locally without:

• Placing the source PDB in READ ONLY mode
• Creating the directory for the destination PDB

Demonstration 2: Hot cloning of PDB remotely

We will clone the pluggable database pdb1 in CDB cdb1 on host host01 to pdb1new in another CDB, i.e. destcdb on host host02:
Source host: host01
Source PDB: pdb1 in container database cdb1
Destination host: host02
Destination PDB: pdb1new in container database destcdb
Currently, there is only one PDB called pdb1 open in READ WRITE mode in destination container database destcdb:

DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

On the target container database destcdb, we need to create the database link to connect to source container database cdb1 which will be used in the CREATE PLUGGABLE DATABASE.

DESTCDB>create database link cdb1_link 
        connect to system identified by oracle using 'host01:1521/cdb1';
Database link created.

Verify that the source pluggable database (pdb1@cdb1) that we want to clone is in READ WRITE mode.

CDB1> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE

Let’s execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) as previously defined.

DESTCDB> create pluggable database pdb1new from pdb1@cdb1_link;
 create pluggable database pdb1new from pdb1@cdb1_link
                                                     *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

Let’s find out location of datafiles for pdb1@cdb1 on host01:

CDB1>alter session set container = pdb1;
Session altered.
CDB1>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

Verify that directory corresponding to data files of target PDB, i.e. pdb1new, is not present on the target host host02:

[oracle@host02 destcdb]$ ls -l /u01/app/oracle/oradata/destcdb
total 1761816
-rw-r----- 1 oracle asmadmin  17973248 Jul 25 15:35 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jul 24 16:04 PDB1
drwxr-x--- 2 oracle oinstall      4096 Jul 24 15:57 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:09 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:35 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:08 redo03.log
-rw-r----- 1 oracle asmadmin 629153792 Jul 25 15:34 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 25 15:35 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 25 15:26 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 25 15:35 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:19 users01.dbf

Let’s specify FILE_NAME_CONVERT and re-execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) we previously defined:

DESTCDB>create pluggable database pdb1new from pdb1@cdb1_link 
        file_name_convert = ('/u01/app/oracle/oradata/cdb1/pdb1','/u01/app/oracle/oradata/destcdb/pdb1new');
Pluggable database created.

By default the new pluggable database is created in MOUNTED state and can be opened.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        MOUNTED

DESTCDB>alter pluggable database pdb1new open;
Pluggable database altered.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        READ WRITE NO
DESTCDB>alter session set container=pdb1new;
Session altered.
DESTCDB>select count(*) from hr.employees;
  COUNT(*)
----------
       107

Verify that the directory for data files of pbdnew has been created automatically on host02 in the location specified using FILE_NAME_CONVERT:

[oracle@host02 pdb1new]$ ls -l /u01/app/oracle/oradata/destcdb/pdb1new
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:41 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:39 pdb1_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:41 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:41 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:41 system01.dbf

Hence, we have been able to hot clone a PDB remotely without:

• Placing the source PDB in READ ONLY mode
• Creating the directory for the destination PDB

Summary:

In Oracle 12.1.0.2c, various enhancements been made to cloning of PDB:

• PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online.
• PDBs can be hot cloned remotely as well, i.e. the source PDB can be located in a remote container.
• There is no need to create the directories where the database files of clone PDB will be placed. If the directory is not already present, it will be automatically created as part of the cloning operation.

Thanks
Srini