On-premise database migration to oracle cloud using data pump conventional Export/Import

This is simplest migration if you prefer to migrate one schema to oracle cloud.

Source:-On-premise database(Hostname:-gnssrv01 )

Target:-Oracle cloud database (Hostname:-myclone)

Step 1:-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

Step 2:-Create a directory object in the on-premises database to reference the operating system directory.

[oracle@gnssrv01 admin]$ sqlplus test/test@orclpdb
SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 26 07:38:24 2018

SQL> CREATE DIRECTORY dp_for_cloud AS '/u01/app/oracle/admin/orcl/dpdump/for_cloud';
Directory created.

Step 3:-Please take export dump of the schema.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.

oracle@gnssrv01 admin]$ expdp system/XXX@orclpdb SCHEMAS=test DIRECTORY=dp_for_cloud
Export: Release 12.2.0.1.0 - Production on Thu Apr 26 07:40:38 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orclpdb SCHEMAS=test DIRECTORY=dp_for_cloud
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."MYTAB"                              9.613 MB   72620 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/orcl/dpdump/for_cloud/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Apr 26 07:42:07 2018 elapsed 0 00:01:18

Step 4:-On the Database Cloud Service compute node, create a directory for the dump file.

$ mkdir /u01/app/oracle/admin/ORCL/dpdump/from_onprem

Step 5:-On the on-premises database host, use the SCP utility to transfer the dump file to the Database Cloud Service compute node.

Please follow below note on how to use private key to transfer using scp to remote host.

[oracle@gnssrv01 ~]$ scp  -i my_priv_key /u01/app/oracle/admin/orcl/ dpdump/for_cloud/expdat.dmp oracle@144.21.83.108:/u01/app/ oracle/admin/ORCL/dpdump/from_onprem/expdat.dmp

Step 6:-On the Database Cloud Service compute node, invoke SQL*Plus and log in to the database as the SYSTEM user.

[oracle@myclone admin]$ sqlplus system/Bppimt#123@orclpdb
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 27 03:32:50 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Apr 27 2018 03:32:24 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/from_onprem';
Directory created.

Step 7:-Now import the schema in oracle cloud database

[oracle@myclone admin]$ impdp system/Bppimt#123@orclpdb SCHEMAS=test DIRECTORY=dp_from_onprem

Import: Release 12.2.0.1.0 – Production on Fri Apr 27 03:34:00 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/********@orclpdb SCHEMAS=test DIRECTORY=dp_from_onprem
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “TEST”.”MYTAB” 9.613 MB 72620 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Fri Apr 27 03:34:38 2018 elapsed 0 00:00:29