DataPump is an excellent tool to transfer data between databases. Since Autonomous Databases don’t provide access to their OS there is a difficulty to use datapump to import or export data. This is bypassed by using a storage bucket.
To upload data using DataPump we will use:
- A storage bucket
- An account that has access to the bucket (and Autonomous of course)
- CloudShell
First we create the bucket and upload the dump file there. Then we use access the file’s details and copy the URI. It is formatted similarly to:
https://{namespace}.objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket_name}/o/{file}
Then we create an Auth token in our profile. It is important to note the password whn created as it will not be accessible later.
We connect to the database and create a credential:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'CredentialName',
username => 'UserName',
password => 'AUTH_TOKEN_PASSWORD'
);
END;Then we can invoke datapump using the following parameters:
directory=data_pump_dir
credential=CredentialName
dumpfile=https://{namespace}.objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket_name}/o/{file}
exclude=cluster,indextype,db_link
Keep in mind that to be able to connect to the Autonomous database from the coloudshell we need to download the wallet, update the sqlnet.ora path like it is described in Access Autonomous Database through CloudShell.