RB-005 — OCI ADB — APEX App + Schema Clone Runbook¶

Step-by-step procedure for cloning an APEX application along with its parsing schema between two Autonomous Databases. Verified end-to-end against UR-Prod (E5) → UR-PreProd (workspace UR_PREPROD on O3 / apex2.448.global) on 19 May 2026.

Companion docs: infra/adb-dr.md for strategy, RB-004 — Data Pump runbook for general Data Pump mechanics.

TL;DR¶

To clone an APEX app + its data from one ADB to another, the supported pattern is two parallel artefacts + a target-side setup:

Source ADB: schema-mode Data Pump export → OCI Object Storage (the data)
Source ADB: apex export → OCI Object Storage (the application)
Target ADB: create the destination schema (CREATE USER)
Target ADB: create the workspace + workspace admin user (APEX_INSTANCE_ADMIN.ADD_WORKSPACE + APEX_UTIL.CREATE_USER)
Target ADB: schema-mode Data Pump import with REMAP_SCHEMA (the data)
Target ADB: apex import of the application (the app)
Verify — invalid objects, row counts, workspace login, app pages render

For subsequent data refreshes (prod → pre-prod sync, keeping app + users intact), see §8 Data refresh below — use TABLE_EXISTS_ACTION = TRUNCATE with INCLUDE_PATH_EXPR = TABLE_DATA.

0. Prerequisites¶

Source ADB: APEX workload, app already deployed
Target ADB: any workload (APEX Service is fine)
An OCI Object Storage bucket in the same region (e.g. adb-data-dump)
An OCI auth token for your IAM user (OCI Console → User Settings → Auth Tokens → Generate)
Your tenancy's Object Storage namespace (OCI Console → Tenancy Details → Object Storage Namespace — e.g. lrqsoobrqq3r)
ADMIN access to both ADBs

1. Source ADB — define credential¶

Run as ADMIN in Database Actions → SQL Worksheet on the source ADB:

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

-- Verify
SELECT credential_name, username, enabled FROM user_credentials;

-- (Optional) default credential so you can skip the credential name on every call
ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DMP_CRED';

2. Source ADB — schema export¶

DECLARE
  v_retention CONSTANT VARCHAR2(20) := 'temp';            -- 'temp' | 'permanent'
  v_adb       CONSTANT VARCHAR2(50) := 'ur-prod';         -- source ADB name, lowercase
  v_schema    CONSTANT VARCHAR2(50) := 'wksp_prod';       -- source schema, 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/<namespace>/b/adb-data-dump/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',
    'DMP_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);

  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;
/

Record the exact prefix that gets written (e.g. temp/ur-prod/schema/wksp_prod/20260519T213314/) — you'll paste it into the import in step 6.

2.1 Monitor / verify the export¶

-- Active job state
SELECT job_name, state, totalwork, units_completed
  FROM user_datapump_jobs;

-- Recent job history
SELECT owner_name, job_name, state, start_time, end_time
  FROM dba_datapump_jobs
 ORDER BY start_time DESC FETCH FIRST 5 ROWS ONLY;

Wait until the active job disappears (or shows NOT RUNNING) before continuing. Look in the log to confirm a clean finish — the last line should be "Job ‘ADMIN’.’SCH_…’ successfully completed at …".

2.2 Ship the log to Object Storage alongside the dump¶

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'DMP_CRED',
    object_uri      => 'https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/adb-data-dump/o/'
                       ||'temp/ur-prod/schema/wksp_prod/<YYYYMMDDTHH24MISS>/export.log',
    directory_name  => 'DATA_PUMP_DIR',
    file_name       => 'export_ur-prod_wksp_prod_<YYYYMMDDTHH24MISS>.log');
END;
/

3. Source ADB — APEX application export¶

Two options. Either works.

3.1 Via APEX App Builder UI (browser)¶

Log into APEX as a workspace developer on the source.
App Builder → select your app → Export / Import → Export.
Save the f<app_id>.sql file. Upload to the bucket if you want it kept alongside the schema dump (oci os object put in Cloud Shell, or drag-and-drop in the bucket UI).

3.2 Via SQLcl (Cloud Shell, browser-only)¶

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

oci os object bulk-upload \
  --bucket-name adb-data-dump \
  --src-dir /tmp/export \
  --prefix temp/ur-prod/apex/100/<YYYYMMDDTHH24MISS>/

Pick whichever fits your workflow. The artefact in both cases is the f100.sql (and any split-out files).

4. Target ADB — create the destination schema¶

On the target ADB (also as ADMIN):

CREATE USER WKSP_URPREPROD IDENTIFIED BY "<strong-password>";
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO WKSP_URPREPROD;

That's all the parsing schema needs at this stage. Don't try GRANT EXECUTE ON APEX_INSTANCE_ADMIN to this user — it'll throw ORA-01031 and isn't needed.

5. Target ADB — create the workspace and a workspace admin¶

5.1 Create the workspace and associate the schema¶

BEGIN
  APEX_INSTANCE_ADMIN.ADD_WORKSPACE(
    p_workspace      => 'UR_PREPROD',
    p_primary_schema => 'WKSP_URPREPROD');
END;
/

-- Verify
SELECT workspace_id, workspace, source_identifier
  FROM apex_workspaces
 WHERE workspace = 'UR_PREPROD';

SELECT workspace_name, schema
  FROM apex_workspace_schemas
 WHERE workspace_name = 'UR_PREPROD';

Both queries should return a row.

5.2 Create a workspace admin user¶

BEGIN
  APEX_UTIL.SET_WORKSPACE(p_workspace => 'UR_PREPROD');

  APEX_UTIL.CREATE_USER(
    p_user_name                    => 'admin',
    p_email_address                => 'you@example.com',
    p_web_password                 => '<strong-password>',
    p_developer_privs              => 'ADMIN',
    p_change_password_on_first_use => 'N',
    p_default_schema               => 'WKSP_URPREPROD');

  COMMIT;
END;
/

-- Verify
SELECT user_name, default_schema, is_admin
  FROM apex_workspace_apex_users
 WHERE workspace_name = 'UR_PREPROD';

If CREATE_USER throws PLS-00306, your APEX version's signature differs. Check the live signature via SELECT argument_name, data_type FROM user_arguments WHERE package_name='APEX_UTIL' AND object_name='CREATE_USER' ORDER BY position; and trim the call to only parameters your version supports. The minimum is p_user_name + p_web_password.

5.3 Alternative — Internal Workspace UI¶

If the PL/SQL keeps fighting you, do it via the UI:

https://<target-adb-host>/ords/apex_admin
Workspace = INTERNAL, Username = ADMIN, Password = ADB ADMIN password
Manage Workspaces → UR_PREPROD → Manage Developers and Users → Create User → tick User is a workspace administrator = Yes

6. Target ADB — schema import with REMAP_SCHEMA¶

Set up the same credential on the target ADB first (repeat step 1 there if not already done). Then:

DECLARE h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA',NULL,
         'SCH_IMP_'||TO_CHAR(SYSDATE,'YYYYMMDD_HH24MI'));

  DBMS_DATAPUMP.ADD_FILE(h,
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/adb-data-dump/o/'
    ||'temp/ur-prod/schema/wksp_prod/<YYYYMMDDTHH24MISS>/dump_%U.dmp',
    'DMP_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

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

  -- rename the schema as it's imported
  DBMS_DATAPUMP.METADATA_REMAP(h, 'REMAP_SCHEMA',
    'WKSP_PROD', 'WKSP_URPREPROD');

  DBMS_DATAPUMP.START_JOB(h);
END;
/

The REMAP_SCHEMA rewrites object ownership on the way in — the source dump itself is unchanged, so the same dump file can be imported under many different names if you want multiple clones.

6.1 Post-import sanity¶

-- Make sure nothing landed invalid
BEGIN DBMS_UTILITY.COMPILE_SCHEMA('WKSP_URPREPROD', FALSE); END;
/

SELECT object_type, object_name, status
  FROM all_objects
 WHERE owner = 'WKSP_URPREPROD' AND status = 'INVALID';

-- Refresh statistics (we excluded them on export to slim the dump)
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('WKSP_URPREPROD'); END;
/

-- Spot-check a key table's row count vs. source
SELECT COUNT(*) FROM WKSP_URPREPROD.<your_main_table>;

7. Target ADB — import the APEX application¶

Log into the new workspace at https://<target-adb-host>/ords/r/ur_preprod (or /ords/ur_preprod) using the admin user you created in §5.2.

7.1 Via App Builder UI¶

App Builder → Import.
Upload the f100.sql from step 3.
When prompted for Parsing Schema, pick WKSP_URPREPROD.
Choose Reuse application ID or Auto-assign new ID — either works; reuse keeps the URL path identical.
Install → Done.

7.2 Via SQLcl¶

sql admin/<password>@target_high
SQL> apex import -file f100.sql -schema WKSP_URPREPROD -workspace UR_PREPROD

8. Data refresh on an existing pre-prod¶

Once the clone is set up, you'll usually want to refresh just the data periodically (prod has moved on, pre-prod should catch up) without nuking the schema, the APEX app, the workspace users, or the work in progress.

8.1 What's preserved across a data-only refresh¶

Item	Preserved?	Notes
Schema user (`WKSP_URPREPROD`)	✅	Not dropped, not recreated
Tables, indexes, constraints, PL/SQL	✅	`TABLE_EXISTS_ACTION=TRUNCATE` keeps structure; only rows are reloaded
APEX application (pages, themes, etc.)	✅	Lives in `APEX_240200`, not your schema
APEX workspace `UR_PREPROD`	✅	Same
APEX workspace users (the ones you created manually)	✅	Same
Row data	🔄 refreshed from source dump
Sequences	⚠️ skipped by default	Won't move; if you need parity with prod's currval, see §8.3

8.2 The refresh procedure¶

Step A — fresh schema export on the source (repeat §2 with a new timestamp; produces a new dump_%U.dmp set in Object Storage).

Step B — data-only import on the target with TRUNCATE:

DECLARE h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN('IMPORT','SCHEMA',NULL,
         'SCH_REFRESH_'||TO_CHAR(SYSDATE,'YYYYMMDD_HH24MI'));

  DBMS_DATAPUMP.ADD_FILE(h,
    'https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/adb-data-dump/o/'
    ||'temp/ur-prod/schema/wksp_prod/<NEW_YYYYMMDDTHH24MISS>/dump_%U.dmp',
    'DMP_CRED', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_URIDUMP_FILE);

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

  DBMS_DATAPUMP.METADATA_REMAP(h, 'REMAP_SCHEMA',
    'WKSP_PROD', 'WKSP_URPREPROD');

  -- Critical refresh options
  DBMS_DATAPUMP.SET_PARAMETER(h, 'TABLE_EXISTS_ACTION', 'TRUNCATE');
  -- Only load TABLE_DATA — skip metadata since the schema already exists
  DBMS_DATAPUMP.METADATA_FILTER(h, 'INCLUDE_PATH_EXPR', 'IN (''TABLE_DATA'')');
  -- Allow TRUNCATE to work even when FKs exist (value 2 = DISABLE_REFERENTIAL_CONSTRAINTS)
  DBMS_DATAPUMP.SET_PARAMETER(h, 'DATA_OPTIONS', 2);

  DBMS_DATAPUMP.START_JOB(h);
END;
/

Step C — re-gather statistics:

BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('WKSP_URPREPROD'); END;
/

That's it. Log back into the APEX workspace; the app runs against the refreshed data with no other changes.

8.3 Sequence drift (and how to fix)¶

TABLE_EXISTS_ACTION=TRUNCATE keeps existing sequences as-is. After a few refreshes, pre-prod sequences will be way behind prod, which can cause primary-key collisions if anything in pre-prod inserts new rows manually.

To resync a sequence to match prod:

-- On source (prod): get the current value
SELECT sequence_name, last_number FROM user_sequences;

-- On target (pre-prod): bump the sequence to match (with a safety margin)
ALTER SEQUENCE WKSP_URPREPROD.MY_SEQ RESTART START WITH <prod_last_number + 1000>;

Or, if you'd rather have sequences also refresh automatically, drop them before the refresh and let Data Pump's TABLE_EXISTS_ACTION policy apply. The cleanest is a small wrapper:

BEGIN
  FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE WKSP_URPREPROD.'||s.sequence_name;
  END LOOP;
END;
/
-- then re-run the refresh including metadata for SEQUENCE

8.4 Trigger gotcha¶

If your tables have BEFORE INSERT / audit triggers (e.g. updated_at = SYSTIMESTAMP), they fire on every row Data Pump loads. For a clean prod-data snapshot, disable them first:

BEGIN
  FOR t IN (SELECT trigger_name FROM user_triggers WHERE status='ENABLED') LOOP
    EXECUTE IMMEDIATE 'ALTER TRIGGER WKSP_URPREPROD.'||t.trigger_name||' DISABLE';
  END LOOP;
END;
/

-- run the refresh

BEGIN
  FOR t IN (SELECT trigger_name FROM user_triggers WHERE status='DISABLED') LOOP
    EXECUTE IMMEDIATE 'ALTER TRIGGER WKSP_URPREPROD.'||t.trigger_name||' ENABLE';
  END LOOP;
END;
/

9. Manual steps you'll always need to do (not covered by Data Pump or APEX import)¶

These are the same as in our verified UR-Prod → UR-PreProd clone:

APEX application end-users — workspace developers and end-users have to be recreated manually in the target workspace. Workspace admins can do this via Workspace Administration → Manage Users.
Database links — definitions come across in a full mode, but passwords don't. Recreate with IDENTIFIED BY on the target.
Network ACLs — DBMS_NETWORK_ACL_ADMIN ACLs live in SYS-owned tables; re-apply on the target if your app makes outbound HTTP calls.
OCI credentials (DBMS_CLOUD.CREATE_CREDENTIAL) — recreate on the target; auth tokens are tied to the IAM identity, not portable inside a dump.
Scheduled jobs (DBMS_SCHEDULER) — owned-by-schema jobs DO come across via Data Pump, and start running immediately on the target. Disable any prod jobs you don't want firing on pre-prod before the import, or right after:

BEGIN
  FOR j IN (SELECT job_name FROM user_scheduler_jobs WHERE enabled='TRUE') LOOP
    DBMS_SCHEDULER.DISABLE('WKSP_URPREPROD.'||j.job_name);
  END LOOP;
END;
/

10. Verification checklist¶

Run after every clone or refresh:

SELECT job_name, state FROM user_datapump_jobs returns no rows (job complete)
SELECT COUNT(*) FROM all_objects WHERE owner='WKSP_URPREPROD' AND status='INVALID' returns 0 after COMPILE_SCHEMA
DBMS_STATS.GATHER_SCHEMA_STATS has run
Row counts on 2–3 key tables match the source (or are intentionally smaller for masked dev clones)
Login to https://<target-adb-host>/ords/<workspace> succeeds with the workspace admin
App Builder loads the imported application; spot-check a page that hits data
Scheduled jobs that shouldn't run in pre-prod are DISABLED
Database links (if any) have been recreated with credentials

When all green, the clone is done.

References¶

RB-004 — Data Pump runbook — full Data Pump mechanics (full vs schema mode, remap operations, naming convention)
infra/adb-dr.md — DR strategy and why this fits in the bigger picture
Oracle docs — APEX_INSTANCE_ADMIN package
Oracle docs — APEX_UTIL.CREATE_USER
Oracle docs — APEX_UTIL.SET_WORKSPACE
Oracle docs — Data Pump REMAP_SCHEMA
Oracle docs — DBMS_DATAPUMP.SET_PARAMETER for TABLE_EXISTS_ACTION