Migrate On-premises Database to Cloud using Data Pump

Below are step by step procedure 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:
   
   

   [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:
   
   

   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:
   - For Service Name enter Cloud-Mig.
   - 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.

Figure1. Creating a new service (Cloud-Mig)

2. In the Service Details screen, do the following:

- For DB Name (SID), enter MIGORCL.
- 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-Mig) to open the main page of the 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.

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

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 shows 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.

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.


References: Oracle Technology Network.


For any queries you can email me on samiappsdba@gmail.com