How to import Data d(p)ump into Oracle OCI Autonomous Database

PreRequisite

• data dump export from your source database
• a host (read here) with sqlplus installed ( try OCI vm instance with image Oracle Cloud Developer Linux, it has sqlplus , sqlcl pre installed) and able to connect to Autonomous Database
• an Oracle OCI object storage bucket (upload your database dump to this bucket, your bucket can be private, no need to set it public. Once uploaded, copy the link via object details button, the link is needed later)
• an Oracle OCI user who has MANAGE Buckets/Objects rights to this bucket

———————————————————————————-

Step !: Create an Auth TOKEN from OCI user

save the token somewhere safe (you can copy it during creation, once created it can NOT be displayed anymore)

———————————————————————————-

Step 2: use sqlplus login to your autonomous database using !!!<ADMIN> account and run

begin
   dbms_cloud.create_credential(credential_name => 'OBJECT_STORE_CRED'
                               ,username        => '${your objectuser}'
                               ,password        => '${your auth token}');
end;
/

# to list all created credentials
SELECT credential_name, username, comments FROM all_credentials;

———————————————————————————-

Step 3: create .par file

On the host machine, create a .par file e.g my_import.par and type the following content ( you can also work without the par file, but then u need to type everything all at once )

Infor about the parameters:

Schemas is optional if your export is on schema level, remap_tablespace optional , remap_schema is optional as well. content optional, EXCLUDE is optional, table_exists_action optional. logfile optional

read more here

directory=DATA_PUMP_DIR    
credential=OBJECT_STORE_CRED
schemas=${SOURCE_SCHEMA}
remap_tablespace=${SOURCE_TABLESPACE}:${TARGET_TABLESPACE}
remap_schema=${SOURCE_SCHEMA}:${TARGET_SCHEMA}
dumpfile=${URL To your Dump file on your object storage}
content=DATA_ONLY
EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX 
TABLE_EXISTS_ACTION=REPLACE
LOGFILE=my_import_log.log

Step 4: execute import command

when you have this .par file , type

impdp userid=ADMIN/${password}@${tns_name} parfile=${path to your .par file}

———————————————————————————-

Step 5: access the log file

As long as your connection is good, even there are errors during import, it should finish with a log file. You can check the log file by running this sql.

   select *
   from   dbms_cloud.list_files('DATA_PUMP_DIR')
   order  by last_modified desc;

Store your logfile to OCI object storage

run this plsql code to upload log file to your bucket storage and so that you can download it and view it in detail

begin
   dbms_cloud.put_object('OBJECT_STORE_CRED'
                        ,'${URL}'
                        ,'DATA_PUMP_DIR'
                        ,'${file name you see in previous sql}');
end;
/

e.g ${URL} ‘https://objectstorage.eu-amsterdam1.oraclecloud.com/n/aosdfasdf/b/DD/o/${anyname}.log’