By Sushil Motwani | Edited by Michelle Malcher

There are several methods used and developed over the years/decades since the Oracle Database has been around to migrate or clone your databases. Most of the methods have required us to use Data Guard or GoldenGate to achieve the same with minimal downtime or RMAN Backup files/Database Datafiles for regular cloning.

With the 12cR1 version of the database, remote cloning was introduced but still required to place the source non-CDB or PDB into Read-Only mode before initiating the cloning. With 12cR2, one has the ability to clone a database (also known as hot-cloning) without the restriction of read-only or downtime on the source. Also, hot-cloning can be achieved without an existing backup of the source.

Use Cases for Hot-Cloning a Database

• Moving Datacenters from one hosting provided/on-premise to another hosting provider/cloud provider, and minimal downtime is an absolute need. The process described below was done from a source database running on Oracle Cloud Infrastructure Classic to the target database running on Oracle Cloud Infrastructure, but the process can be used with any source/target combination datacenters.
• Creating a Master Clone Database (Multi-Terabyte) which gets refreshed regularly, thus providing the ability to create regular clones for Development and Testing

Pre-Requisite for Hot-Clone

Remember, we are only mentioning “hot”

• The remote PDB or non-CDB must be in Local Undo Mode. If not, then it is not a hot-clone, that is, the operation described below can be performed by the source would need to be in read-only mode.
  • Check source and target DB local undo mode setting (local undo should be enabled)

SQL> SELECT property_name, property_value FROM database_properties WHERE
property_name = ‘LOCAL_UNDO_ENABLED’;

PROPERTY_NAME                     PROPERTY_VALUE

——————————   ——————————

LOCAL_UNDO_ENABLED                TRUE

• The target (local) PDB must be at least 12.2.0.1, the source can be a non-CDB or PDB at least at 12.2.0.1
• Source (Remote) DB needs to be in ARCHIVELOG mode. If not, then it is not a hot-clone, that is, the operation described below can be performed by the source would need to be in read-only mode.
• Source and target database must have the same endianness.

Hot-Cloning Steps

• Create a new user in the remote (source) database that would be used for the process of the cloning.

SQL> CREATE USER c$$remote_admin IDENTIFIED BY *****
CONTAINER=ALL;
SQL> GRANT CREATE SESSION, RESOURCE, SYSOPER, CREATE PLUGGABLE
DATABASE TO c$$remote_admin CONTAINER=ALL;

Note: even if you grant SYSDBA, but it is not enough, still need SYSOPER.

•  Create a TNS alias on target DB to point to source DB

Remote_DB_Alias =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = ****))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = *********)
)
)

If you use an alias for the database link in a RAC environment, the same
tnsnames.ora entry should be added on all RAC instances. 

• Create a database link on the target CDB, connecting to the common user on the remote database using the database alias created in the tnsnames.ora file.

$ sqlplus / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit
Production
SQL> CREATE DATABASE LINK to_source c$$remote_admin IDENTIFIED BY *****
USING ‘Remote_DB_Alias’;
Database link created

Verify that you are able to connect to the Remote/Source Database from the Target Database.
SQL> select * from dual@Remote_DB_Alias;

D
–
X

Clone Process

A hot clone can be taken while the source is still open read-write i.e without interrupting any operations in the source.

A hot clone is known as a “fuzzy read” of all the blocks in the datafiles of the source.

•  The clone operation begins at time t0
• The clone operation will end at time t1
• Several blocks would have changed on the source between t0 and t1
• The clone is now physically inconsistent with the source.
• So, the redo that had been created on the source between t0 and t1 need to be transported to the target.
• The redo needs to be reapplied to the Target PDB
• Target PDB is now an exact copy of Source at time t1, but also contains the committed and uncommitted transactions on the source, and is not transactionally consistent.
• Rollback the uncommitted transactions by applying the UNDO to all uncommitted transactions
• The clone operation is complete.
• The clone is now a transactionally consistent copy of the source at time t1.

All of this is performed atomically in the clone operation, with no human intervention when the clone / create pluggable database command is executed.

Creating the Cloned Database (Non-Refreshable)

At the Target CDB (If using ASM at the Target – this is the default on Oracle Cloud) ensure the Initialization Parameter for the Destination File Directories in ASM is set:

For example: DB_CREATE_FILE_DEST=’+DATA’ (For non-ASM Target DBs, we need to use the FILE_NAME_CONVERT in the CREATE PLUGGABLE DATABASE command)

Hot clone Source to Target PDB, run below on target:
SQL> CREATE PLUGGABLE DATABASE targetpdb FROM SourcePDBName or
NON$CDB@to_source KEYSTORE IDENTIFIED BY “<wallet password>” ;

OR

SQL> CREATE PLUGGABLE DATABASE targetpdb FROM SourcePDBName or
NON$CDB@to_source CREATE_FILE_DEST=’*****’ KEYSTORE IDENTIFIED BY
“<wallet password>”;

(The files will be created by default into the CREATE_FILE_DEST destination directory)
When cloning a PDB in DBAAS environment with TDE Encrypted Data, the default wallet password is system user password which is given during DB creation.

Pluggable database created.

The cloning command above can take anything from minutes to days depending on the network connectivity between source and target servers, and the size of the database. The above process for the author of this paper took 4 hours for a 6TB Source Database.

If the target PDB was created as a clone of a non-CDB, run the following commands to let the database be now a pluggable database:

SQL> ALTER SESSION SET CONTAINER=targetpdb;
SQL> $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> alter pluggable database targetpdb open;

Check PDB_PLUG_IN_VIOLATIONS on target database for any error.

select time,name,cause,status,type,message from PDB_PLUG_IN_VIOLATIONS
where name = ‘PPDBOCI’ and status != ‘RESOLVED’ and type = ‘ERROR’

Resolve the errors (if any) on the new Target Cloned PDB, and your clone is complete.

Creating the Cloned Database (Refreshable)

The cloning operation for production PDBs can take minutes to hours as mentioned above. If there is a need for frequent clones, then the process of larger database systems can be a long time-consuming activity even in the case of hot-clones.

A refreshable clone PDB solves the above problem. There are two types of Refreshable Cloned PDBs you can create:

• Manual Refresh
• Automatic Refresh (Every “x” minutes)

The REFRESH MODE clause is provided in the CREATE PLUGGABLE DATABASE … FROM statement which was used above:

• REFRESH MODE NONE, the default, to create a PDB that is not refreshable – the process defined above was a non-refreshable database. The cloned database created cannot be converted into a refreshable database.
• REFRESH MODE MANUAL – creates a refreshable PDB that must be refreshed manually.
• REFRESH MODE EVERY “X” MINUTES – Creates a refreshable PDB that is refreshed automatically after “X” minutes has passed. A refreshable PDB that uses automatic refresh can also be refreshed manually.

A refreshable clone PDB must be in either of the following states:

• Closed – A refreshable PDB must be closed when a refresh is performed. If not, the refresh is deferred until the next scheduled refresh.
• Read-Only – The refreshable PDB is intended to serve as a clone master

A refreshable clone PDB can be converted into an ordinary PDB by running the command:

ALTER PLUGGABLE DATABASE REFRESH MODE NONE;
ALTER PLUGGABLE DATABASE OPEN;

An ordinary PDB cannot be converted into a refreshable clone PDB – that is the process is irreversible.

About the Author

Sushil Motwani has about 25+ years of experience as an Oracle SME, with hands-on experience on the complete Red Stack of Oracle, from Hardware to Software and the Oracle IaaS/PaaS Cloud.

He is a thought leader having published training material for Oracle University, published and presented on multiple occasions at events such as Collaborate (Oracle Applications User Group), Oracle Open World, Oracle Health Sciences Conference in Denver, Atlanta, New York, Las Vegas, Rome, Monte Carlo and multiple locations across New England. He is actively involved with the OAUG and IOUG groups and presented at a number of SIGs (Special Interest Groups) as part of these user groups.

He has a Computer Science Engineering Degree from the University of Bombay. Over the last few years, before joining SmartDog Services, Sushil has lead Oracle and Microsoft teams to proactively deliver to their customer’s technical roadmaps and provide newer/better solutions to improve technical delivery in the long run. At Oracle, Sushil worked with Advanced Customer Services for 6+ years leading some of Oracle’s largest premier customers as a Technical Account Manager. Sushil has also worked as a Director of the DBA Practice for a well-known Managed Services Provider in the Boston area from 2007 to 2009. From 2001 to 2007, Sushil worked at Harvard University as their Oracle and Microsoft SME and lead multiple technical implementations as part of their central IT team.