Autonomous Database Data Pump through Object Storage: credentials, dump file URIs, parallelism and logs

#Goal

You need a logical copy of one or more schemas from an Autonomous Database (ADB): to refresh a test database, to move a schema to another tenancy or region, or to keep an export before a risky change. On ADB you have no access to the server file system, so the dump files go to OCI Object Storage. This post covers the working path end to end: create an auth token and a database credential, run expdp with the dump files written straight to a bucket, run impdp from the same bucket, and get the log files back so you can read them.

Tip

A clone is often the faster way to make a copy of a whole ADB. Use Data Pump when you need only some schemas, a different target (another tenancy, an on-prem or Base Database), or a file you can keep outside the database.

#Prerequisites

• An Oracle Data Pump client of 19.9 or later. That version adds the credential parameter for exports straight to Object Storage. The current Oracle Instant Client with the Tools package works.
• The ADB wallet, plus a working tnsnames.ora alias such as mydb_high.
• A private bucket in the same region as the database.
• For a full export, or to export objects owned by other users, the database user needs DATAPUMP_CLOUD_EXP. For the matching imports it needs DATAPUMP_CLOUD_IMP. ADMIN already has both.

#Step 1: create an auth token and a database credential

On ADB 19c the Object Storage credential for Data Pump exports must be an auth token. Resource principal and OCI native signing-key credentials are supported for exports only on 26ai.

# Find your user OCID and the Object Storage namespace
oci iam user list --name "dp-export-user" --query 'data[0].id' --raw-output
oci os ns get --query data --raw-output

# Create the auth token. Copy the token value now; it is shown only once.
oci iam auth-token create \
  --user-id ocid1.user.oc1..aaaa... \
  --description "adb-datapump"

Store the token in the database as a credential. Connect as ADMIN or as the user that will run the job:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DP_CRED',
    username        => 'dp-export-user',    -- OCI user name; include the identity domain or IdP prefix if your user has one
    password        => '<auth-token>'
  );
END;
/

Test the credential against the bucket before you start a long job:

SELECT object_name, bytes
FROM   DBMS_CLOUD.LIST_OBJECTS(
         'DP_CRED',
         'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<namespace>/b/adb-exports/o/');

If this fails with an authorization error, nothing in Data Pump will work. Fix the user name, the token or the bucket policy first.

#Step 2: export to the bucket

expdp admin@mydb_high \
  schemas=APP_OWNER \
  credential=DP_CRED \
  dumpfile=https://<namespace>.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/<namespace>/b/adb-exports/o/app_%L.dmp \
  filesize=5GB \
  parallel=4 \
  directory=data_pump_dir \
  logfile=app_export.log \
  encryption_pwd_prompt=yes

What each setting does:

Warning

Running expdp on the HIGH service with a high parallel value takes CPU away from the application. Run large exports out of hours, or on a refreshable clone, which takes the load off production.

#Don't be surprised by the objects in the bucket

Data Pump uploads each dump file in chunks of 10 MB or less. In the console you'll see app_100.dmp at 0 bytes plus objects named app_100.dmp_aaaaaa and so on. This is expected. impdp and the DBMS_CLOUD procedures put the chunks back together. To download a dump for use outside OCI, follow Oracle's curl-based download procedure. Don't download the chunks one at a time from the console.

oci os object list --bucket-name adb-exports --prefix app_ \
  --query 'data[].{name:name,size:size}' --output table

#Step 3: get the export log

The log is in DATA_PUMP_DIR inside the database. Check that it exists, then copy it to the bucket:

SELECT object_name, bytes, last_modified
FROM   DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR')
WHERE  object_name LIKE '%.log';

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'DP_CRED',
    object_uri      => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/<namespace>/b/adb-exports/o/logs/app_export.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'app_export.log');
END;
/

oci os object get --bucket-name adb-exports --name logs/app_export.log --file - | tail -30

Confirm the last line reads successfully completed, or that it lists only errors you expected. Read every ORA- line before you delete the source data or start the import.

#Step 4: import into the target ADB

Create the same credential in the target database (step 1), then:

impdp admin@targetdb_high \
  credential=DP_CRED \
  dumpfile=https://<namespace>.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/<namespace>/b/adb-exports/o/app_%L.dmp \
  parallel=4 \
  directory=data_pump_dir \
  logfile=app_import.log \
  encryption_pwd_prompt=yes \
  remap_schema=APP_OWNER:APP_TEST \
  exclude=cluster,indextype,db_link

• The exclude list is the one Oracle shows for ADB imports. Clusters, indextypes and database links aren't supported or need to be recreated by hand.
• The import must use the same credential type that wrote the dump. If you exported straight to Object Storage with an auth token, you can't import those files with resource principal.
• For Data Pump clients older than 18.3 (import only), set ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DP_CRED' and prefix the dump file with default_credential:.
• To refresh a schema that already exists, drop it first or use table_exists_action. Decide which before you start.

Caution

remap_schema into a schema that already exists, combined with table_exists_action=replace, overwrites tables without asking. Run impdp against production only after you've checked the connect string and the target schema twice.

Copy app_import.log to the bucket with DBMS_CLOUD.PUT_OBJECT, exactly as in step 3, and read it.

#Common failures

#Prevent it next time

• Keep a tested script with the credential name, URI pattern and parallel value for each database. Don't rebuild the command during an incident.
• Rotate the auth token on a schedule, then update the credential with DBMS_CLOUD.UPDATE_CREDENTIAL. Track the expiry the same way you track certificate expiry.
• Put a lifecycle rule on the export bucket so old dump chunks don't build up storage cost.
• Run a test import into a non-production ADB at least once a quarter. An export you've never imported isn't a backup you can rely on.