OCI-Oracle ATP- Import Data from on-prem

The primary method to move data to the Oracle ATP instance is by using Data Pump.

You export data from your On-Prem database. Use the following parameters for faster and easier migration to ATP

exclude=cluster,db_link
parallel=n
schemas=schema_name
dumpfile=export%u.dmp

For larger data size, use parallelism. Use the number of CPU's you have in your ATP. For a 16 CPU ATP instance, here is an example of export. Other parameters like compression can also be used.

expdp sh/sh@orcl \
exclude=cluster,db_link \
parallel=16 \
schemas=sh \
dumpfile=export%u.dmp

Once the export file(s) is ready, move the files to Oracle Cloud Infrastructure Object Storage.

You can use 10 GiB of Object Storage and 10 GiB of Archive Storage for free in your home region. If you use more than 20 GiB and have not upgraded when your Free Trial ends, your data is deleted.


If You are participating in a Free Trial and can store unlimited data. When your trial ends, you are converted to an Always Free account. An Always Free account is limited to 20 GiB of combined Object Storage and Archive Storage in your home region. If you are using more than 20 GiB when your account is converted, your data is deleted. Reduce your usage to 20 GiB or less before converting to Always Free.

Storage usage is the approximate total size of all objects in the region. Usage is updated periodically. You will see a lag between what is displayed and the actual usage.

Oracle Data Pump v18.3 or above supports importing data into ATP. See (https://erpthings.blogspot.com/2019/10/oci-oracle-atp-connect-via-sqlplus-on.html) for details on how to get Oracle Instance Client.
Steps:

• Store Credential Argument so that Data Pump can authenticate to the Object Storage services

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'objectstore',
    username => <'userid'>,
    password => <'password'>
  );

  dbms_cloud.enable_credential(credential_name => 'objectstore');
  ALTER DATABASE PROPERTY SET default_credential = 'ADMIN.objectstore';
End;
  exit;

• Determine the Native URI for your dump file(s) (See this post)
• Run Data Pump import

impdp admin/password@ATPC1_high \       
     directory=data_pump_dir \       
     credential=def_cred_name \       
     dumpfile= https://objectstorage.us-ashburn-1.oraclecloud.com/n/atpc/b/atpc_user/o/export%u.dmp \
     parallel=16 \
     transform=segment_attributes:n \
     transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y \
     exclude=cluster,db_link

  I got a few errors that were eluding to the fact that the user is not authenticated. To work around the issue, I used a few support documents:

How to validate Object Storage URI and Credentials Are Correctly Set for ATP (Doc ID 2468298.1)

NOTE:2113967.1 - How To Validate A DataPump Export (EXPDP) Dump File ?
NOTE:2416814.1 - ADW: Getting "ORA-17500: ODM err:ODM HTTP Unauthorized" error during import to ADW Instance
NOTE:2446550.1 - ATP : Import to ATP From Object Store Fails with 'ORA-17500: ODM Err:ODM HTTP Unauthorized'

Finally, I used a pre-authenticated url for the dmp file as discussed in the last note.
Once this was done, here is the new impdb command which imported the data:

./impdp admin@buzztaqdev_high \ dumpfile=default_credential:<pre-authenticated url > \
transform=segment_attributes:n transform=dwcs_cvt_iots:y \
transform=constraint_use_default_index:y exclude=cluster,db_link

Sachin
Chief Architect
Oracle Apex Enthusiast
BuzzClan