Using Autonomous DB - Data Pump

The Data Pump tool in Autonomous DB is designed to import DMP files as an object from a bucket. By default, the database does not have access to the buckets, that is why we need to do the following steps. Similar steps are required for external tables from buckets (not covered here).

Few relevant links:

Oracle Doc - importing data using data pump

CarlosAL - how to import dump files into oci autonomous database using database actions

Stuart Coggins (Coggs) - cloud credentials with oci

If you try using Data Pump with no setup, you should expect an error:

Unable to load the import tool

A resource principal or a cloud service credential must be configured in order to access the Object Storage bucket. For detailed information please refer to the documentation.

In most cases in Autonomous DB things are rather autonomous or one click away. This is not one of those cases.

Part of the setup is done in the DB and part in the OCI itself. Since I use it only once, I'm going to use the Admin user in DB and my user in OCI. If you plan using Data Pump, as repeating process, you might want to consider otherwise, especially regarding the OCI user, so you are not dependent on a specific person.

As Admin user in SQL I run: 

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

This creates the credential "OCI$RESOURCE_PRINCIPAL" in the Database.

Don't bother the Data Pump yet... You wouldn't get the previous error and will be able to select Credential Name (OCI$RESOURCE_PRINCIPAL) but will not be able to select Compartment or a Bucket.

To access the OCI bucket I will 

• Create a Dynamic Group & relevant Policy in OCI
• Create API keys for my user in OCI
• Use them in a relevant SQL 

Only then, I can use the Data Pump.

Create a Dynamic Group & relevant Policy in OCI

In OCI go to your Autonomous AI DB, click the ... (3 dots) and copy OCID. We will use it in the dynamic group setting

For managing dynamic groups, you must have one of the following privileges:

• You are a member of the Administrators group.
• You are granted the Identity Domain Administrator role or the Security Administrator role.
• You are a member of a group that is granted manage identity-domains or manage dynamic-groups.

Under OCI console: Identity and Security, Domains  

In the domain (I used the default) click Dynamic Groups and create Dynamic group.

Set the Rule in the format 

any { resource.id = 'Copied DB OCID'}

Press Create

Under OCI console: Identity and Security, Policies  

Press Create Policy

The documentation named the policy ObjectStorageReadersPolicy, I will do the same.

Select the relevant compartment level.

In policy builder switch to manual and enter

Allow dynamic-group YOUR_DYNAMIC_GROUP_NAME to read buckets in tenancy

Create and add a second statement

Allow dynamic-group YOUR_DYNAMIC_GROUP_NAME to read objects in tenancy

  

The flow is: the Dynamic Group is connected to the DB and the Policy is connected to the Dynamic Group.

Create API keys for my user in OCI

The documentation advised to create a user for this task. I used my own.

Click the profile icon on the Right and click your username.

This brings you to Identity & Security, My Profile

Select Tokens and Keys and press Add API key. 

Download the Private key. Now we can press Add.

Copy and save the Configurate data from next screen. Close.

The user, fingerprint and tenancy information will be used later to create your user database.

Use them in a relevant SQL 

Return to the SQL in Autonomous AI databases (in your database select the SQL Database Action).

Use the value from previous section of API keys to in the code. All cove from the data you saved, except the private key (only the text between -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY-----):

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL (

    credential_name => 'API_KEY',

    user_ocid              => 'YOUR User OCID',

    tenancy_ocid           => 'YOUR Tenancy OCID',

    private_key            => 'M. . .T=', 

    fingerprint            => 'YOUR fingerprint');

END;

(It might also work with AUH_TOKEN as described in  Stuart Coggins (Coggs) - cloud credentials with oci, didn't try it myself.) 

Now it's time go back to the Data Pump tool in the Database, and press Import. In my case it took a minute or 2 for data Pump to be able to actually see the bucket.