Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 1 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).
Interesting Facts :In Oracle 12.1.0.2c
1. 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.
2. 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.
3. 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.
Demonstration 1: Hot cloning of PDB locally
Host : nadhans.com
We will clone the pluggable database prd to prd1 in the same CDB, i.e. PRODCDB on host nadhans.com
Source host: nadhans.com
Source PDB: prd in container database prodcdb
Destination host: nadhans.com
Destination PDB: prd1 in container database prodcdb
Currently, there is only one PDB called prd currently open in READ WRITE mode in the container database prodcdb.
SQL> select name, cdb from v$database;
NAME CDB
------------------------------ ---
PRODCDB YES
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
3 PRD READ WRITE
Using the CREATE PLUGGABLE DATABASE ... FROM command we will clone the existing PDB (prd) to create a new PDB (prd1) in the same container database (prodcdb). This statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.
PRODCDB>create pluggable database pdb1clone from pdb1
file_name_convert = ('PRD','PRD1');
Pluggable database created.
We can see that the new PDB called prd1 is in MOUNTED state when created and is opened successfully thereafter.
SQL>show pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PRD READ WRITE
4 PRD1 MOUNTED
PRODCDB> alter pluggable database PRD1 open;
Pluggable database altered.
SQL>show pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PRD READ WRITE
4 PRD1 READ WRITE
CDB1>alter session set container=PRD1;
Session altered.
CDB1>sho con_name
CON_NAME
------------------------------
PRD1
Note that the directory for the data files of the clone PDB PRD1 has been created automatically in the location specified using FILE_NAME_CONVERT.
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 PRD in CDB PRODCDB on host nadhans.com to TEST in another CDB TESTCDB
Source host: nadhans.com
Source PDB: PRD in container database PRODCDB
Destination host: testserver.com
Destination PDB: TEST in container database TESTCDB
Currently, there is only one PDB called TEST open in READ WRITE mode in destination container database :
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 TEST READ WRITE
SQL>
On the target container database TESTCDB, we need to create the database link to connect to source container database PRODCDB which will be used in the CREATE PLUGGABLE DATABASE.
create database link test_clone connect to system identified by manager using 'nadhans.com:1521/PRODCDB';
Database link created.
OR
We can create new user for clone with below grants on SOURNCE instead of using system user .
SQL> create user c##nadhans identified by Password;
User created.
SQL> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##link_user CONTAINER=ALL;
Grant succeeded.
SQL> GRANT CREATE PLUGGABLE DATABASE TO c##link_user CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SYSOPER TO c##link_user CONTAINER=ALL;
Grant succeeded.
Then we can create database link on TARGET using below c##nadhans user .
CREATE public DATABASE link clone_link CONNECT TO c##nadhans IDENTIFIED BY nadhan USING 'PRODCDB';
Verify that the source pluggable database (PRD@PRODCDB) that we want to clone is in READ WRITE mode.
SQL> /
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PRD READ WRITE
SQL>
On the Target Container Database:
Close TEST Pluggable database.
SQL> alter pluggable database "TEST" close;
Pluggable database altered.
Drop TEST Pluggable database.
SQL> drop pluggable database TEST including datafiles;
Pluggable database dropped.
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
Let’s execute the CREATE PLUGGABLE DATABASE statement using the database link (test_clone) as previously defined.
SQL> CREATE PLUGGABLE DATABASE TEST FROM PRD@clone_link;
Pluggable database created.
SQL>
Also we can use FILE_NAME_CONVERT .
SQL>create pluggable database TEST from PRD@clone_link
file_name_convert= = (' /ua1001/PRODCDB/oradata/',' /ua1001/TEST/oradata/TESTCDB/');
Pluggable database created.
By default the new pluggable database is created in MOUNTED state and can be opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 TEST MOUNTED
SQL>
SQL> alter pluggable database TEST OPEN;
Pluggable database altered.
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 TEST READ WRITE NO
SQL>
Hence, we have been able to hot clone a PDB remotely without:
- 1. Placing the source PDB in READ ONLY mode
- 2. Creating the directory for the destination PDB
Summary:
In Oracle 12.1.0.2c, various enhancements been made to cloning of PDB:
- 1. 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.
- 2. PDBs can be hot cloned remotely as well, i.e. the source PDB can be located in a remote container.
- 3. 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.
No comments:
Post a Comment