{"id":73102,"date":"2016-09-05T17:07:55","date_gmt":"2016-09-05T17:07:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-12-1-0-2c-hot-cloning-of-non-container-databases\/"},"modified":"2021-07-14T13:07:02","modified_gmt":"2021-07-14T13:07:02","slug":"oracle-12-1-0-2c-hot-cloning-of-non-container-databases","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-12-1-0-2c-hot-cloning-of-non-container-databases\/","title":{"rendered":"Oracle Database 12.1.0.2c : Hot Cloning of Non-Container Databases"},"content":{"rendered":"<p>In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods:<\/p>\n<ul>\n<li>Using DBMS_PDB<\/li>\n<li>Using datapump (<a href=\"https:\/\/oracle-base.com\/articles\/12c\/multitenant-migrate-non-cdb-to-pdb-12cr1\">expdb, impdp<\/a>)<\/li>\n<li>Using GoldenGate replication<\/li>\n<\/ul>\n<p>The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network.\u00a0Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the <code>CREATE PLUGGABLE DATABASE<\/code> command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in <code>READ ONLY<\/code> mode for cloning so that it can be cloned online. It <em>is<\/em> a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c\u00a0Release 1 (12.1.0.2) or later.<\/p>\n<p>In this article, I will demonstrate hot cloning of a remote non-container ASM database (non-CDB) to create a new pluggable database (PDB) with file system storage in Oracle Database 12.1.0.2c. For the purpose of this demonstration, I have two virtual machines, <strong>host01<\/strong> and <strong>host02<\/strong>. We will clone the non-cdb <strong>orcl<\/strong> on host <strong>host01<\/strong> to PDB <strong>orclclone<\/strong> in the CDB <strong>destcdb<\/strong> on remote host <strong>host02.<\/strong><\/p>\n<p><strong>Source<\/strong>:<br \/> Host : <strong>host01<\/strong><br \/> Non-CDB<strong> : orcl <\/strong>having ASM storage<\/p>\n<p><strong>Destination<\/strong>:<br \/> Host : <strong>host02<\/strong><br \/> Container Database : <strong>destcdb<\/strong><br \/> Pluggable database : <strong>orclclone <\/strong>having file system storage<\/p>\n<h2>Demonstration<\/h2>\n<p>Let&#8217;s confirm that our source database <strong>orcl<\/strong> is a Non-CDB:<\/p>\n<pre>ORCL&gt;select name, open_mode, cdb from v$database;\r\n\r\n<strong>NAME      OPEN_MODE            CDB<\/strong>\r\n--------- -------------------- ---\r\n<strong><span style=\"color: red;\">ORCL<\/span>      READ WRITE           <span style=\"color: red;\">NO<\/span><\/strong><\/pre>\n<p>We need to make sure that both the source non-CDB <strong>orcl<\/strong> and the destination CDB <strong>destcdb<\/strong> are running Oracle Database 12c\u00a0Release 1 (12.1.0.2) or later.<\/p>\n<pre><strong>ORCL&gt;select version from v$instance;<\/strong>\r\n<strong>VERSION<\/strong>\r\n-----------------\r\n<strong><span style=\"color: red;\">12.1.0.2.0<\/span><\/strong>\r\n<strong>DESTCDB&gt;select version from v$instance;<\/strong>\r\n<strong>VERSION<\/strong>\r\n-----------------\r\n<strong><span style=\"color: red;\">12.1.0.2.0<\/span><\/strong><\/pre>\n<p>On the target container database <strong>destcdb<\/strong>, we need to create a database link to connect to source non-CDB <strong>orcl<\/strong> which will be used in the <code>CREATE PLUGGABLE DATABASE<\/code> statement:<\/p>\n<pre><strong>DESTCDB&gt;create database link orcl_link<\/strong>\r\n        <strong>connect to system identified by oracle using 'host01:1521\/orcl';<\/strong>\r\n<strong>Database link created.<\/strong><\/pre>\n<p>Verify that target container database <strong>destcdb<\/strong> currently has two PDB&#8217;s &#8211; PDB$SEED and PDB1:<\/p>\n<pre><strong>DESTCDB&gt;sho pdbs<\/strong>\r\n\r\n    <strong>CON_ID CON_NAME                       OPEN MODE  RESTRICTED<\/strong>\r\n---------- ------------------------------ ---------- ----------\r\n         <strong>2 <span style=\"color: red;\">PDB$SEED<\/span>                       READ ONLY  NO<\/strong>\r\n         <strong>3 <span style=\"color: red;\">PDB1<\/span>                           READ WRITE NO<\/strong><\/pre>\n<pre><strong>DESTCDB&gt;select name, open_mode, cdb from v$database@orcl_link;<\/strong>\r\n<strong>NAME      OPEN_MODE            CDB<\/strong>\r\n--------- -------------------- ---\r\n<strong>ORCL      <span style=\"color: red;\">READ WRITE<\/span>           NO<\/strong><\/pre>\n<p>Let&#8217;s execute the <code>CREATE PLUGGABLE DATABASE<\/code> statement using the previously-defined database link (<strong><code>orcl_link<\/code>)<\/strong> to create target PDB <strong>orclclone<\/strong> in CDB <strong>destcdb<\/strong>.<\/p>\n<pre><strong>DESTCDB&gt;create pluggable database orclclone from orcl@orcl_link;<\/strong>\r\n<strong>create pluggable database orclclone from orcl@orcl_link<\/strong>\r\n<strong>*<\/strong>\r\n<strong><span style=\"color: red;\">ERROR at line 1:<\/span><\/strong>\r\n<strong><span style=\"color: red;\">ORA-65016: FILE_NAME_CONVERT must be specified<\/span><\/strong><\/pre>\n<p>In order to specify FILE_NAME_CONVERT<strong>,<\/strong> let&#8217;s find out the location of database files for source non-CDB <strong>orcl<\/strong> on <strong>host01:<\/strong><\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;select name from v$datafile@orcl_link;\r\nNAME\r\n--------------------------------------------------------------------------------\r\n+DATA\/ORCL\/DATAFILE\/system.258.904565437\r\n+DATA\/ORCL\/DATAFILE\/sysaux.257.904565371\r\n+DATA\/ORCL\/DATAFILE\/undotbs1.260.904565513\r\n+DATA\/ORCL\/DATAFILE\/example.266.904565615\r\n+DATA\/ORCL\/DATAFILE\/users.259.904565511<\/pre>\n<p>Since source database files are on ASM and hence use OMF, we will have to employ OMF for the destination PDB also by specifying <code>CREATE_FILE_DEST<\/code> clause in <code>CREATE PLUGGABLE DATABASE<\/code> statement in addition to the database link (<strong>orcl_link)<\/strong>:<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;create pluggable database orclclone from orcl@orcl_link \r\n        create_file_dest = '\/u01\/app\/oracle\/oradata\/';\r\nPluggable database created.<\/pre>\n<p>Note that when the source database is a non-CDB, we can substitute NON$CDB for the name of the non-CDB. For example, the following statement is equivalent to the above statement:<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;create pluggable database orclclone from NON$CDB@orcl_link \r\n        create_file_dest = '\/u01\/app\/oracle\/oradata\/';<\/pre>\n<p>Verify that the new pluggable database <strong>orclclone<\/strong> is created in <code>MOUNTED<\/code> state and its status is <code>NEW<\/code>:<\/p>\n<p><strong>DESTCDB&gt;sho pdbs<\/strong><\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;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 <span style=\"color: red;\">ORCLCLONE                      MOUNTED<\/span>\r\nDESTCDB&gt;select pdb_name, status from cdb_pdbs;\r\nPDB_NAME        STATUS\r\n--------------- ---------\r\nPDB1            NORMAL\r\nPDB$SEED        NORMAL\r\n<span style=\"color: red;\">ORCLCLONE       NEW<\/span><\/pre>\n<p>Verify that OMF data files of <strong>orclclone<\/strong> have been created on <strong>host02<\/strong> in the location specified using <code>FILE_NAME_CONVERT<\/code>.<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt; select con_id, name , guid from v$pdbs where name = 'ORCLCLONE';\r\n    CON_ID NAME                           GUID\r\n---------- ------------------------------ --------------------------------\r\n         4 <span style=\"color: red;\">ORCLCLONE                      389926412E383872E053B9C909C0C716<\/span>\r\nDESTCDB&gt; select name from v$datafile where con_id = 4;\r\nNAME\r\n--------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/oradata\/DESTCDB\/<span style=\"color: red;\">389926412E383872E053B9C909C0C716\/datafile\/o1_mf_system_csjqcowk_.dbf<\/span>\r\n\r\n\/u01\/app\/oracle\/oradata\/DESTCDB\/<span style=\"color: red;\">389926412E383872E053B9C909C0C716\/datafile\/o1_mf_sysaux_csjqcowo_.dbf<\/span>\r\n\r\n\/u01\/app\/oracle\/oradata\/DESTCDB\/<span style=\"color: red;\">389926412E383872E053B9C909C0C716\/datafile\/o1_mf_users_csjqcowo_.dbf<\/span>\r\n\r\n\/u01\/app\/oracle\/oradata\/DESTCDB\/<span style=\"color: red;\">389926412E383872E053B9C909C0C716\/datafile\/o1_mf_example_csjqcowp_.dbf<\/span><\/pre>\n<p>Prior to opening the PDB for the first time, we need to log in to the destination PDB <strong>orclclone<\/strong> as SYS user and run the script <code>$ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql<\/code> in order to modify some metadata and convert the Non-CDB to PDB. The script opens the PDB, performs changes, and closes the PDB when the changes are complete.<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;alter session set container = orclclone;\r\nSession altered.\r\nDESTCDB&gt;sho con_name\r\nCON_NAME\r\n------------------------------\r\nORCLCLONE\r\nSQL&gt; @$ORACLE_HOME\/rdbms\/admin\/noncdb_to_pdb.sql\r\n...\r\n...\r\nDESTCDB&gt;-- leave the PDB in the same state it was when we started\r\nDESTCDB&gt;BEGIN\r\n  2    execute immediate '&amp;open_sql &amp;restricted_state';\r\n  3  EXCEPTION\r\n  4    WHEN OTHERS THEN\r\n  5    BEGIN\r\n  6      IF (sqlcode &lt;&gt; -900) THEN\r\n  7        RAISE;\r\n  8      END IF;\r\n  9    END;\r\n 10  END;\r\n 11  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\nDESTCDB&gt;\r\nDESTCDB&gt;\r\nDESTCDB&gt;WHENEVER SQLERROR CONTINUE;<\/pre>\n<p>We will now open the new PDB <strong>orclclone<\/strong> in <code>READ WRITE<\/code> mode to complete the integration of the new PDB into the CDB. After the PDB is opened in <code>READ WRITE<\/code> mode, its status changes from <code>NEW<\/code> to <code>NORMAL<\/code>.<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt; conn \/ as sysdba \r\n        alter pluggable database orclclone open;\r\nPluggable database altered.\r\nDESTCDB&gt;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 <span style=\"color: red;\">ORCLCLONE                      READ WRITE<\/span> NO\r\nDESTCDB&gt;select pdb_name, status from cdb_pdbs; \r\nPDB_NAME        STATUS\r\n--------------- ---------\r\nPDB1            NORMAL\r\nPDB$SEED        NORMAL\r\n<span style=\"color: red;\">ORCLCLONE       NORMAL<\/span><\/pre>\n<p>Let&#8217;s check the user data in the new PDB:<\/p>\n<pre style=\"font-weight: bold;\">DESTCDB&gt;alter session set container=orclclone;\r\nSession altered.\r\nDESTCDB&gt;select count(*) from hr.employees;\r\n  COUNT(*)\r\n----------\r\n       107<\/pre>\n<p>Hence, we have been able to hot clone a non-CDB remotely and during this process:<\/p>\n<ul>\n<li>The source non-CDB remained in READ WRITE mode<\/li>\n<li>Data files were migrated from ASM to non-ASM<\/li>\n<\/ul>\n<p>Although the process is quite simple, it may not be feasible for large databases or situations involving slow or unreliable network links since it depends on transporting the data over a database link. Hence, you must consider the size of your source database and the speed of your internet connection in order to decide if it is a feasible migration approach in your case.<\/p>\n<p><strong>Summary<\/strong>:<\/p>\n<ul>\n<li>The 12.1.0.2 patchset introduces the ability to create a PDB as a clone of a remote non-CDB over a database link.<\/li>\n<li>Non-CDB&#8217;s can be hot cloned i.e. the source non-CDB need not be put in READ ONLY mode for cloning.<\/li>\n<li>Remote cloning of non-CDB may not be feasible for large databases or situations involving slow or unreliable network links.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods: Using DBMS_PDB Using datapump (expdb, impdp) Using GoldenGate replication The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network.\u00a0Remote Cloning of a Non-CDB is a&hellip;<\/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":[],"coauthors":[],"class_list":["post-73102","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73102","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=73102"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73102\/revisions"}],"predecessor-version":[{"id":91601,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73102\/revisions\/91601"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73102"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}