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. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the CREATE PLUGGABLE DATABASE command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.
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, host01 and host02. We will clone the non-cdb orcl on host host01 to PDB orclclone in the CDB destcdb on remote host host02.
Source:
Host : host01
Non-CDB : orcl having ASM storage
Destination:
Host : host02
Container Database : destcdb
Pluggable database : orclclone having file system storage
Demonstration
Let’s confirm that our source database orcl is a Non-CDB:
|
1 2 3 4 5 |
ORCL>select name, open_mode, cdb from v$database; <strong>NAME OPEN_MODE CDB</strong> --------- -------------------- --- <strong><span style="color: red;">ORCL</span> READ WRITE <span style="color: red;">NO</span></strong> |
We need to make sure that both the source non-CDB orcl and the destination CDB destcdb are running Oracle Database 12c Release 1 (12.1.0.2) or later.
|
1 2 3 4 5 6 7 8 |
<strong>ORCL>select version from v$instance;</strong> <strong>VERSION</strong> ----------------- <strong><span style="color: red;">12.1.0.2.0</span></strong> <strong>DESTCDB>select version from v$instance;</strong> <strong>VERSION</strong> ----------------- <strong><span style="color: red;">12.1.0.2.0</span></strong> |
On the target container database destcdb, we need to create a database link to connect to source non-CDB orcl which will be used in the CREATE PLUGGABLE DATABASE statement:
|
1 2 3 |
<strong>DESTCDB>create database link orcl_link</strong> <strong>connect to system identified by oracle using 'host01:1521/orcl';</strong> <strong>Database link created.</strong> |
Verify that target container database destcdb currently has two PDB’s – PDB$SEED and PDB1:
|
1 2 3 4 5 6 |
<strong>DESTCDB>sho pdbs</strong> <strong>CON_ID CON_NAME OPEN MODE RESTRICTED</strong> ---------- ------------------------------ ---------- ---------- <strong>2 <span style="color: red;">PDB$SEED</span> READ ONLY NO</strong> <strong>3 <span style="color: red;">PDB1</span> READ WRITE NO</strong> |
|
1 2 3 4 |
<strong>DESTCDB>select name, open_mode, cdb from v$database@orcl_link;</strong> <strong>NAME OPEN_MODE CDB</strong> --------- -------------------- --- <strong>ORCL <span style="color: red;">READ WRITE</span> NO</strong> |
Let’s execute the CREATE PLUGGABLE DATABASE statement using the previously-defined database link (orcl_link) to create target PDB orclclone in CDB destcdb.
|
1 2 3 4 5 |
<strong>DESTCDB>create pluggable database orclclone from orcl@orcl_link;</strong> <strong>create pluggable database orclclone from orcl@orcl_link</strong> <strong>*</strong> <strong><span style="color: red;">ERROR at line 1:</span></strong> <strong><span style="color: red;">ORA-65016: FILE_NAME_CONVERT must be specified</span></strong> |
In order to specify FILE_NAME_CONVERT, let’s find out the location of database files for source non-CDB orcl on host01:
|
1 2 3 4 5 6 7 8 |
DESTCDB>select name from v$datafile@orcl_link; NAME -------------------------------------------------------------------------------- +DATA/ORCL/DATAFILE/system.258.904565437 +DATA/ORCL/DATAFILE/sysaux.257.904565371 +DATA/ORCL/DATAFILE/undotbs1.260.904565513 +DATA/ORCL/DATAFILE/example.266.904565615 +DATA/ORCL/DATAFILE/users.259.904565511 |
Since source database files are on ASM and hence use OMF, we will have to employ OMF for the destination PDB also by specifying CREATE_FILE_DEST clause in CREATE PLUGGABLE DATABASE statement in addition to the database link (orcl_link):
|
1 2 3 |
DESTCDB>create pluggable database orclclone from orcl@orcl_link create_file_dest = '/u01/app/oracle/oradata/'; Pluggable database created. |
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:
|
1 2 |
DESTCDB>create pluggable database orclclone from NON$CDB@orcl_link create_file_dest = '/u01/app/oracle/oradata/'; |
Verify that the new pluggable database orclclone is created in MOUNTED state and its status is NEW:
DESTCDB>sho pdbs
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DESTCDB>sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 <span style="color: red;">ORCLCLONE MOUNTED</span> DESTCDB>select pdb_name, status from cdb_pdbs; PDB_NAME STATUS --------------- --------- PDB1 NORMAL PDB$SEED NORMAL <span style="color: red;">ORCLCLONE NEW</span> |
Verify that OMF data files of orclclone have been created on host02 in the location specified using FILE_NAME_CONVERT.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DESTCDB> select con_id, name , guid from v$pdbs where name = 'ORCLCLONE'; CON_ID NAME GUID ---------- ------------------------------ -------------------------------- 4 <span style="color: red;">ORCLCLONE 389926412E383872E053B9C909C0C716</span> DESTCDB> select name from v$datafile where con_id = 4; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/DESTCDB/<span style="color: red;">389926412E383872E053B9C909C0C716/datafile/o1_mf_system_csjqcowk_.dbf</span> /u01/app/oracle/oradata/DESTCDB/<span style="color: red;">389926412E383872E053B9C909C0C716/datafile/o1_mf_sysaux_csjqcowo_.dbf</span> /u01/app/oracle/oradata/DESTCDB/<span style="color: red;">389926412E383872E053B9C909C0C716/datafile/o1_mf_users_csjqcowo_.dbf</span> /u01/app/oracle/oradata/DESTCDB/<span style="color: red;">389926412E383872E053B9C909C0C716/datafile/o1_mf_example_csjqcowp_.dbf</span> |
Prior to opening the PDB for the first time, we need to log in to the destination PDB orclclone as SYS user and run the script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DESTCDB>alter session set container = orclclone; Session altered. DESTCDB>sho con_name CON_NAME ------------------------------ ORCLCLONE SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ... ... DESTCDB>-- leave the PDB in the same state it was when we started DESTCDB>BEGIN 2 execute immediate '&open_sql &restricted_state'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 BEGIN 6 IF (sqlcode <> -900) THEN 7 RAISE; 8 END IF; 9 END; 10 END; 11 / PL/SQL procedure successfully completed. DESTCDB> DESTCDB> DESTCDB>WHENEVER SQLERROR CONTINUE; |
We will now open the new PDB orclclone in READ WRITE mode to complete the integration of the new PDB into the CDB. After the PDB is opened in READ WRITE mode, its status changes from NEW to NORMAL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DESTCDB> conn / as sysdba alter pluggable database orclclone 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 <span style="color: red;">ORCLCLONE READ WRITE</span> NO DESTCDB>select pdb_name, status from cdb_pdbs; PDB_NAME STATUS --------------- --------- PDB1 NORMAL PDB$SEED NORMAL <span style="color: red;">ORCLCLONE NORMAL</span> |
Let’s check the user data in the new PDB:
|
1 2 3 4 5 6 |
DESTCDB>alter session set container=orclclone; Session altered. DESTCDB>select count(*) from hr.employees; COUNT(*) ---------- 107 |
Hence, we have been able to hot clone a non-CDB remotely and during this process:
- The source non-CDB remained in READ WRITE mode
- Data files were migrated from ASM to non-ASM
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.
Summary:
- 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.
- Non-CDB’s can be hot cloned i.e. the source non-CDB need not be put in READ ONLY mode for cloning.
- Remote cloning of non-CDB may not be feasible for large databases or situations involving slow or unreliable network links.
Load comments