Cloning\u00a0of a Pluggable Database\u00a0(PDB) in Oracle Multitenant\u00a0is 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). In Oracle 12.1.0.2c:<\/p>\n
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.<\/p>\n
Host: host01<\/strong> Host: host02<\/strong> We will clone the pluggable database pdb1<\/strong> to pdb1clone<\/strong> in the same CDB, i.e. cdb1<\/strong> on host host01<\/strong><\/p>\n Source host: host01<\/strong> Destination host: host01<\/strong> Currently, there is only one PDB called pdb1<\/strong> currently open in CDB1>select name, cdb from v$database;<\/strong><\/p>\n Verify that directory corresponding to data files of target PDB, i.e. pdb1clone<\/strong> is not present:<\/p>\n Using the We can see that the new PDB called pdb1clone<\/strong> is in CDB1>sho pdbs<\/strong><\/p>\n Note that the directory for the data files of the clone PDB pdb1clone <\/strong>has been created automatically in the location specified using [oracle@host01 cdb1]$ ls -l \/u01\/app\/oracle\/oradata\/cdb1\/pdb1clone<\/strong><\/p>\n Hence, we have been able to hot clone a PDB locally without:<\/p>\n We will clone the pluggable database pdb1<\/strong> in CDB cdb1<\/strong> on host host01<\/strong> to pdb1new<\/strong> in another CDB, i.e. destcdb<\/strong> on host host02:<\/strong><\/p>\n Source host: host01<\/strong> Destination host: host02<\/strong> Currently, there is only one PDB called pdb1<\/strong> open in On the target container database destcdb<\/strong>, we need to create the database link to connect to source container database cdb1<\/strong> which will be used in the Verify that the source pluggable database (pdb1@cdb1<\/strong>) that we want to clone is in Let’s execute the Let’s find out location of datafiles for pdb1@cdb1<\/strong> on host01:<\/strong><\/p>\n Verify that directory corresponding to data files of target PDB, i.e. pdb1new<\/strong>, is not present on the target host host02:<\/strong><\/p>\n Let’s specify By default the new pluggable database is created in MOUNTED state and can be opened.<\/p>\n Verify that the directory for data files of pbdnew<\/strong> has been created automatically on host02<\/strong> in the location specified using Hence, we have been able to hot clone a PDB remotely without:<\/p>\n In Oracle 12.1.0.2c, various enhancements been made to cloning of PDB:<\/p>\n Cloning\u00a0of a Pluggable Database\u00a0(PDB) in Oracle Multitenant\u00a0is 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). In Oracle 12.1.0.2c: PDBs can be hot cloned, i.e. you don’t need to put the source PDB…<\/p>\n","protected":false},"author":316217,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48347,48377,48476],"coauthors":[],"class_list":["post-73100","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-12c","tag-cloning","tag-pluggable-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316217"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73100"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73100\/revisions"}],"predecessor-version":[{"id":91599,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73100\/revisions\/91599"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73100"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Container Database 12.1.0.2c: cdb1<\/strong> with pluggable database pdb1<\/strong><\/p>\n
Container Database 12.1.0.2c: destcdb<\/strong> with pluggable database pdb1<\/strong><\/p>\nDemonstration 1: Hot cloning of PDB locally<\/h3>\n
Source PDB: pdb1 <\/strong>in container database cdb1<\/strong><\/p>\n
Destination PDB: pdb1clone <\/strong>in container database cdb1<\/strong><\/p>\nREAD WRITE<\/code> mode in the container database cdb1.<\/strong><\/p>\nCDB1>select name, cdb from v$database;\r\nNAME CDB\r\n--------- ---\r\nCDB1 YES<\/span>\r\n\r\nCDB1>select con_id, name, open_mode from v$pdbs;\r\n CON_ID NAME OPEN_MODE\r\n---------- ------------------------------ ----------\r\n 2 PDB$SEED READ ONLY\r\n 3 PDB1 READ WRITE<\/span>\r\n\r\nCDB1>select name from v$datafile where con_id = 3;\r\nNAME\r\n--------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/system01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/sysaux01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/SAMPLE_SCHEMA_users01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/example01.dbf<\/pre>\n[oracle@host01 cdb1]$ ls -l \/u01\/app\/oracle\/oradata\/cdb1\/ \r\ntotal 1782620\r\n-rw-r----- 1 oracle asmadmin 17973248 Jul 26 15:03 control01.ctl\r\ndrwxr-x--- 2 oracle oinstall 4096 Jun 29 12:06 pdb1\r\ndrwxr-x--- 2 oracle oinstall 4096 Jun 29 11:59 pdbseed\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 25 15:18 redo01.log\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 25 15:18 redo02.log\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 26 15:03 redo03.log\r\n-rw-r----- 1 oracle asmadmin 650125312 Jul 26 15:02 sysaux01.dbf\r\n-rw-r----- 1 oracle asmadmin 817897472 Jul 26 15:02 system01.dbf\r\n-rw-r----- 1 oracle asmadmin 62922752 Jul 26 15:02 temp01.dbf\r\n-rw-r----- 1 oracle asmadmin 173023232 Jul 26 15:01 undotbs01.dbf\r\n-rw-r----- 1 oracle asmadmin 5251072 Jul 25 15:18 users01.dbf\r\n\r\n[oracle@host01 cdb1]$ ls -l \/u01\/app\/oracle\/oradata\/cdb1\/ | grep pdb1\r\ndrwxr-x--- 2 oracle oinstall 4096 Jun 29 12:06 pdb1<\/pre>\n
CREATE PLUGGABLE DATABASE ... FROM<\/code> command we will clone the existing PDB (pdb1<\/strong>) to create a new PDB (pdb1clone<\/strong>) in the same container database (cdb1<\/strong>). This statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.<\/p>\nCDB1>create pluggable database pdb1clone from pdb1 \r\n file_name_convert = ('pdb1','pdb1clone');\r\nPluggable database created.<\/pre>\nMOUNTED<\/code> state when created and is opened successfully thereafter.<\/p>\nCDB1>sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 READ WRITE NO\r\n 4 PDB1CLONE MOUNTED<\/span>\r\n\r\nCDB1>alter pluggable database pdb1clone open;\r\nPluggable database altered.\r\n\r\nCDB1>sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 READ WRITE NO\r\n 4 PDB1CLONE READ WRITE<\/span> NO\r\nCDB1>alter session set container=pdb1clone;\r\nSession altered.\r\n\r\nCDB1>sho con_name\r\nCON_NAME\r\n------------------------------\r\nPDB1CLONE\r\n\r\nCDB1>select count(*) from hr.employees;\r\n COUNT(*)\r\n----------\r\n 107\r\n<\/pre>\nFILE_NAME_CONVERT<\/code>.<\/strong><\/p>\n[oracle@host01 cdb1]$ ls -l \/u01\/app\/oracle\/oradata\/cdb1\/pdb1clone<\/span>\r\ntotal 2089832\r\n-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:53 example01.dbf\r\n-rw-r----- 1 oracle asmadmin 20979712 Jul 25 15:52 pdb1clone_temp012016-06-29_12-06-27-PM.dbf\r\n-rw-r----- 1 oracle asmadmin 5251072 Jul 25 15:53 SAMPLE_SCHEMA_users01.dbf\r\n-rw-r----- 1 oracle asmadmin 555753472 Jul 25 15:53 sysaux01.dbf\r\n-rw-r----- 1 oracle asmadmin 272637952 Jul 25 15:53 system01.dbf<\/pre>\n\n
READ ONLY<\/code> mode<\/li>\nDemonstration 2: Hot cloning of PDB remotely<\/h3>\n
Source PDB: pdb1 <\/strong>in container database cdb1<\/strong><\/p>\n
Destination PDB: pdb1new <\/strong>in container database destcdb<\/strong><\/p>\nREAD WRITE<\/code> mode in destination container database destcdb:<\/strong><\/p>\nDESTCDB>sho pdbs\r\n\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 READ WRITE<\/span> NO<\/pre>\nCREATE PLUGGABLE DATABASE<\/code>.<\/p>\nDESTCDB>create database link cdb1_link \r\n connect to system identified by oracle using 'host01:1521\/cdb1';\r\nDatabase link created.<\/pre>\n
READ WRITE<\/code> mode.<\/p>\nCDB1> select con_id, name, open_mode from v$pdbs where name = 'PDB1';\r\n\r\n CON_ID NAME OPEN_MODE\r\n---------- ------------------------------ ----------\r\n 3 PDB1 READ WRITE<\/span><\/pre>\nCREATE PLUGGABLE DATABASE<\/code> statement using the database link (cdb1_link)<\/strong> as previously defined.<\/p>\nDESTCDB> create pluggable database pdb1new from pdb1@cdb1_link;\r\n create pluggable database pdb1new from pdb1@cdb1_link\r\n *\r\nERROR at line 1:\r\nORA-65016: FILE_NAME_CONVERT must be specified<\/span><\/pre>\nCDB1>alter session set container = pdb1;\r\nSession altered.\r\nCDB1>select name from v$datafile;\r\nNAME\r\n--------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/oradata\/cdb1\/undotbs01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/system01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/sysaux01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/SAMPLE_SCHEMA_users01.dbf\r\n\/u01\/app\/oracle\/oradata\/cdb1\/pdb1\/example01.dbf<\/pre>\n
[oracle@host02 destcdb]$ ls -l \/u01\/app\/oracle\/oradata\/destcdb\r\ntotal 1761816\r\n-rw-r----- 1 oracle asmadmin 17973248 Jul 25 15:35 control01.ctl\r\ndrwxr-x--- 2 oracle oinstall 4096 Jul 24 16:04 PDB1\r\ndrwxr-x--- 2 oracle oinstall 4096 Jul 24 15:57 pdbseed\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 24 16:09 redo01.log\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 25 15:35 redo02.log\r\n-rw-r----- 1 oracle asmadmin 52429312 Jul 24 16:08 redo03.log\r\n-rw-r----- 1 oracle asmadmin 629153792 Jul 25 15:34 sysaux01.dbf\r\n-rw-r----- 1 oracle asmadmin 817897472 Jul 25 15:35 system01.dbf\r\n-rw-r----- 1 oracle asmadmin 62922752 Jul 25 15:26 temp01.dbf\r\n-rw-r----- 1 oracle asmadmin 173023232 Jul 25 15:35 undotbs01.dbf\r\n-rw-r----- 1 oracle asmadmin 5251072 Jul 25 15:19 users01.dbf<\/pre>\n
FILE_NAME_CONVERT<\/code> and re-execute the CREATE PLUGGABLE DATABASE<\/code> statement using the database link (cdb1_link)<\/strong><\/code> we previously defined:<\/p>\nDESTCDB>create pluggable database pdb1new from pdb1@cdb1_link \r\n file_name_convert = ('\/u01\/app\/oracle\/oradata\/cdb1\/pdb1','\/u01\/app\/oracle\/oradata\/destcdb\/pdb1new');\r\nPluggable database created.\r\n<\/pre>\nDESTCDB>sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 READ WRITE NO\r\n 4 PDB1NEW MOUNTED\r\n\r\nDESTCDB>alter pluggable database pdb1new open;\r\nPluggable database altered.\r\n\r\nDESTCDB>sho pdbs\r\n CON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n 2 PDB$SEED READ ONLY NO\r\n 3 PDB1 READ WRITE NO\r\n 4 PDB1NEW READ WRITE NO\r\nDESTCDB>alter session set container=pdb1new;\r\nSession altered.\r\nDESTCDB>select count(*) from hr.employees;\r\n COUNT(*)\r\n----------\r\n 107<\/pre>\n
FILE_NAME_CONVERT<\/code>:<\/p>\n[oracle@host02 pdb1new]$ ls -l \/u01\/app\/oracle\/oradata\/destcdb\/pdb1new\r\ntotal 2089832\r\n-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:41 example01.dbf\r\n-rw-r----- 1 oracle asmadmin 20979712 Jul 25 15:39 pdb1_temp012016-06-29_12-06-27-PM.dbf\r\n-rw-r----- 1 oracle asmadmin 5251072 Jul 25 15:41 SAMPLE_SCHEMA_users01.dbf\r\n-rw-r----- 1 oracle asmadmin 555753472 Jul 25 15:41 sysaux01.dbf\r\n-rw-r----- 1 oracle asmadmin 272637952 Jul 25 15:41 system01.dbf<\/pre>\n
\n
READ ONLY<\/code> mode<\/li>\nSummary:<\/h2>\n
\n