Step By Steps for Migrate on-premises Oracle database to Oracle Cloud Architecture(OCI) Database service database using Data Pump Conventional Export/Import
You can use this method regardless of the endian format(OS Platform Independent) and database character set of the on-premises database.
To migrate an on-premises source database, tablespace, schema, or table to the database on a Database service database deployment using Data Pump Export and Import, you perform these tasks:
- On the on-premises database host, invoke Data Pump Export and export the on-premises database.
- Use a secure copy utility to transfer the dump file to the Database service compute node.
- On the Database service compute node, invoke Data Pump Import and import the data into the database.
- After verifying that the data has been imported successfully, we can delete the dump file.
This example provides a step-by-step tasks required to migrate a schema from an on-premises Oracle database to a Database 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.
- On the on-premises database host, invoke Data Pump Export to export the schemas.
- 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
- On the on-premises database host, invoke
SQL*Plus and log in to the on-premises database as the SYSTEM user.
c. $ sqlplus system
Enter password: <enter the password for the SYSTEM user>
- 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';
- Exit from SQL*Plus.
- 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
- Use a secure copy utility to transfer the dump
file to the Database 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.
- On the OCI Database service compute node,
create a directory for the dump file.
$ mkdir
/u01/app/oracle/admin/ORCL/dpdump/from_onprem
- Before using the scp command to copy
the export dump file, make sure the SSH private key that provides access
to the OCI Database service compute node is available on your
on-premises host.
- On the on-premises database host, use the SCP
utility to transfer the dump file to the OCI Database 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
- On the OCI Database service compute node, invoke Data Pump Import and import the data into the database.
- On the OCI Database service compute node,
invoke SQL*Plus and log in to the database as the SYSTEM user.
b. $ sqlplus system
Enter password: <enter the password for the SYSTEM user>
- Create a directory object in the Database service
database.
SQL> CREATE
DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/from_onprem';
- If they do not exist, create the tablespace(s)
for the objects that will be imported.
- Exit from SQL*Plus.
- On the OCI Database 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
- After verifying that the data has been imported successfully, we can delete the dump file