Oracle Dba Cloning Refresh Migration

Oracle DBA - Cloning, Refresh & Migration

Domain skill for copying, refreshing, and moving Oracle databases (19c & 23ai EE, single_instance / Data Guard, on-prem). It imports oracle-dba-common: all in-DB work runs through the SQLcl MCP saved connection dba_ai_conn (wallet-backed), secrets resolve from the Oracle Wallet only, and every script obeys the tier rules in _common/references/conventions.md. Read-only readiness/precheck automation lives here; every DESTRUCTIVE clone/transport/cutover is a print-only Tier-3 runbook.

Scope & risk map

Every task in the domain, its blast level, autonomy tier, the mechanism that performs it, and whether it is safely re-runnable. sqlcl-mcp: = SQL run via dba_ai_conn; script: = OS wrapper (.sh+.ps1 pair, dry-run default at T2); runbook: = print-only.

Masking note: scripts/testdev_refresh.sh --mask delegates to scripts/data_mask_subset.{sh,ps1} (T2), which previews via sql/mask_preview.sql (dry-run default) and applies via sql/mask_apply.sql on --execute (passing CONFIRM_APPLY=APPLY). The apply SQL is the real safety boundary: it refuses a prod-looking DB_UNIQUE_NAME unconditionally, requires a registered NONPROD marker, and commits only with CONFIRM_APPLY=APPLY. If --mask is requested but the wrapper is missing, testdev_refresh fails closed (exit 3) rather than leave an unmasked copy. You may also run the masking SQL by hand via dba_ai_conn (preview first, then apply) or follow references/runbooks.md#mask.

Preconditions

• SQLcl MCP saved connection dba_ai_conn reachable and wallet-backed. Never SYS/SYSTEM; SYSDBA is a T3 exception used only inside a runbook the operator drives.
• Secrets from the Oracle Wallet / external password store only. No plaintext anywhere: impdp connects /@<wallet_alias> (default dba_ai_conn); ZDM uses SSH keys + SEPS; the snapshot storage hook and --zdm-args are scanned and refused if they carry creds.
• Helpers sourced from _common/scripts/lib.sh / lib.ps1 (connect_db, require_dry_run, require_approval_token, precheck/postcheck, log_event, print_banner). The .sh and .ps1 of each task pair are behaviour-equivalent.
• Releases: all SQL/RMAN/Data Pump runs on 19c AND 23ai EE. 23ai is CDB-only. Per-version deltas: references/version-notes.md.
• Non-prod gate: refresh/masking REFUSES production — testdev_refresh.sh requires a non-prod --env-tag (TEST|DEV|QA|STAGE|NONPROD) plus --i-understand. mask_apply.sql enforces three independent in-DB gates: (1) an UNCONDITIONAL refusal of a %PROD%/%PRD% DB_UNIQUE_NAME that ENV_TAG can never override, (2) a recognised non-prod ENV_TAG AND a registered NONPROD marker (DATABASE_PROPERTIES ODB_NONPROD_MARKER='NONPROD'), and (3) CONFIRM_APPLY=APPLY before it COMMITs (otherwise it runs the rules and rolls back).

Procedures

Each entry: Trigger -> Precheck -> Action -> Postcheck -> Rollback. Scripts default to --dry-run (preview) at T2; pass --execute to act. Code lives in the referenced files, not here.

PDB clone / refreshable clone (T2)

• Trigger: "clone PROD PDB to TEST", stand up a non-prod PDB, set up a repeatable refreshable clone.
• Precheck: sqlcl-mcp:sql/pdb_clone_status.sql with EXPECT=ABSENT (target must not exist). Embedded in scripts/pdb_clone_refresh.sh.
• Action: scripts/pdb_clone_refresh.sh --op create --source <SRC> --target <TGT> [--db-link <L>] [--refresh-mode manual|every] [--execute] -> sql/pdb_clone.sql.
• Postcheck: sql/pdb_clone_status.sql EXPECT=OPEN (plain clone) or MOUNTED (refreshable). Idempotent: existing target -> outcome=noop.
• Rollback: drop the clone PDB — T3, human-gated: references/runbooks.md#pdb-clone (runs sql/pdb_drop.sql, INCLUDING DATAFILES).

PDB refresh (T2)

• Trigger: "refresh the TEST clone from PROD", scheduled non-prod refresh.
• Precheck: sql/pdb_clone_status.sql EXPECT=REFRESHABLE.
• Action: scripts/pdb_clone_refresh.sh --op refresh --target <TGT> [--execute] -> sql/pdb_refresh.sql (close -> REFRESH -> leave MOUNTED).
• Postcheck: same status helper. Refresh of a current clone applies zero redo (no-op).
• Rollback: drop & re-create the clone — references/runbooks.md#pdb-clone.

Schema refresh via Data Pump (T2)

• Trigger: "refresh HR/SALES in TEST from the prod export", impdp REMAP load.
• Precheck: sql/schema_refresh_precheck.sql (DIRECTORY exists; reports target footprint).
• Action: scripts/schema_refresh.sh --directory <DIR> --dumpfile <D> --schemas <CSV> [--remap-schema S:T] [--remap-tablespace S:T] [--execute] (default TABLE_EXISTS_ACTION=REPLACE; impdp /@<wallet_alias>).
• Postcheck: sql/datapump_job_status.sql (job COMPLETED / terminal). Re-run of a COMPLETED job name -> outcome=noop.
• Rollback: re-import from a prior dump, or re-clone the environment; impdp itself has no undo — the dump set IS the source of truth.

Test/Dev environment refresh orchestrator (T2)

• Trigger: one-command non-prod refresh ("refresh QA from prod and mask it").
• Precheck: non-prod --env-tag + --i-understand gate; children run their own pre/postchecks.
• Action: scripts/testdev_refresh.sh --strategy pdb|schema --env-tag <TAG> --i-understand [--mask <RULESET>] [--execute] -> delegates to pdb_clone_refresh.sh or schema_refresh.sh, then optional masking.
• Postcheck: aggregate child exit codes; per-child postchecks.
• Rollback: re-run the refresh (idempotent) or re-clone from source.

Data masking / subsetting (T1 preview -> T2 apply, non-prod only)

• Trigger: de-identify PII / shrink data in a refreshed non-prod copy.
• Precheck/Preview: sqlcl-mcp:sql/mask_preview.sql (RULESET, ENV_TAG) — reports the plan and the non-prod guard evaluation; changes nothing.
• Action: scripts/data_mask_subset.sh --ruleset <R> --env-tag <TAG> [--execute] (T2, dry-run default) -> sql/mask_apply.sql (driven from ODB_MASK_RULES). The apply SQL unconditionally refuses a %PROD%/%PRD% DB, requires a registered NONPROD marker, and commits only on CONFIRM_APPLY=APPLY. Reached via testdev_refresh.sh --mask or references/runbooks.md#mask.
• Postcheck: re-run mask_preview.sql (converged rules show ~0 matching rows).
• Rollback: masking is deliberately one-way (PII must not be recoverable) — recover by re-refreshing the non-prod environment from source.

Snapshot / storage-based clone (T2 orchestration; T3 mount)

• Trigger: ASM/NFS/array snapshot of an OPEN database for a fast clone.
• Precheck: sql/snapshot_clone_readiness.sql (datafiles snapshot-safe).
• Action: scripts/snapshot_clone.sh --action provision --snap-cmd "<site hook>" [--execute] — BEGIN BACKUP -> site storage hook -> END BACKUP (END always runs, even on hook failure). begin/end/snapshot actions available individually.
• Postcheck: hot-backup state correct after the action (none ACTIVE after end/provision).
• Rollback: --action end clears hot-backup mode; mounting the snapshot as a NEW DB (rename, OPEN RESETLOGS) is T3 — references/runbooks.md#snapshot-clone.

Transportable tablespaces — same platform (T1 check, T2 read-only)

• Trigger: transport a tablespace set to another DB on the same platform/endian.
• Precheck: sqlcl-mcp:sql/tts_check_transport_set.sql (DBMS_TTS.TRANSPORT_SET_CHECK; exit 3 if not self-contained).
• Action: sqlcl-mcp:sql/tts_set_readonly.sql MODE=READONLY (export window), then the T3 transport runbook copies datafiles + plugs in the target.
• Postcheck: confirm tablespace modes; the runbook verifies the plug-in.
• Rollback: sql/tts_set_readonly.sql MODE=READWRITE restores access — always run in the cleanup path. Full transport: references/runbooks.md#tts.

Cross-platform transportable tablespaces (XTTS) + endian (T1 readiness)

• Trigger: transport tablespaces to a DIFFERENT platform; "different endian".
• Precheck/Action: scripts/xtts_precheck.sh --tablespaces <CSV> --target-platform "<exact platform_name>" -> sql/xtts_transport_check.sql (self-containment, exit 3 on violation) + sql/endian_platform_check.sql (CONVERT decision). Read-only (T1).
• Postcheck: PASS/FAIL summary; operator reads the convert decision.
• Rollback: none needed (read-only). The transport + RMAN CONVERT itself is T3: references/runbooks.md#xtts (and #endian-convert when endians differ).

RMAN DUPLICATE database clone (T1 precheck; T3 duplicate)

• Trigger: "duplicate / clone the whole database to an auxiliary instance".
• Precheck: sqlcl-mcp:sql/clone_db_precheck.sql — ARCHIVELOG gate (exit 3), identity, datafile footprint, last backup, PDB scope.
• Action: the DUPLICATE (creates/overwrites the auxiliary DB) is T3 — never here: references/runbooks.md#clone-db.
• Postcheck/Rollback: per the runbook (auxiliary DB is disposable; drop & re-DUPLICATE).

ZDM migration to cloud (T2 eval; T3 cutover)

• Trigger: "migrate to OCI / cloud", "plan the move", pre-cutover validation.
• Precheck: response file readable; --zdm-args refused if it contains creds or a non-eval migrate verb.
• Action: scripts/zdm_precheck.sh --zdm-home <H> --rsp <RSP> --source-node <S> --target-node <T> [--execute] — runs zdmcli migrate database ... -eval ONLY (non-destructive); or --query-job <ID> to observe.
• Postcheck: eval output reports no FAIL/ERROR phases.
• Rollback: none (eval moves no data). Real cutover is T3: references/runbooks.md#zdm-cutover.

Heterogeneous (non-Oracle) migration readiness (T1)

• Trigger: migrate from/to PostgreSQL, SQL Server, MySQL, DB2 via Gateway/HS/GoldenGate.
• Precheck/Action: scripts/heterogeneous_readiness.sh --dblink <NAME> [--gateway-init <PATH>] [--tns-admin <DIR>] -> sql/heterogeneous_gateway_check.sql (DB link exists + live probe; exit 3 if not). Read-only.
• Postcheck: PASS/FAIL summary.
• Rollback: none (read-only). Data movement / GoldenGate cutover is T3: references/runbooks.md#heterogeneous.

Migration source readiness snapshot (T1)

• Trigger: any migration planning ("is this DB ready to move?").
• Action: sqlcl-mcp:sql/migration_source_readiness.sql — version/charset/COMPATIBLE, size, TDE, redo rate, invalid objects, supplemental logging, blocking datatypes. Read-only, never aborts. Feeds the ZDM / XTTS / heterogeneous / endian runbooks.

Tier-3 runbooks

See references/runbooks.md. These PRINT exact commands for a human operator and NEVER self-execute — they are gated by require_approval_token (real change ticket; placeholders rejected; token value never logged). SYSDBA, when needed, is supplied interactively inside the runbook only. Anchors map 1:1 to the risk-map rows:

• #clone-db — RMAN DUPLICATE (FROM ACTIVE / backup-based) to an auxiliary instance.
• #snapshot-clone — mount/rename a storage snapshot, recover, OPEN RESETLOGS as a new DB.
• #tts — classic transportable tablespaces: metadata export + plug-in.
• #xtts / #endian-convert — cross-platform transport + RMAN CONVERT of every datafile.
• #zdm-cutover — the real zdmcli migrate database (no -eval): switchover/cutover.
• #heterogeneous — non-Oracle data movement / GoldenGate extract-replicat + cutover.
• #pdb-clone — DROP a clone PDB (rollback/teardown; sql/pdb_drop.sql, INCLUDING DATAFILES).
• #mask — register the NONPROD marker and run the non-prod masking/subsetting plan by hand (sql/mask_preview.sql -> sql/mask_apply.sql with CONFIRM_APPLY=APPLY) when not using the scripts/data_mask_subset.{sh,ps1} wrapper.