In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump.
- Part 1: Creation of the ATP database and connection verification
- Part 2: Export of on-premises data with Oracle Data Pump and move into into the Oracle cloud
- Part 3: Import of data into the ATP database with Oracle Data Pump
Oracle Data Pump Export on On-Premises Database
Oracle recommends the following settings for the Oracle Data Pump export job. If you use already an 18c database, you have to set the version parameter to 12.2 to avoid this error during ATP import process: ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0.
exclude=cluster, db_link parallel=n schemas=schema name dumpfile=export%u.dmp version=12.2
My expdp export job for the schema HR:
$ expdp system/***password***@PDBHR01.martinberger.local schemas=hr directory=expdp dumpfile=expdpHR05102018.dmp logfile =expdpHR05102018.log exclude=cluster, db_link version=12.2
The example schema contains these objects – output from expdp job
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows . . exported "HR"."LOCATIONS" 8.437 KB 23 rows . . exported "HR"."JOB_HISTORY" 7.195 KB 10 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 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/ORA18/expdp/expdpHR05102018.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 5 09:06:27 2018 elapsed 0 00:04:07
Transfer the Dump File Set into the Oracle Cloud
The export dump file has to be transferred into the Oracle Cloud Object Storage for later usage. First we have to create an Object Storage Bucket. Login into Object Storage menu and click on the “Create Bucket” button.
Enter a Bucket Name and click on “Create Bucket”.
Enter the new created bucket. Click on the bucket name link or the “three bullets” on the right side to view the details.
Upload Data
Upload the export dump file, click on “Upload Object”.
“Browse” local for the Oracle Data Pump export file and click on “Upload Object”.
Object Details
Click on object “Details” to verify the object on the Object Storage.
Note the URL. The URL will be used later for the import process.
Summary of Part 2
Upload objects into the Oracle Cloud Object Storage is very easy. If you don’t want to use the browser functionality, there are other possibilities to upload files like the API.
Now the export dump file is ready to import into the Autonomous Transaction Processing Database.