Friday, 16 September 2016

Oracle : Hot Cloning of Non-Container Databases

In the initial release of Oracle 12c (, 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 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 ( 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 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.

Host : host01
Non-CDB : orcl having ASM storage

Host : host02
Container Database : destcdb
Pluggable database : orclclone having file system storage


Let’s confirm that our source database orcl is a Non-CDB:

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

--------- -------------------  ---
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 ( or later.

ORCL>select version from v$instance;
DESTCDB>select version from v$instance;

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

---------- ----------------   ------------      ----------
         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;

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
---------- ----------------   ------------      ----------
         2   PDB$SEED          READ ONLY     NO
         3   PDB1                  READ WRITE   NO
         4   ORCLCLONE       MOUNTED

DESTCDB>select pdb_name, status from cdb_pdbs;
--------------- ---------
PDB1               NORMAL

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;




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
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
DESTCDB>-- leave the PDB in the same state it was when we started
  2    execute immediate '&open_sql &restricted_state';
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

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
---------- ---------------------- ---------- ----------
  2         PDB$SEED                      READ ONLY  NO
  3         PDB1                           READ WRITE NO
  4         ORCLCLONE                      READ WRITE NO
DESTCDB>select pdb_name, status from cdb_pdbs;
--------------- ---------
PDB1            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;

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.