RB-004 — OCI Autonomous Database — Data Pump Runbook¶

Audience: anyone running backup / clone / migration operations against a Parallax-style OCI Autonomous Database (APEX Service or ATP workload). Pairs with infra/adb-dr.md which covers the high-level DR strategy. This doc is the how. For the APEX-app clone procedure that uses these recipes, see RB-005 — APEX clone.

Last reviewed: 19 May 2026.

TL;DR — when to use which¶

Goal	Best path	Time
Belt-and-braces snapshot before a risky change	Full Data Pump export → Object Storage + APEXExport	5–15 min
Move one app + its data to another ADB	Schema export + APEXExport, then import (optionally with `REMAP_SCHEMA`)	10–30 min
Clone prod → dev with renamed schema	Schema export + `REMAP_SCHEMA` on import + APEX import with `-schema`	15–30 min
Hourly automated backup	DBMS_SCHEDULER job calling DBMS_DATAPUMP, target = Object Storage	one-off setup
Restore after disaster	New ADB + impdp via DBMS_DATAPUMP + APEX import + metadata replay	1–2 h

Universal rule: for any APEX-workload ADB, you must use the DBMS_DATAPUMP PL/SQL API — the expdp / impdp command-line clients require SQL*Net inbound, which the APEX Service workload does not provide.

1. What Data Pump actually is¶

Data Pump is Oracle's logical export/import tool. It reads schema objects and data through SQL, and writes them to portable .dmp files. It is not a physical backup (no redo log, no block-level snapshot).

Two interchangeable surfaces:

expdp / impdp — command-line clients that connect over SQLNet. ❌ Don't work against APEX Service workload (no SQLNet inbound).
DBMS_DATAPUMP PL/SQL API — runs inside the database. ✅ Works on every ADB workload including APEX Service and Always Free. This is the path we use.

Both can write directly to OCI Object Storage by setting the dumpfile target to an Object Storage URL (no local disk staging needed).

Modes¶

Mode	What it includes	Typical use
`FULL`	Every user-owned schema, plus user definitions, password hashes, role grants, ACLs, scheduler jobs, etc.	Pre-upgrade snapshot; tenant migration
`SCHEMA`	One or more named schemas — their tables, indexes, PL/SQL, views, sequences, types, data	App cloning, single-app restore
`TABLE`	Specific tables only	Cherry-pick recovery, data warehouse loads
`TABLESPACE`	(Not usable on ADB — Oracle manages tablespaces)	n/a

Important role nuance: SCHEMA mode of your own schema needs no special role. To export other schemas (or do FULL), you need DATAPUMP_CLOUD_EXP (or DATAPUMP_EXP_FULL_DATABASE). The built-in ADMIN user has both.

2. One-time setup¶

Run these once per ADB, as ADMIN, in Database Actions → SQL Worksheet.

2.1 Create an Object Storage bucket¶

In the OCI Console: Object Storage → Buckets → Create Bucket. Pick the same region as the ADB. Enable versioning and set a lifecycle rule if you want auto-expiry (e.g. delete objects > 30 days for hourly dumps).

2.2 Generate an auth token¶

OCI Console → User Settings → Auth Tokens → Generate Token. Copy the token value immediately (it's shown once).

2.3 Register the credential inside the ADB¶

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OS_CRED',
    username        => '<your-oci-username-or-email>',
    password        => '<the-auth-token-you-just-generated>'
  );
END;
/

Confirm:

SELECT credential_name, username, enabled
  FROM user_credentials;

2.4 (Optional) Set DEFAULT_CREDENTIAL¶

If you'll only ever use one credential, you can avoid passing directory=>'OS_CRED' on every call:

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.OS_CRED';

2.5 Bucket URL pattern¶

Wherever this doc says <BUCKET_URL>, substitute the native URI form:

https://objectstorage.<region>.oraclecloud.com/n/<tenancy-namespace>/b/<bucket>/o/<object>

Find your tenancy namespace in OCI Console → Tenancy Details → Object Storage Namespace.

For UK South:

https://objectstorage.uk-london-1.oraclecloud.com/n/abc123xyz/b/parallax-backups/o/dump_%U.dmp

The %U placeholder is essential — Data Pump uses it to chunk the dump into numbered files.

3. Recipes¶

Each recipe is a complete, runnable block. Copy, fill in the angle-brackets, paste into Database Actions → SQL Worksheet.

3.0 Object naming convention (used by all recipes below)¶

All recipes follow this prefix pattern in the bucket — so multiple ADBs and schemas can share one bucket without colliding, and lifecycle rules can target by retention class:

<retention>/<source-adb>/<mode>/<schema>/<YYYYMMDDTHH24MISS>/dump_%U.dmp
<retention>/<source-adb>/<mode>/<schema>/<YYYYMMDDTHH24MISS>/export.log

Segment	Values	Notes
`<retention>`	`temp` (default) \| `permanent`	Lifecycle rules target this. `temp/` auto-expires after 7 days; `permanent/` is kept (optionally moved to Archive tier after 90 days).
`<source-adb>`	`ur-prod`, `ur-preprod`, `analytics`, …	ADB display name, lowercase, hyphenated.
`<mode>`	`full` \| `schema` \| `table`	Matches the Data Pump job mode.
`<schema>`	`eksp_prod`, `wksp_urpreprod`, or `_all` for FULL	Schema being dumped (lowercase).
`<YYYYMMDDTHH24MISS>`	`20260519T211000`	ISO-8601, sortable, no characters needing URL-encoding.

Default retention is temp. Choose permanent only for snapshots you want to keep beyond the lifecycle window (e.g. pre-upgrade safety nets, compliance archives). Hourly scheduled exports go to temp/ so they rotate automatically.

Concrete examples:

temp/ur-prod/full/_all/20260519T211000/dump_%U.dmp           # ad-hoc full export
temp/ur-prod/schema/eksp_prod/20260519T211500/dump_%U.dmp    # ad-hoc clone-prep
permanent/ur-prod/schema/eksp_prod/snapshots/pre-upgrade-20260601/dump_%U.dmp   # one-off labelled snapshot
temp/ur-preprod/schema/eksp_preprod/20260519T220000/dump_%U.dmp                 # different ADB

Lifecycle rules to set on the bucket (OCI Console → bucket → Management → Lifecycle Policy):

Rule	Prefix	Action	Days
Auto-delete temp dumps	`temp/`	Delete	7
Archive aged permanent dumps	`permanent/`	Move to Archive tier	90

Without the temp/ rule, ad-hoc dumps will sit forever and you'll pay for them.

The log file lives in DATA_PUMP_DIR (the DB's internal directory), not Object Storage — Data Pump's log writer doesn't accept Object Storage URLs. To get the log alongside the dump, copy it out after the job completes with DBMS_CLOUD.PUT_OBJECT (each recipe below includes this step).

Recipe A — Full export to Object Storage (pre-upgrade snapshot)¶

Default retention is temp (rotates out after 7 days via lifecycle rule). For a labelled pre-upgrade snapshot you actually want to keep, change v_retention to 'permanent'.

DECLARE
  v_retention CONSTANT VARCHAR2(20) := 'temp';            -- 'temp' | 'permanent'
  v_adb       CONSTANT VARCHAR2(50) := 'ur-prod';         -- lowercase ADB name
  v_ts        CONSTANT VARCHAR2(20) := TO_CHAR(SYSTIMESTAMP,'YYYYMMDD"T"HH24MISS');
  v_prefix    CONSTANT VARCHAR2(500) :=
    v_retention||'/'||v_adb||'/full/_all/'||v_ts||'/';
  v_base      CONSTANT VARCHAR2(500) :=
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'||v_prefix;
  v_log       CONSTANT VARCHAR2(100) := 'export_'||v_adb||'_full_'||v_ts||'.log';
  h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN(
         operation => 'EXPORT', job_mode => 'FULL', remote_link => NULL,
         job_name  => 'FULL_'||v_ts);

  DBMS_DATAPUMP.ADD_FILE(
    handle    => h,
    filename  => v_base || 'dump_%U.dmp',
    directory => 'OS_CRED',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

  -- log file goes into the DB's internal DATA_PUMP_DIR; copied to Object Storage after the job
  DBMS_DATAPUMP.ADD_FILE(
    handle    => h,
    filename  => v_log,
    directory => 'DATA_PUMP_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- skip optimizer stats and indexes to slim the dump (rebuild on import)
  DBMS_DATAPUMP.METADATA_FILTER(h, 'EXCLUDE_PATH_EXPR',
    'IN (''STATISTICS'',''INDEX_STATISTICS'')');

  DBMS_DATAPUMP.START_JOB(h);
END;
/

Monitor:

SELECT job_name, state, totalwork, units_completed
  FROM user_datapump_jobs;

After completion, copy the log into Object Storage alongside the dump (paste the same v_prefix and v_log values you used above):

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'OS_CRED',
    object_uri      => 'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'
                       ||'temp/ur-prod/full/_all/<YYYYMMDDTHH24MISS>/export.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'export_ur-prod_full_<YYYYMMDDTHH24MISS>.log');
END;
/

Retrieve the log without leaving the DB (useful while debugging):

SELECT * FROM TABLE(DBMS_DATAPUMP.GET_DUMPFILE_INFO(
  filename => 'export_ur-prod_full_<YYYYMMDDTHH24MISS>.log', directory => 'DATA_PUMP_DIR'));

Recipe B — Single-schema export¶

DECLARE
  v_retention CONSTANT VARCHAR2(20) := 'temp';            -- 'temp' | 'permanent'
  v_adb       CONSTANT VARCHAR2(50) := 'ur-prod';
  v_schema    CONSTANT VARCHAR2(50) := 'eksp_prod';       -- lowercase
  v_ts        CONSTANT VARCHAR2(20) := TO_CHAR(SYSTIMESTAMP,'YYYYMMDD"T"HH24MISS');
  v_prefix    CONSTANT VARCHAR2(500) :=
    v_retention||'/'||v_adb||'/schema/'||v_schema||'/'||v_ts||'/';
  v_base      CONSTANT VARCHAR2(500) :=
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'||v_prefix;
  v_log       CONSTANT VARCHAR2(100) := 'export_'||v_adb||'_'||v_schema||'_'||v_ts||'.log';
  h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,
         'SCH_'||UPPER(v_schema)||'_'||v_ts);

  DBMS_DATAPUMP.ADD_FILE(h,
    v_base || 'dump_%U.dmp',
    'OS_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

  DBMS_DATAPUMP.ADD_FILE(h,
    v_log, 'DATA_PUMP_DIR', NULL,
    DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- pick the schemas to export (uppercase, note triple-quote for the inner literal)
  DBMS_DATAPUMP.METADATA_FILTER(h, 'SCHEMA_LIST', ''''||UPPER(v_schema)||'''');
  DBMS_DATAPUMP.METADATA_FILTER(h, 'EXCLUDE_PATH_EXPR', 'IN (''STATISTICS'')');

  DBMS_DATAPUMP.START_JOB(h);
END;
/

For multiple schemas, set v_schema to a tag (e.g. 'eksp_bundle') and pass the list to METADATA_FILTER: '''EKSP_PROD'',''EKSP_CONFIG'''.

After completion, ship the log alongside the dump:

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'OS_CRED',
    object_uri      => 'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'
                       ||'temp/ur-prod/schema/eksp_prod/<YYYYMMDDTHH24MISS>/export.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'export_ur-prod_eksp_prod_<YYYYMMDDTHH24MISS>.log');
END;
/

Recipe C — Schema import with rename (REMAP_SCHEMA)¶

Pre-create the target user in the destination ADB (ADB schema-mode imports do not auto-create users in all cases):

CREATE USER PARALLAX_APP_DEV IDENTIFIED BY "<strong-password-min-12-chars>";
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO PARALLAX_APP_DEV;

That's all the parsing schema itself needs. Do not try GRANT EXECUTE ON APEX_INSTANCE_ADMIN TO <new_schema> — it'll fail with ORA-01031: insufficient privileges (ADMIN can execute that package but can't grant it onward in ADB) and the schema doesn't need it anyway.

To make APEX recognise the new schema as a parsing schema, associate it with the workspace — run this as ADMIN:

BEGIN
  APEX_INSTANCE_ADMIN.ADD_SCHEMA(
    p_workspace => 'PROD',                -- workspace name as shown in the APEX UI
    p_schema    => 'PARALLAX_APP_DEV');
END;
/

Or do it via the UI: log into APEX → Workspace Administration → Manage Service → Schemas → Add Schema. Either path is equivalent.

Then import — set v_dump_prefix to the exact path where Recipe B wrote the dump (copy it from the OCI Console → bucket → Objects):

DECLARE
  v_dump_prefix CONSTANT VARCHAR2(500) :=
    'temp/ur-prod/schema/eksp_prod/20260519T211500/';   -- from Recipe B's export
  v_old_schema  CONSTANT VARCHAR2(50)  := 'EKSP_PROD';
  v_new_schema  CONSTANT VARCHAR2(50)  := 'EKSP_PROD_NEW';
  v_ts          CONSTANT VARCHAR2(20)  := TO_CHAR(SYSTIMESTAMP,'YYYYMMDD"T"HH24MISS');
  v_log         CONSTANT VARCHAR2(100) := 'import_'||LOWER(v_new_schema)||'_'||v_ts||'.log';
  h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA',NULL,
         'SCH_IMP_'||v_new_schema||'_'||v_ts);

  DBMS_DATAPUMP.ADD_FILE(h,
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'||v_dump_prefix||'dump_%U.dmp',
    'OS_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

  DBMS_DATAPUMP.ADD_FILE(h,
    v_log, 'DATA_PUMP_DIR', NULL,
    DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- rename the schema as it's imported (REMAP_SCHEMA is import-only; the source dump is unchanged)
  DBMS_DATAPUMP.METADATA_REMAP(h, 'REMAP_SCHEMA', v_old_schema, v_new_schema);

  DBMS_DATAPUMP.START_JOB(h);
END;
/

Recipe D — Full restore into a fresh ADB¶

After provisioning a new ADB:

Set the same ADMIN password (you choose it on create).
Create credential (Recipe 2.3) in the new ADB.
Pre-create any application users before import (passwords from FULL dump will be restored if you skip this — but pre-create gives you control over privilege grants).
Run a FULL-mode import:

DECLARE
  v_dump_prefix CONSTANT VARCHAR2(500) :=
    'permanent/ur-prod/full/_all/20260519T211000/';     -- the snapshot you're restoring from
  v_ts          CONSTANT VARCHAR2(20)  := TO_CHAR(SYSTIMESTAMP,'YYYYMMDD"T"HH24MISS');
  v_log         CONSTANT VARCHAR2(100) := 'restore_'||v_ts||'.log';
  h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN('IMPORT','FULL',NULL,'RESTORE_'||v_ts);

  DBMS_DATAPUMP.ADD_FILE(h,
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'||v_dump_prefix||'dump_%U.dmp',
    'OS_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

  DBMS_DATAPUMP.ADD_FILE(h, v_log, 'DATA_PUMP_DIR', NULL,
    DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- skip objects ADB doesn't allow, common safe excludes
  DBMS_DATAPUMP.METADATA_FILTER(h, 'EXCLUDE_PATH_EXPR',
    'IN (''DB_LINK'',''CLUSTER'',''INDEXTYPE'')');

  DBMS_DATAPUMP.START_JOB(h);
END;
/

Restores typically pull from a permanent/ snapshot — temp/ dumps may already have been auto-deleted by the lifecycle rule.

Then re-create database links separately (they're excluded above because their passwords don't carry):

CREATE DATABASE LINK reporting_link
  CONNECT TO repo_user IDENTIFIED BY "<password>"
  USING '<tns-or-easyconnect>';

Recipe E — Clone an APEX app + its data to a renamed schema¶

End-to-end browser-only workflow.

On the source ADB — Cloud Shell:

sql admin/<pwd>@source_high
SQL> apex export -applicationid 100 -dir /tmp/clone -split
SQL> exit

oci os object bulk-upload \
  --bucket-name parallax-backups \
  --src-dir /tmp/clone \
  --prefix apex/

On the source ADB — Database Actions / SQL Worksheet: run Recipe B (schema export of PARALLAX_APP).

On the target ADB — Database Actions / SQL Worksheet:

-- 1. Create the target user
CREATE USER PARALLAX_APP_DEV IDENTIFIED BY "<strong-password>";
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO PARALLAX_APP_DEV;

-- 2. Register OS credential (Recipe 2.3) if not already

-- 3. Run Recipe C: schema import with REMAP_SCHEMA
--    (block as shown in Recipe C above)

Pre-import APEX wiring — go to APEX in the target instance → Workspace Administration → Manage Service → Schemas → add PARALLAX_APP_DEV to the target workspace.

On the target ADB — Cloud Shell:

oci os object get -bn parallax-backups --file f100.sql --name apex/f100.sql

sql admin/<pwd>@target_high
SQL> apex import -file f100.sql -schema PARALLAX_APP_DEV -workspace MY_WORKSPACE

Or use the PL/SQL API for fine-grained control (new app_id, offset):

BEGIN
  apex_application_install.set_workspace('MY_WORKSPACE');
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;
  apex_application_install.set_schema('PARALLAX_APP_DEV');
END;
/
@f100.sql

Verify:

SELECT application_id, application_name, owner
  FROM apex_applications
 WHERE owner = 'MY_WORKSPACE';

SELECT COUNT(*) FROM PARALLAX_APP_DEV.<your-main-table>;

Recipe F — Scheduled hourly backup¶

Once you've validated Recipe A manually, wrap it in a DBMS_SCHEDULER job so it runs hourly on its own:

Defaults below: retention temp (the bucket's lifecycle rule rotates these out after 7 days, giving a rolling 7-day backup window), ADB name ur-prod. Adjust both for your environment.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'HOURLY_DATAPUMP_EXPORT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_retention CONSTANT VARCHAR2(20) := 'temp';
        v_adb       CONSTANT VARCHAR2(50) := 'ur-prod';
        v_ts        CONSTANT VARCHAR2(20) := TO_CHAR(SYSTIMESTAMP,'YYYYMMDD"T"HH24MISS');
        v_prefix    CONSTANT VARCHAR2(500) :=
          v_retention||'/'||v_adb||'/full/_all/'||v_ts||'/';
        h NUMBER;
      BEGIN
        h := DBMS_DATAPUMP.OPEN('EXPORT','FULL',NULL,'HRLY_'||v_ts);
        DBMS_DATAPUMP.ADD_FILE(h,
          'https://objectstorage.uk-london-1.oraclecloud.com/n/<ns>/b/<bucket>/o/'||v_prefix||'dump_%U.dmp',
          'OS_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);
        DBMS_DATAPUMP.METADATA_FILTER(h,'EXCLUDE_PATH_EXPR',
          'IN (''STATISTICS'',''INDEX_STATISTICS'')');
        DBMS_DATAPUMP.START_JOB(h);
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Hourly full DB export to OCI Object Storage (temp/ prefix)');
END;
/

Check status:

SELECT job_name, last_start_date, last_run_duration, state, failure_count
  FROM user_scheduler_jobs
 WHERE job_name = 'HOURLY_DATAPUMP_EXPORT';

SELECT log_date, status, error#
  FROM user_scheduler_job_run_details
 WHERE job_name = 'HOURLY_DATAPUMP_EXPORT'
 ORDER BY log_date DESC FETCH FIRST 10 ROWS ONLY;

The temp/ lifecycle rule (§3.0) handles cleanup automatically — no extra retention plumbing needed. For pre-upgrade or compliance snapshots you want to keep, run Recipe A manually with v_retention := 'permanent' instead of relying on this scheduled job.

4. What is captured vs. not¶

Item	FULL mode	SCHEMA mode	Notes
Tables, indexes, views, sequences, types	✅	✅	Reliable
PL/SQL packages, procedures, functions, triggers	✅	✅	Reliable
Row data, LOBs, BLOBs	✅	✅	Reliable
User accounts	✅	❌	Pre-create users before SCHEMA-mode import
User password hashes	✅	❌	FULL captures `IDENTIFIED BY VALUES '<hash>'` — passwords stay the same after restore
System & role grants	✅	⚠️ (only grants the schema owns)	Pre-grant in SCHEMA mode
Network ACLs (`DBMS_NETWORK_ACL_ADMIN`)	✅	❌	Re-apply manually if needed
Database links (definition)	✅	✅	But the password is not in the dump — recreate with `IDENTIFIED BY`
Scheduler jobs	✅	✅ (if schema-owned)
APEX application metadata	⚠️ via APEX_xxxxx schema (fragile)	❌	Use APEXExport instead — supported and clean
APEX workspace files (Shared Components)	bundled with APEX app export	n/a
APEX Static Workspace Files	❌	❌	Export separately from Workspace Utilities → Static Workspace Files
ORDS modules / REST endpoints	⚠️ partial	❌	Snapshot `USER_ORDS_*` views separately
ADMIN password	❌	❌	Set fresh on new instance
Connect strings / wallet	❌	❌	New ADB = new endpoint. Use vanity URL if URL stability matters.
Object Storage credentials (`DBMS_CLOUD.CREATE_CREDENTIAL`)	partial	❌	Recreate — auth tokens are tied to OCI IAM identity
TDE-encrypted columns	✅	✅	Re-encrypted on import using the target's keys

Rule of thumb: Data Pump alone is "schema and data." Anything that lives outside a schema — workspace files, ORDS server-side config, ACLs, credentials, the ADMIN account — needs separate capture. The brief's §4.1 recommends three artefacts in parallel for exactly this reason: Data Pump + APEXExport + metadata script.

5. Remap operations during import¶

Beyond REMAP_SCHEMA, Data Pump supports several other remaps you can apply via DBMS_DATAPUMP.METADATA_REMAP:

Remap name	Example	Effect
`REMAP_SCHEMA`	`'PARALLAX_APP'` → `'PARALLAX_APP_DEV'`	Whole-schema rename
`REMAP_TABLE`	`'PARALLAX_APP.ORDERS'` → `'ORDERS_2025'`	Rename a single table
`REMAP_TABLESPACE`	(not applicable in ADB)	n/a
`REMAP_DATA`	Apply a PL/SQL function to column values	Mask PII when cloning prod → dev

Example — masking customer email when cloning to dev:

DBMS_DATAPUMP.METADATA_REMAP(h, 'REMAP_DATA',
  'PARALLAX_APP.CUSTOMERS.EMAIL',
  'PARALLAX_APP_DEV.MASKING_PKG.MASK_EMAIL');

(MASK_EMAIL is a function you define in the target DB that returns the masked value.)

6. Working with APEX — the pairing pattern¶

Pure Data Pump round-tripping of the APEX internal schema (APEX_<version>) is possible but fragile — Oracle's supported method is to use APEXExport alongside Data Pump:

Tool	What it captures
`DBMS_DATAPUMP` (schema mode of the parsing schema)	Tables, data, PL/SQL the app reads from
`apex export` (SQLcl)	App pages, themes, plugins, shared components, supporting objects, Static App Files
Workspace Utilities → Export Static Workspace Files	Static workspace-level files (CSS, images)
Small SQL script (`USER_ORDS_*`, `USER_SCHEDULER_JOBS`, ACLs)	Server-side environment metadata

Restore order:

New ADB up.
Data Pump import (Recipe D or C).
Workspace ready in APEX (create or restore via Internal Workspace Administration).
apex import of each f<id>.sql into the correct workspace + parsing schema.
Re-upload Static Workspace Files.
Re-apply ACLs and ORDS config.

Per-app: ~1–2 minutes for apex import once data is in. The slow step is always Data Pump for the data.

7. Dos & Don'ts¶

✅ DO¶

Use Object Storage as the dump target, not DATA_PUMP_DIR. Especially on Always Free — local exports permanently consume your 20 GB quota even after you delete the dump (storage doesn't shrink).
Run as ADMIN or a user with DATAPUMP_CLOUD_EXP. Without that role, FULL and cross-schema exports fail.
Encrypt sensitive dumps. Add DBMS_DATAPUMP.SET_PARAMETER(h, 'ENCRYPTION', 'ALL') and DBMS_DATAPUMP.SET_PARAMETER(h, 'ENCRYPTION_PASSWORD', '<pwd>'). On import, supply the same password.
Set a lifecycle rule on the bucket. Without one, hourly dumps will balloon storage costs over months.
Test the restore before you need it. Drill it once, time it, write down what surprised you.
Combine with APEXExport. Pure Data Pump is not a clean APEX-app backup.
Pre-create users before SCHEMA-mode imports. Avoids "user does not exist" errors and lets you control privileges.
Use EXCLUDE_PATH_EXPR='IN(''STATISTICS'')' on exports to slim the dump significantly (rebuild stats post-import with DBMS_STATS.GATHER_SCHEMA_STATS).
Use vanity URLs / custom domains for any APEX app whose URL must stay stable across restores or migrations.
Snapshot the bucket cross-region if regional protection matters — Object Storage replication is much cheaper than cross-region ADG.

❌ DON'T¶

Don't expect expdp / impdp to work on APEX Service workload. They need SQL*Net inbound, which APEX Service blocks. Use DBMS_DATAPUMP instead.
Don't rely on Data Pump alone to restore an APEX app. Schema-internal APEX tables round-trip badly across versions; use APEXExport for the apps.
Don't import DB_LINKs blindly — passwords aren't in the dump. Exclude on import then recreate.
Don't run a SCHEMA-mode export of "everything" expecting users to come back. Users are FULL-mode only.
Don't bake auth tokens into PL/SQL. Use DBMS_CLOUD.CREATE_CREDENTIAL and reference the credential name; rotate tokens via DBMS_CLOUD.UPDATE_CREDENTIAL.
Don't schedule sub-15-minute exports on minimum-shape ADBs. Job-slave / parallel limits will queue them.
Don't qualify table names with schema prefixes in APEX SQL (SELECT * FROM PARALLAX_APP.ORDERS). It breaks REMAP_SCHEMA clones. Use unqualified ORDERS and let the parsing schema resolve.
Don't assume ADMIN survives a restore. Set the new ADMIN password fresh on the target ADB before kicking off any import.
Don't store dumps in the home region only if you care about regional outages — replicate the bucket cross-region (Frankfurt or Amsterdam from London).

8. Troubleshooting¶

Symptom	Likely cause	Fix
`ORA-39001: invalid argument value` on START_JOB	Bucket URL malformed, credential not authorised	Validate URL pattern in §2.5; confirm the auth token is current and the user has `manage objects` on the bucket compartment
`ORA-39002: invalid operation`	Trying to do FULL mode without the role	Run as `ADMIN`, or grant `DATAPUMP_CLOUD_EXP`
Import fails with `ORA-39142: incompatible version number`	Source DB version > target	Re-export with `VERSION=` parameter set to target's version, or upgrade target first
`ORA-31694: master table failed to load/unload`	Bucket changed mid-job, or partial dump	Re-run; the dump file isn't valid if it didn't complete
`ORA-31626: job does not exist` after a long-running job	Idle session timeout closed the API handle	The job is fine — use `DBMS_DATAPUMP.ATTACH` from a fresh session to monitor
APEX import: app loads but pages 404	Workspace not associated with the new schema	Workspace Administration → Manage Service → Schemas → add the new schema
Restored DB has zero rows in some tables	Schema-mode export was used but cross-schema FK or trigger silently dropped data	Use FULL mode, or add all related schemas to `SCHEMA_LIST`
`ORA-65096: invalid common user or role name`	Pre-12c naming style hitting CDB constraints	Pre-create users with quoted lowercase names, or accept the renamed user

Live monitoring:

-- progress of a running job
SELECT job_name, state, totalwork, units_completed,
       ROUND(units_completed/NULLIF(totalwork,0)*100,1) AS pct
  FROM user_datapump_jobs;

-- attach from a new session if your original session died
DECLARE h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.ATTACH(job_name => 'FULL_20260519_1430', job_owner => 'ADMIN');
  DBMS_DATAPUMP.STATUS(h);
END;
/

9. Pre-flight checklists¶

Before clicking "Upgrade Always Free → Paid"¶

OS credential set up (§2.3) — verified by SELECT * FROM user_credentials
Bucket created with lifecycle rule
Successful FULL export run manually (Recipe A) and restore.log shows zero errors
APEX apps exported (apex export -instance for all apps in workspace)
Static Workspace Files exported
Environment metadata script captured
At least one restore drill performed into a throwaway ADB

Before enabling ADG on production¶

Tenancy quota verified — enough free ECPU and storage for a second instance
Quiet window picked (defensive — Oracle says no downtime, but minimise surprises)
Latest backup confirmed in Object Storage
Team aware that Stop/Restart/Restore/Move admin actions are blocked while Lifecycle = Updating

Before cloning prod → dev¶

Target workspace exists in the target APEX instance
Target schema pre-created (CREATE USER ...)
Target workspace associated with the new schema (Manage Service → Schemas)
PII masking via REMAP_DATA configured if exporting personal data into a less-trusted dev environment
DNS / app config plan for any external integrations pointing at the new app

10. References¶

Oracle docs — Use Oracle Data Pump to Export Data from Autonomous Database
Oracle docs — Export Data to Object Store Using CREDENTIAL Parameter
Oracle docs — Export Data to Object Store using DEFAULT_CREDENTIAL
Oracle docs — Load Data Using Oracle Data Pump on Autonomous Database
Oracle docs — DBMS_DATAPUMP package reference
Oracle docs — DBMS_CLOUD package reference
Oracle docs — APEX — Managing Application Backups (APEXExport)
Oracle docs — APEX Exporting and Importing Using SQLcl
Oracle docs — APEX Service workload limitations
Oracle blog — Migrate your APEX Applications to Autonomous Database
Oracle docs — Connect to Database Actions
Oracle docs — OCI Cloud Shell — pre-installed tools
Oracle docs — APEX vanity URLs
Companion doc — OCI ADB Failure & Low-Cost DR Brief
Companion runbook — RB-005 — APEX App + Schema Clone