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:

ORCL>select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
ORCL      READ WRITE           NO

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.

ORCL>select version from v$instance;
VERSION
-----------------
12.1.0.2.0
DESTCDB>select version from v$instance;
VERSION
-----------------
12.1.0.2.0

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:

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

Verify that target container database destcdb currently has two PDB’s – PDB$SEED and PDB1:

DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
DESTCDB>select name, open_mode, cdb from v$database@orcl_link;
NAME      OPEN_MODE            CDB
--------- -------------------- ---
ORCL      READ WRITE           NO

Let’s execute the CREATE PLUGGABLE DATABASE statement using the previously-defined database link (orcl_link) to create target PDB orclclone in CDB destcdb.

DESTCDB>create pluggable database orclclone from orcl@orcl_link;
create pluggable database orclclone from orcl@orcl_link
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

In order to specify FILE_NAME_CONVERT, let’s find out the location of database files for source non-CDB orcl on host01:

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):

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:

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

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 ORCLCLONE                      MOUNTED
DESTCDB>select pdb_name, status from cdb_pdbs;
PDB_NAME        STATUS
--------------- ---------
PDB1            NORMAL
PDB$SEED        NORMAL
ORCLCLONE       NEW

Verify that OMF data files of orclclone have been created on host02 in the location specified using FILE_NAME_CONVERT.

DESTCDB> select con_id, name , guid from v$pdbs where name = 'ORCLCLONE';
    CON_ID NAME                           GUID
---------- ------------------------------ --------------------------------
         4 ORCLCLONE                      389926412E383872E053B9C909C0C716
DESTCDB> select name from v$datafile where con_id = 4;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DESTCDB/389926412E383872E053B9C909C0C716/datafile/o1_mf_system_csjqcowk_.dbf

/u01/app/oracle/oradata/DESTCDB/389926412E383872E053B9C909C0C716/datafile/o1_mf_sysaux_csjqcowo_.dbf

/u01/app/oracle/oradata/DESTCDB/389926412E383872E053B9C909C0C716/datafile/o1_mf_users_csjqcowo_.dbf

/u01/app/oracle/oradata/DESTCDB/389926412E383872E053B9C909C0C716/datafile/o1_mf_example_csjqcowp_.dbf

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.

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.

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 ORCLCLONE                      READ WRITE NO
DESTCDB>select pdb_name, status from cdb_pdbs; 
PDB_NAME        STATUS
--------------- ---------
PDB1            NORMAL
PDB$SEED        NORMAL
ORCLCLONE       NORMAL

Let’s check the user data in the new PDB:

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.