You can use this method regardless of the endian format and database character set of the on-premises database.
To migrate an on-premises source database, tablespace, schema, or table to the database on an Oracle Database Cloud - Database as a Service instance using Data Pump Export and Import, you perform these tasks:
1. On the on-premises database host, invoke Data Pump Export and export the on-
premises database.
2. Use a secure copy utility to transfer the dump file to the Database as a Service
compute node.
3. On the Database as a Service compute node, invoke Data Pump Import and
import the data into the database.
4. After verifying that the data has been imported successfully, you can delete the
dump file.
For information about Data Pump Import and Export, see these topics:
• In Oracle Database Utilities for 12c Release 1: Data Pump Export Modes and Data Pump Import Modes
• In Oracle Database Utilities for 11g Release 2: Data Pump Export Modes and Data Pump Import Modes
Data Pump Conventional Export/Import: Example
This example provides a step-by-step demonstration of the tasks required to migrate a schema from an on-premises Oracle database to an Oracle Database Cloud - Database as a Service database.
This example illustrates a schema mode export and import. The same general procedure applies for a full database, tablespace, or table export and import. In this example, the on-premises database is on a Linux host.
1. On the on-premises database host, invoke Data Pump Export to export the
schemas.
a. On the on-premises database host, create an operating system directory to use
for the on-premises database export files.
$ mkdir /u01/app/oracle/admin/orcl/dpdump/for_cloud
b. On the on-premises database host, invoke SQL*Plus and log in to the on-
premises database as the SYSTEM user.
$ sqlplus system
Enter password: <enter the password for the SYSTEM user>
c. Create a directory object in the on-premises database to reference the
operating system directory.
SQL> CREATE DIRECTORY dp_for_cloud AS '/u01/app/oracle/admin/orcl/dpdump/
for_cloud';
d. Exit from SQL*Plus.
e. On the on-premises database host, invoke Data Pump Export as the SYSTEM
user or another user with the DATAPUMP_EXP_FULL_DATABASE role and
export the on-premises schemas. Provide the password for the user when prompted.
$ expdp system SCHEMAS=fsowner DIRECTORY=dp_for_cloud
2. Use a secure copy utility to transfer the dump file to the Database as a 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 Database as a Service compute node, create a directory for the dump
file.
$ mkdir /u01/app/oracle/admin/ORCL/dpdump/from_onprem
b. Before using the scp command to copy the export dump file, make sure the
SSH private key that provides access to the Database as a Service compute node is available on your on-premises host. For more information about SSH keys, see About Network Access to a Database as a Service Instance.
c. On the on-premises database host, use the SCP utility to transfer the dump
file to the Database as a Service compute node.
$ scp –i private_key_file \
/u01/app/oracle/admin/orcl/dpdump/for_cloud/expdat.dmp \
oracle@IP_address_DBaaS_VM:/u01/app/oracle/admin/ORCL/dpdump/from_onprem
3. On the Database as a Service compute node, invoke Data Pump Import and
import the data into the database.
a. On the Database as a Service compute node, invoke SQL*Plus and log in to
the database as the SYSTEM user.
$ sqlplus system
Enter password: <enter the password for the SYSTEM user> b. Create a directory object in the Database as a Service database.
SQL> CREATE DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/
from_onprem';
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 Database as a Service compute node, invoke Data Pump Import and
connect to the database. Import the data into the database.
$ impdp system SCHEMAS=fsowner DIRECTORY=dp_from_onprem
4. After verifying that the data has been imported successfully, you can delete the
expdat.dmp file.
Data Pump Full Transportable
You can use this method only if the source database release version is 11.2.0.3 or later and the database character set is AL32UTF8.
You can use the Data Pump full transportable method to copy an entire database from your on-premises host to the database on an Oracle Database Cloud - Database as a Service instance.
To migrate an Oracle Database 11g on-premises database to the Oracle Database 12c database on a Database as a Service instance using the Data Pump full transportable method, you perform these tasks:
1. 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.
2. On the on-premises database host, invoke Data Pump Export to perform the full
transportable export.
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 Database as a Service compute node.
4. Set the on-premises tablespaces back to READ WRITE.
5. On the Database as a Service compute node, prepare the database for the
tablespace import.
6. On the Database as a Service compute node, invoke Data Pump Import and
connect to the database.
7. After verifying that the data has been imported successfully, you can delete the
dump file.