What You See Is What You Get Element

How to Migrate an On-Premises Instance of Oracle Database to Oracle Cloud Using the Data Pump "Full Transportable" Mode

by Joel Pérez and Skant Gupta

This article shows how to use the "full transportable" mode of the Data Pump feature of Oracle Database to copy an entire database from your on-premises host to an Oracle Database Cloud Service database deployment.

Note: You can use this method only if the source database release version is Oracle Database 11.2.0.3 or later and the database character sets of your on-premises database and the Oracle Database Cloud Service database are compatible.

To migrate an Oracle Database 11g on-premises database to an Oracle Database Cloud Service instance of Oracle Database 12c using the Data Pump full transportable method, you perform these tasks:

  1. Create a new instance of Oracle Database Cloud Service.
  2. Connect to the Oracle Database Cloud Service compute node.
  3. On the on-premises database host, prepare the database for the Data Pump full transportable export by placing the user-defined tablespaces in read-only mode.
  4. On the on-premises database host, invoke Data Pump Export (expdp) to perform the full transportable export.
  5. Use a secure copy utility to transfer the Data Pump Export dump file and the datafiles for all of the user-defined tablespaces to the Oracle Database Cloud Service compute node.
  6. Set the on-premises tablespaces back to read-write mode.
  7. On the Oracle Database Cloud Service compute node, invoke Data Pump Import (impdp) and import the data into the database.
  8. After verifying that the data has been imported successfully, delete the dump file.

The following sections show an example of the entire process. In this example, the source database is on a Linux host.

Create a New Instance of Oracle Cloud Database 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.

    Figure 1 new service (Cloud-Mig)

    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 specify

    Figure 2. Specifying the service details

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

    Figure 3 Creating

    Figure 3. Creating the cloud database instance

     

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

    Figure 4 Create DB

    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

    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 Select Access Rules

    Figure 6. Selecting the Access Rules item

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

    Figure 7 enable listener

    Figure 7. Enabling the ora_p2_dblistener rule

Connect to the Cloud Database

Open two different instances of the PuTTY executable and connect to both the machines using an SSH public key.

Figure 8 Connect usin PuTTY

Figure 8. Connecting to both machines using PuTTY

Export the On-Premises Database, Transfer the Dump File, and Import the Data

  1. On the source database host, prepare the database for the Data Pump full transportable export.

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

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


    b. On the source database host, invoke SQL*Plus and log in to the source database as the SYSTEM user.

    [oracle@cloud ~]$ sqlplus SYSTEM
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 1 01:49:18 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


    c. Create a directory object in the source database to reference the operating system directory.

    SQL> CREATE DIRECTORY prem_to_cloud AS '/u01/app/dpump';
Directory created.


    d. Determine the name(s) of the tablespaces and data files that belong to the user-defined tablespaces by querying DBA_DATA_FILES. These files will also be listed in the export output.

    SQL> SELECT tablespace_name, file_name FROM dba_data_files;
TABLESPACE_NAME                  FILE_NAME
------------------------------   --------------------------------------------------
USERS                            /u01/app/oracle/oradata/ORA11G/users01.dbf
UNDOTBS1                         /u01/app/oracle/oradata/ORA11G/undotbs01.dbf
SYSAUX                           /u01/app/oracle/oradata/ORA11G/sysaux01.dbf
SYSTEM                           /u01/app/oracle/oradata/ORA11G/system01.dbf
EXAMPLE                          /u01/app/oracle/oradata/ORA11G/example01.dbf


    e. On the source database host, set all tablespaces that will be transported (the transportable set) to read-only mode.

    SQL> ALTER TABLESPACE example READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE users READ ONLY;
Tablespace altered.


    f. Exit from SQL*Plus.
  2. On the source database host, invoke Data Pump Export utility to perform the full transportable export. Specify FULL=y and TRANSPORTABLE=always. Because this is an Oracle Database 11g database and full transportable is an Oracle Database 12c feature, specify VERSION=12. Provide the password for the SYSTEM user when prompted.

    [oracle@cloud ~]$ expdp system FULL=y TRANSPORTABLE=always VERSION=12 DUMPFILE=expdat.dmp DIRECTORY=prem_to_cloud
Export: Release 11.2.0.4.0 - Production on Thu Jun 1 01:56:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** FULL=y TRANSPORTABLE=always VERSION=12 DUMPFILE=expdat.dmp DIRECTORY=prem_to_cloud
  3. Use a secure copy utility to transfer the Data Pump Export dump file and the datafiles for all of the user-defined tablespaces to the Oracle Database Cloud Service compute node.

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

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

    [oracle@Cloud-Mig ~]$ mkdir -p /u01/app/on_premise


    b. Before using the scp utility to copy files, make sure the SSH private key that provides access to the Oracle Database Cloud Service compute node is available on your source host.

    c. On the source database host, use the scp utility to transfer the dump file and all datafiles of the transportable set to the Oracle Database Cloud Service compute node.

    [oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/dpump/expdat.dmp oracle@129.144.152.30:/u01/app/on_premise
Enter passphrase for key 'rsa-key-20170111.ssh':
expdat.dmp                     100%   97MB 121.3KB/s   13:42
[oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/oracle/oradata/ORA11G/users01.dbf oracle@129.144.152.30:/u02/app/oracle/oradata/MIGORCL/pdb3 
Enter passphrase for key 'rsa-key-20170111.ssh':
users01.dbf                    100% 5128KB 125.1KB/s   00:41
[oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/oracle/oradata/ORA11G/example01.dbf oracle@129.144.152.30:/u02/app/oracle/oradata/MIGORCL/pdb3
Enter passphrase for key 'rsa-key-20170111.ssh':
example01.dbf                  100%  141MB 183.7KB/s   16:00
  4. Set the source tablespaces back to read-write mode.

    a. Invoke SQL*Plus and log in as the SYSTEM user.

    b. Set the user-defined tablespaces back to read-write mode.

    SQL> ALTER TABLESPACE example READ WRITE;
Tablespace altered.
SQL> ALTER TABLESPACE users READ WRITE;
Tablespace altered.


    c. Exit from SQL*Plus.
  5. On the Oracle Database Cloud Service compute node, prepare the pluggable database (PDB) for the tablespace import.

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

    b. Create a directory object in the PDB.

    SQL> CREATE DIRECTORY on_cloud AS '/u01/app/on_premise';
Directory created.
  6. On the Oracle Database Cloud Service compute node, invoke the Data Pump Import utility and connect to the PDB. Then Import the data into the database using the TRANSPORT_DATAFILES option.

    [oracle@Cloud-Mig pdb3]$ impdp system@PDB3 FULL=y DIRECTORY=on_cloud \
> TRANSPORT_DATAFILES='/u02/app/oracle/oradata/MIGORCL/pdb3/example01.dbf', \
> '/u02/app/oracle/oradata/MIGORCL/pdb3/users01.dbf'
Import: Release 12.1.0.2.0 - Production on Wed May 31 21:18:45 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
  7. After verifying that the data has been imported successfully, you can delete the expdat.dmp dump file. With this step, the entire process has been 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.