# Oracle Dba Cloning Refresh Migration

> USE THIS SKILL for ANY Oracle cloning, environment refresh, or migration request — even when the user does NOT name the task or tool. Covers: RMAN DUPLICATE / database clone, PDB clone & refreshable clones (hot/remote-over-DB-link), snapshot / storage (ASM/NFS/array) clones via hot-backup mode, Data Pump schema refresh (impdp REMAP), test/dev refresh orchestration + non-prod data masking/subsetting, classic transportable tablespaces (TTS), cross-platform transportable tablespaces (XTTS) + endian conversion, Zero Downtime Migration (ZDM) to cloud, and heterogeneous (non-Oracle Gateway / HS / GoldenGate-staging) migration readiness. TRIGGER on symptoms and codes too: "clone prod to test", "refresh QA from prod", "duplicate the database", "stand up a copy", "move to OCI/cloud", "go-live cutover", "transport these tablespaces", "different endian platform", "mask PII in the copy"; and on ORA-19505 / ORA-19625 (DUPLICATE file issues), ORA-65261/65000 (refreshable PDB), ORA-39001/39002/ 39082/39083/31626/31693 (Data

- Skill: `shreyas70773/oracle-dba-cloning-refresh-migration` (Agent Skill, multi-file: 37 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-cloning-refresh-migration`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-cloning-refresh-migration/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/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.

| Task | Level | Tier | Mechanism | Idempotent? |
|------|-------|------|-----------|-------------|
| Migration source readiness report | OBSERVE | T1 | sqlcl-mcp:sql/migration_source_readiness.sql | yes |
| RMAN DUPLICATE clone — source precheck | OBSERVE | T1 | sqlcl-mcp:sql/clone_db_precheck.sql | yes |
| PDB clone status (pre/postcheck helper) | OBSERVE | T1 | sqlcl-mcp:sql/pdb_clone_status.sql | yes |
| Snapshot / storage-clone readiness | OBSERVE | T1 | sqlcl-mcp:sql/snapshot_clone_readiness.sql | yes |
| Schema-refresh (Data Pump) precheck | OBSERVE | T1 | sqlcl-mcp:sql/schema_refresh_precheck.sql | yes |
| Data Pump job status (refresh postcheck) | OBSERVE | T1 | sqlcl-mcp:sql/datapump_job_status.sql | yes |
| TTS self-containment check (same-platform) | OBSERVE | T1 | sqlcl-mcp:sql/tts_check_transport_set.sql | yes |
| XTTS / endian readiness driver | OBSERVE | T1 | script:scripts/xtts_precheck.sh (sql/xtts_transport_check.sql, sql/endian_platform_check.sql) | yes |
| Endianness / platform convert decision | OBSERVE | T1 | sqlcl-mcp:sql/endian_platform_check.sql | yes |
| Heterogeneous (non-Oracle) gateway readiness | OBSERVE | T1 | script:scripts/heterogeneous_readiness.sh (sql/heterogeneous_gateway_check.sql) | yes |
| Data-masking / subset PREVIEW (non-prod) | OBSERVE | T1 | sqlcl-mcp:sql/mask_preview.sql | yes |
| PDB clone / refreshable-clone create | REVERSIBLE | T2 | script:scripts/pdb_clone_refresh.sh --op create (sql/pdb_clone.sql) | yes (no-op if target exists) |
| PDB refresh (pull source redo) | REVERSIBLE | T2 | script:scripts/pdb_clone_refresh.sh --op refresh (sql/pdb_refresh.sql) | yes (no-op if not refreshable) |
| Schema refresh via impdp (REMAP) | REVERSIBLE | T2 | script:scripts/schema_refresh.sh (sql/schema_refresh_precheck.sql, sql/datapump_job_status.sql) | yes (no-op if job COMPLETED) |
| Test/Dev environment refresh orchestrator | REVERSIBLE | T2 | script:scripts/testdev_refresh.sh (delegates to pdb/schema + optional mask) | yes (children no-op when current) |
| Data-masking / subset APPLY (non-prod only) | REVERSIBLE | T2 | script:scripts/data_mask_subset.sh (sql/mask_preview.sql, sql/mask_apply.sql) | yes (converges; SUBSET deletes) |
| TTS: set tablespaces READ ONLY / back | REVERSIBLE | T2 | sqlcl-mcp:sql/tts_set_readonly.sql | yes (skips already-in-mode) |
| Snapshot clone orchestration (hot-backup begin/snapshot/end) | REVERSIBLE | T2 | script:scripts/snapshot_clone.sh (sql/snapshot_clone_readiness.sql) | yes (begin/end self-detect) |
| ZDM to cloud — EVALUATION (`zdmcli -eval`) | REVERSIBLE | T2 | script:scripts/zdm_precheck.sh | yes (eval creates fresh job) |
| RMAN DUPLICATE — run the clone/auxiliary DB | DESTRUCTIVE | T3 | runbook:references/runbooks.md#clone-db | n/a (runbook) |
| Snapshot clone — mount/rename/OPEN RESETLOGS as new DB | DESTRUCTIVE | T3 | runbook:references/runbooks.md#snapshot-clone | n/a (runbook) |
| TTS transport — export metadata + plug in target | DESTRUCTIVE | T3 | runbook:references/runbooks.md#tts | n/a (runbook) |
| XTTS transport + RMAN CONVERT (endian) | DESTRUCTIVE | T3 | runbook:references/runbooks.md#xtts (#endian-convert) | n/a (runbook) |
| ZDM real migration / cutover (no `-eval`) | DESTRUCTIVE | T3 | runbook:references/runbooks.md#zdm-cutover | n/a (runbook) |
| Heterogeneous migration / GoldenGate cutover | DESTRUCTIVE | T3 | runbook:references/runbooks.md#heterogeneous | n/a (runbook) |
| Drop clone PDB (rollback / teardown) | DESTRUCTIVE | T3 | runbook:references/runbooks.md#pdb-clone (sql/pdb_drop.sql) | yes (no-op if absent) |

> 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.