How to Migrate an On-Premises Instance of Oracle Database to Oracle Cloud Using Data Pump

This article shows how to migrate an on-premises source database, tablespace, schema, or table to an Oracle Database Cloud Service database deployment using the conventional export and import functionality of the Data Pump feature of Oracle Database. This method can be used regardless of the endian format and database character set of the on-premises database.

To accomplish the migration, perform the following steps:

1. On the on-premises database host, invoke Data Pump Export (expdp) and export the on-premises database.
2. Create a new Oracle Database Cloud Service.
3. Connect to the Oracle Database Cloud Service compute node and then use a secure copy utility to transfer the dump file to the Oracle Database Cloud Service compute node.
4. On the Oracle Database Cloud Service compute node, invoke Data Pump Import (impdp) and import the data into the database.
5. After verifying that the data has been imported successfully, delete the dump file.

The following sections show an example of the entire process. The example illustrates a schema export and import. However, the same procedure applies for exporting and importing a full database, a tablespace, or a table.

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

Export the On-Premises Database

Perform the following steps on the on-premises database host to export the schemas:

1. On the on-premises database host, create an operating system directory to use for the on-premises database export files:

   [oracle@cloud ~]$ mkdir -p /u01/app/dbpump

2. 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 sys@PDB_PREM as sysdba
   SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 16:05:26 2017
   Copyright (c) 1982, 2014, Oracle. All rights reserved.
   Enter Password:
   Connected to:
   Oracle Database12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
   

3. Create a directory object in the on-premises database to reference the operating system directory:
   Copy

   
   
   SQL> create directory prem_to_cloud as '/u01/app/dpdump';
   Directory created
   
   

4. Exit from SQL*Plus.
5. On the on-premises database host, invoke the Data Pump Export utility as the SYSTEM user or as another user with the DATAPUMP_EXP_FULL_DATABASE role and export the on-premises schemas. Provide the password for the user when prompted.

[oracle@cloud ]$ expdp system@PDB_PREM full=y directory=prem_to_cloud
Export: Release 12.1.0.2.0 - Production on Sun May 21 16:05:26 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
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
...

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:
   1. For Service Name enter Cloud-Mig.
   2. From the Service Level list, select Oracle Database Cloud Service.
   3. From the Metering Frequency list, select whatever frequency is appropriate for your environment.
   4. From the Software Release list, select Oracle Database 12c Release 1.
   5. From the Software Edition list, select Enterprise Edition.
   6. From the Database Type list, select Single Instance.

Then click Next to continue.



Figure1. Creating a new service (Cloud-Mig)

2. In the Service Details screen, do the following:
   1. For DB Name (SID), enter MIGORCL.
   2. Set an administrative password of your choice and confirm the password (this will be your sys password).
   3. For Usable Database Storage (GB), enter 25.
   4. From the Compute Shape list, select OC3 -1 OCPU, 7.5 GB RAM (this is the bare minimum required).
   5. 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-Mig) 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, Transfer the Dump File, and Import the Data

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 Database Cloud Service compute node using PuTTY

2. On the Oracle Database Cloud Service compute node, check the pluggable databases (PDBs) and the pmon process:

[oracle@Cloud-Mig ~]$ ps -ef|grep pmon
oracle    311 32724 0 10:31 pts/1    00:00:00 grep pmon
oracle   7695     1 0 07:57 ?        00:00:00 ora_pmon_MIGORCL
[oracle@Cloud-Mig ~]$ sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 10:31:42 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, Oracle Label Security, OLAP, Advanced Analytics 
and Real Application Testing options
SQL> show pdbs
   CON_ID   CON_NAME     OPEN MODE      RESTRICTED
----------  ----------   -----------    ------------
     2      PDB$SEED     READ ONLY      NO
     3      PDB1         READ WRITE     NO

3. Use a secure copy utility to transfer the dump file to the Oracle Database Cloud Service compute node.

In this example, the dump file is copied to the /u01 directory. Choose an appropriate location based on the size of the file that will be transferred.

a. On the Oracle Database Cloud Service compute node, create a directory for the dump file:

[oracle@Cloud-Mig admin]$ mkdir -p /u01/app/dump

b. Before using the scp command to copy the exported dump file, make sure the SSH private key that provides access to the Oracle Database Cloud Service compute node is available on your on-premises host.

c. On the on-premises database host, use the scp command to transfer the dump file to the Oracle Database Cloud Service compute node:

[oracle@cloud dpdump]$ ls
expdat.dmp   export.log

[oracle@cloud dpdump]$ scp -i /home/oracle/rsa-key-20170111.ssh expdat.dmp oracle@129.157.129.107:/u01/app/dump

Enter passphrase for key '/home/oracle/rsa-key-20170111.ssh':
expdat.dmp               4%  2704KB 856.1KB/s   01:12 ETA

4. On the Oracle Database Cloud Service compute node, import the data into the database:

a. On the Oracle Database Cloud Service compute node, invoke SQL*Plus and log in to the database as the SYSTEM user.

[oracle@Cloud-Mig admin]$ sqlplus sys@pdprem2 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 11:56:53 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, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

b. Create a directory object in the Oracle Database Cloud Service database.

Copy

SQL> create directory cloud_mig as '/u01/app/dpump';
Directory created.

c. If they do not exist, create the tablespace(s) for the objects that will be imported.

d. Exit from SQL*Plus.

e. On the Oracle Database Cloud Service compute node, invoke the Data Pump Import utility and connect to the database. Import the data into the database.

[oracle@Cloud-Mig admin] $ impdb system@pdbprem2 full=y directory=cloud_mig
Import: Release 12.1.0.1.0 - Production on Sun May 21 12:24:39 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
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
...

5. After verifying that the data has been imported successfully, you can delete the expdat.dmp file. With this step, the entire process has be completed.

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.