Migrating On-Prem Database Schema to Autonomous Data Warehouse Cloud (ADWC) Using Data Pump

In this post, you will learn how to export the HR Schema from On-Prem Oracle Database to ADWC instance using Data Pump Export (expdp).

Log in to On-Prem Machine with oracle user and create a directory to place the dump files generated by using data pump.

[oracle@oracle19c ~]$ mkdir -p /u01/app/oracle/admin/orcl19c/dpdump/for_adwc

On the On-Prem Machine, invoke SQL*Plus and log in to the database as the sys user and create a directory.

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 21 17:33:53 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl19c
SQL>
SQL> create directory dp_for_adwc as '/u01/app/oracle/admin/orcl19c/dpdump/for_adwc';

Directory created.

SQL> exit

Now invoke the Data Pump export as the SYSTEM user for HR schema.

[oracle@oracle19c ~]$ expdp system@orcl19c directory=DP_FOR_ADWC dumpfile=export_hr_adwc.dmp exclude=index, cluster, indextype, materialized_view,materialized_view_log, materialized_zonemap, db_link data_options=group_partition_table_data schemas=hr

Export: Release 19.0.0.0.0 - Production on Thu Feb 21 17:37:21 2019
Version 19.2.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orcl19c directory=DP_FOR_ADWC dumpfile=export_hr_adwc.dmp exclude=index, cluster, indextype, materialized_view,materialized_view_log, materialized_zonemap, db_link data_options=group_partition_table_data schemas=hr
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "HR"."EMPLOYEES"                            17.07 KB     107 rows
. . exported "HR"."MV_EMP"                               17.07 KB     107 rows
. . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
. . exported "HR"."JOB_HISTORY"                          7.414 KB      16 rows
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."REGIONS"                              5.546 KB       4 rows
. . exported "HR"."MLOG$_EMPLOYEES"                          0 KB       0 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_adwc/export_hr_adwc.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Feb 21 17:39:39 2019 elapsed 0 00:02:16

[oracle@oracle19c ~]$

In the next post, we can move the dump file to object storage bucket which we created earlier post.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp