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

by Joel Pérez and Skant Gupta

This article shows how to use the "transportable tablespace" 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 on-premises platform is little endian and the database character sets of your on-premises database and Oracle Database Cloud Service database are compatible.

The transportable tablespace method is generally much faster than a conventional export/import of the same data because the data files containing all of the actual data are simply copied to the destination location. You use Data Pump to transfer only the metadata of the tablespace objects to the new database.

To migrate an on-premises instance of Oracle Database to a database deployment on Oracle Database Cloud Service using the Data Pump transportable tablespace method, you perform these tasks:

  • On the on-premises database host, prepare the database for the Data Pump transportable tablespace export by setting all tablespaces that will be transported (the transportable set) to read-only mode.
  • On the on-premises database host, invoke Data Pump Export 
    expdp
    to perform the transportable tablespace export.
  • Create a new instance of Oracle Database Cloud Service.
  • Connect to the Oracle Database Cloud Service compute node.
  • Use a secure copy utility to transfer the Data Pump Export dump file and the tablespace datafiles to the Oracle Database Cloud Service compute node.
  • Set the on-premises tablespaces back to read-write mode.
  • On the Oracle Database Cloud Service compute node, prepare the database for the tablespace import.
  • On the Oracle Database Cloud Service compute node, invoke Data Pump Import 
    impdp
    and connect to the database.
  • Set the tablespaces on the Oracle Database Cloud Service database to read-write mode.
  • After verifying that the data has been imported successfully, delete the dump file.
 

The following sections show a step-by-step example of the tasks required to migrate tablespaces in an on-premises instance of Oracle Database 11g Release 2 to an Oracle Database Cloud Service database using the Data Pump transportable tablespace method. However, a similar process can be used to migrate tablespaces in an on-premises instance of Oracle Database 12c Release 1 or 12c Release 2.

This example performs a migration of the TBSP_DATA and TBSP_INDEX tablespaces.

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

Export the On-Premises Database

  • 1. On the on-premises database host, prepare the database for the Data Pump transportable tablespace export:

    a. On the on-premises database host, create a directory in the operating system to use for the on-premises export.
      
    [oracle@cloud ~]$ mkdir -p /u01/app/dpump

    b. On the on-premises database host, invoke SQL*Plus and log in to the on-premises 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 

  SQL>


    c. Create a directory object in the on-premises 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 datafiles that belong to the TBSP_DATA and TBSP_INDEX 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 where tablespace_name = 'TBSP_DATA';

   TABLESPACE_NAME  ------------------------------ 

   FILE_NAME  -------------------------------------------------------------- 

   TBSP_DATA  /u01/app/oracle/oradata/ORA11G/tbspdata01.dbf 

   SQL> SELECT tablespace_name, file_name FROM dba_data_files where tablespace_name = 'TBSP_INDEX';

   TABLESPACE_NAME  ------------------------------ 

   FILE_NAME  -------------------------------------------------------------- 

   TBSP_DATA  /u01/app/oracle/oradata/ORA11G/tbspindex01.dbf

    e. On the on-premises database host, set all tablespaces that will be transported (the transportable set) to read-only mode.
      
    SQL> ALTER TABLESPACE tbsp_data READ ONLY;

 Tablespace altered. 

 SQL> ALTER TABLESPACE tbsp_index READ ONLY;

 Tablespace altered.


    f. Exit from SQL*Plus.

  • 2. On the on-premises database host, invoke the Data Pump Export utility and connect to the on-premises database. Export the on-premises tablespaces using the TRANSPORT_TABLESPACES option. Provide the password for the SYSTEM user when prompted.
      
    [oracle@cloud ~]$ expdp system TRANSPORT_TABLESPACES=tbsp_data,tbps_index TRANSPORT_FULL_CHECK=YES DIRECTORY=prem_to_cloud 

 Export: Release 11.2.0.4.0 - Production on Sat Jun 3 23:30:03 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_TRANSPORTABLE_01":  system/******** TRANSPORT_TABLESPACES=tbsp_data,tbps_index TRANSPORT_FULL_CHECK=YES DIRECTORY=prem_to_cloud

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 
     Cloud-Mig11g

    - 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 11g Release 2.
    - 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  
    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.

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-Mig11g) 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 machine using PuTTY

  • 2. Use a secure copy utility to transfer the Data Pump Export dump file and the tablespace datafiles 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 file that will be transferred.

    a. On the Oracle Database Cloud Service compute node, create a directory for the dump file.
[oracle@Cloud-Mig11g ~]$ mkdir -p /u01/app/on_premise

 [oracle@Cloud-Mig11g ~]$

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 on-premises host.

c. On the on-premises 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.230:/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/tbspdata01.dbf oracle@129.144.152.230:/u02/app/oracle/oradata/PROD

 Enter passphrase for key 'rsa-key-20170111.ssh': 

 tbspdata01.dbf             100%  141MB 183.7KB/s   16:00 

 [oracle@cloud ~]$ scp -i rsa-key-20170111.ssh /u01/app/oracle/oradata/ORA11G/tbspindex01.dbf oracle@129.144.152.230:/u02/app/oracle/oradata/PROD

 Enter passphrase for key 'rsa-key-20170111.ssh': 

 tbspindex01.dbf            100%  14MB 183.7KB/s   2:00

 [oracle@cloud ~]$

3. Set the on-premises tablespaces back to read-write mode:

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

b. Set the TBPS_DATA and TBSP_INDEX tablespaces back to read-write mode.

SQL> ALTER TABLESPACE tbsp_data READ WRITE;

 Tablespace altered. 

 SQL> ALTER TABLESPACE tbsp_index READ WRITE;

 Tablespace altered.

c. Exit from SQL*Plus.

4. On the Oracle Database Cloud Service compute node, prepare the database for the tablespace import:

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

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

SQL> CREATE DIRECTORY on_cloud AS '/u01/app/on_premise';

 Directory created.

c. If the owners of the objects that will be imported do not exist in the database, create them before performing the import. The transportable tablespace mode of import does not create the users.

SQL> CREATE USER test PROFILE default IDENTIFIED BY test TEMPORARY TABLESPACE temp ACCOUNT UNLOCK;

User Created.

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

TRANSPORT_DATAFILES
option. 
[oracle@Cloud-Mig11g PROD]$ impdp system DIRECTORY=on_cloud \
 > TRANSPORT_DATAFILES='/u02/app/oracle/oradata/PROD/tbspdata01.dbf' \

>'/u02/app/oracle/oradata/ORCL/tbspindex01.dbf'

 Import: Release 11.2.0.4.0 - Production on Sat Jun 3 19:16:50 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 and Real Application Testing options 

 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded 

 Source time zone version is 14 and target time zone version is 28.

 Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** DIRECTORY=on_cloud TRANSPORT_DATAFILES=/u02/app/oracle/oradata/PROD/ tbspdata 01.dbf,/u02/app/oracle/oradata/ORCL/tbspindex01.dbf

6. Set the tablespaces on the Oracle Database Cloud Service database to read-write mode:

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

b. Set the TBSP_DATA and TBSP_INDEX tablespaces to read-write mode.

SQL> ALTER TABLESPACE tbsp_data READ WRITE;

  Tablespace altered. 

  SQL> ALTER TABLESPACE tbsp_index READ WRITE;

  Tablespace altered.

c. Exit from SQL*Plus.

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