How to Migrate an On-Premises Instance of Oracle Database 12c to Oracle Cloud Using Remote Cloning

This article shows how to use the remote cloning method to copy a pluggable database (PDB) from your on-premises Oracle Database 12c database to a PDB in a database-as-a-service (DBaaS) container database (CDB) in an Oracle Database Cloud Service deployment.

Note: You can use this method only if the on-premises platform is little endian; the on-premises release is Oracle Database 12.1.0.2 or higher; and the on-premises database and Oracle Database Cloud Service database have compatible database character sets and national character sets.

To copy an Oracle Database 12c PDB to a PDB in an Oracle Database Cloud Service deployment using the remote cloning method, you perform these tasks:

1. On the on-premises database host, prepare the on-premises PDB by invoking SQL*Plus, closing the on-premises PDB, and then reopening the PDB in read-only mode.
2. Create a new instance of Oracle Database Cloud Service.
3. Connect to the Oracle Database Cloud Service compute node, invoke SQL*Plus, and create a database link that enables a connection to the on-premises database.
4. On the Oracle Database Cloud Service compute node, execute the CREATE PLUGGABLE DATABASE command to clone the on-premises PDB.
5. On the Oracle Database Cloud Service compute node, open the new PDB by executing the ALTER PLUGGABLE DATABASE OPEN command.
6. Optionally, on the on-premises database host, invoke SQL*Plus and set the on-premises PDB back to read-write mode.

The following sections provide a step-by-step demonstration of the tasks required to remotely clone an on-premises Oracle Database PDB into a new PDB in a DBaaS CDB.

In this example, the on-premises database is on a Linux host.

Prepare the On-Premises PDB

1. On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.
   
   Copy

   
   
   [oracle@cloud ~]$ sqlplus / as sysdba
   SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017
   Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
   SQL>
   

2. Close the on-premises PDB_PREM PDB.
   
   Copy

   
   
   SQL> ALTER PLUGGABLE DATABASE pdb_prem CLOSE;
   Pluggable database altered.
   
   

3. Open the on-premises PDB_PREM PDB in read-only mode.
   
   Copy

   
   
   SQL> ALTER PLUGGABLE DATABASE pdb_prem OPEN READ ONLY;
   Pluggable database altered.
   
   

Create a New Instance of Oracle Database Cloud Service

1. Log in to your Oracle Cloud services account, go to the Oracle Database Cloud Service page, and create a new service:
   
   - For Service Name enter Cloud-Mig12c.
   - From the Service Level list, select Oracle Database Cloud Service.
   - From the Metering Frequency list, select whatever frequency is appropriate for your environment.
   - From the Software Release list, select Oracle Database 12c Release 1.
   - From the Software Edition list, select Enterprise Edition.
   - From the Database Type list, select Single Instance.
   
   

   Then click Next to continue.

   

   Figure 1. Creating a new service (Cloud-Mig11g)

2. In the Service Details screen, do the following:
   
   - For DB Name (SID), enter PRODDB.
   - Set an administrative password of your choice and confirm the password (this will be your sys password).
   - For Usable Database Storage (GB), enter 25.
   - From the Compute Shape list, select OC3 -1 OCPU, 7.5 GB RAM (this is the bare minimum required).
   - For SSH Public Key, enter rsa-key-20170111.pub.
   
   Then click Next to continue.
   

   

   Figure 2. Specifying the service details

3. Finally, review the configuration and click Create to create your cloud database.
   

   

   Figure 3. Creating the cloud database instance

After a few minutes, the cloud database instance has been created successfully.



Figure 4. The cloud database has been created

4. Click the service name (Cloud-Mig12c) to open the main page of the database.
   

   

   Figure 5. Main page of the cloud database

5. Before trying to connect to the database instance on the cloud machine, you have to enable the dblistener access rule. Do the following:
   
   a. Open the database service and select Access Rules from the menu.
   

   

   Figure 6. Selecting the Access Rules item

   b. For the ora_p2_dblistener rule, select Enable from the Actions menu.
   

   

   Figure 7. Enabling the ora_p2_dblistener rule

Connect to the Cloud Database and Create a Database Link

1. Open an instance of the PuTTY executable and connect to the Oracle Database Cloud Service compute node using an SSH public key.
   

   

   Figure 8. Connecting to machine using PuTTY

2. On the Database Cloud Service compute node, invoke SQL*Plus and create a database link that enables a connection to the on-premises database.
   
   a. On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.
   
   Copy

   
   
   [oracle@Cloud-Mig12c ~]$ sqlplus / as sysdba
   SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 06:33:14 2017
   Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
   and Real Application Testing options
   
   

   
   
   b. Create a database link in the DBaaS PRODDB CDB to connect to the on-premises PDB_PREM PDB.
   Copy

   
   
   SQL> CREATE DATABASE LINK link_prem CONNECT TO system IDENTIFIED BY sys USING 'PDB_PREM';
   
   Database link created.
   
   

Clone the On-Premises PDB

1. On the Oracle Database Cloud Service compute node, execute the CREATE PLUGGABLE DATABASE command to clone the on-premises PDB:
   
   a. Create a directory for the data files of the cloned PDB.
   
   Copy

   
   
   [oracle@Cloud-Mig12c ~]$ mkdir -p /u02/app/oracle/oradata/PRODDB/PDB_PREM
   [oracle@Cloud-Mig12c ~]$
   
   

   b. Create the new PDB2 PDB in the DBaaS CDB.
   
   Copy

   
   
   SQL> CREATE PLUGGABLE DATABASE pdb2 FROM PDB_PREM@link_prem CREATE_FILE_DEST = '/u02/app/oracle/oradata/PRODDB/PDB_PREM' TEMPFILE REUSE;
   Pluggable database created.
   
   
   

2. On the Oracle Database Cloud Service compute node, open the new PDB by executing the ALTER PLUGGABLE DATABASE OPEN command:
   
   a. Connect to the new PDB using the net service name after having configured it in the tnsnames.ora file.
   
   Copy

   
   
   [oracle@Cloud-Mig12c ~]$ sqlplus sys@pdb2 as sysdba
   
   SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 12:45:44 2017
   Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   Enter password:
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   SQL> SHOW con_name
   
   CON_NAME
   ------------------------------
   PDB2
   
   

   b. Open the new PDB in the DBaaS CDB.
   
   Copy

   
   
   SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
   Pluggable database altered.
   SQL> CONNECT SYSTEM@PDB2
   Enter password:
   Connected.
   
   

3. Optionally, on the on-premises database host invoke SQL*Plus and set the on-premises PDB back to read-write mode:
   
   a. On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.
   
   Copy

   
   
   [oracle@cloud ~]$ sqlplus / as sysdba
   SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017
   Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
   Connected to:
   Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
   

   
   
   b. Close the on-premises PDB_PREM PDB.
   
   Copy

   
   
   SQL> ALTER PLUGGABLE DATABASE pdb_prem CLOSE;
   Pluggable database altered.
   
   

   c. Open the on-premises PDB_PREM PDB in read-write mode.
   
   Copy

   
   
   SQL> ALTER PLUGGABLE DATABASE pdb_prem OPEN;
   Pluggable database altered.
   
   

We hope this article will be useful and we invite you to continue reading our next publications focused on Oracle Cloud.

About the Authors

Joel Pérez is an expert DBA and Oracle ACE Director and an Oracle Certified Master in Oracle Maximum Availability Architecture, Oracle Database cloud administration, and Oracle Database 11g and12c. He has over 17 years of real-world experience with Oracle technology and specializes in the design and implementation of solutions for the cloud, high availability (HA), disaster recovery, upgrades, replication, and most areas related to Oracle Database. Currently, he works as chief technologist and architect for cloud technologies, Oracle Maximum Availability Architecture, and HA at Yunhe Enmo (Beijing) Technology Co., Ltd. in Beijing, China.

Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g, and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on cloud, database, and high availability solutions, Oracle WebLogic Suite, and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and India.