This article shows how to use the remote cloning method to copy a pluggable database (PDB) from your on-premises Oracle Database 12
c
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 12
c
PDB to a PDB in an Oracle Database Cloud Service deployment using the remote cloning method, you perform these tasks:
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.
Create a new instance of Oracle Database Cloud Service.
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.
On the Oracle Database Cloud Service compute node, execute the CREATE PLUGGABLE DATABASE command to clone the on-premises PDB.
On the Oracle Database Cloud Service compute node, open the new PDB by executing the ALTER PLUGGABLE DATABASE OPEN command.
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
On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user.
[oracle@cloud ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017
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
Close the on-premises
PDB_PREM
PDB.
Create a New Instance of Oracle Database Cloud Service
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)
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
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
Click the service name (Cloud-Mig12c) to open the main page of the database.
Figure 5. Main page of the cloud database
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
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
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.
[oracle@Cloud-Mig12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 06:33:14 2017
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.
Clone the On-Premises PDB
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.
[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.
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.
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.
[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
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
------------------------------
b. Open the new PDB in the DBaaS CDB.
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
Pluggable database altered.
SQL> CONNECT SYSTEM@PDB2
Enter password:
Connected.
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.
[oracle@cloud ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 4 11:47:11 2017
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
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 11
g
and12
c
. 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.
