# Oracle Dba Data Guard Disaster Recovery

> USE THIS SKILL for ANY Oracle Data Guard / standby / disaster-recovery / HA request - EVEN IF the user does not name the task. Triggers: physical/standby database, redo transport & apply, apply/transport LAG, archive/redo GAP, switchover, FAILOVER, reinstate failed primary, standby build/rebuild (RMAN active duplicate), Active Data Guard / real-time query / DML redirect, Fast-Start Failover (FSFO) + observer, snapshot standby, cascaded standby, Data Guard broker (dgmgrl), protection modes (Max Protection/Availability/ Performance), Far Sync, role-based services. Symptoms: "standby is behind", "apply stopped", "MRP not running", "redo not shipping", "lost the primary", "DR site", "promote standby". Error codes: ORA-16xxx broker/DG family (ORA-16700/16766/16778/16789/16809/16810/16664/16661 reinstate, ORA-16143/16131), ORA-01153, ORA-19909 (standby_became_primary), ORA-00752, RMAN-05xxx during DUPLICATE ... FOR STANDBY, CRS-xxxx when srvctl relocates DG services. Follows oracle-dba-common contracts: in-DB work

- Skill: `shreyas70773/oracle-dba-data-guard-disaster-recovery` (Agent Skill, multi-file: 27 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-data-guard-disaster-recovery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-data-guard-disaster-recovery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Productivity
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-data-guard-disaster-recovery

---

# Oracle DBA - Data Guard / Disaster Recovery

Physical-standby Data Guard for Oracle **19c and 23ai**, Enterprise Edition,
single-instance and Data Guard, on-prem. In-DB SQL runs through the SQLcl MCP
server on the saved connection **`dba_ai_conn`** (wallet). Host tools
(`dgmgrl`, `rman`, `srvctl`, `sqlplus`) cannot go through the SQLcl MCP server
(it is in-DB only) - they run from a shell via a script or a runbook, and would
later front a guarded `oracle-dba-ops` MCP.

## Scope & risk map

Every task in this domain (both authoring halves), with its self-execute tier
and the exact mechanism. T1 = read-only/observe (self-executes freely);
T2 = reversible (self-executes only with `--dry-run` default + explicit
`--execute`); T3 = destructive / role-change / structural (runbook-only +
`require_approval_token`, NEVER self-executes).

| Task | Level | Tier | Mechanism (path) | Idempotent? |
|------|-------|------|------------------|-------------|
| Data Guard status / health snapshot | L1 | T1 | script:`scripts/dg_status.sh` / `scripts/dg_status.ps1` (db\|broker\|all) + sqlcl-mcp:`sql/dg_status.sql` | yes (read-only) |
| Apply + transport lag report | L1 | T1 | sqlcl-mcp:`sql/dg_lag_monitor.sql`; tighter pre-role-change: `sql/dg_apply_lag.sql` | yes (read-only) |
| Standby Redo Apply (MRP) status | L1 | T1 | sqlcl-mcp:`sql/standby_apply_status.sql` | yes (read-only) |
| DG readiness PASS/FAIL inventory | L1 | T1 | sqlcl-mcp:`sql/dg_config_check.sql` | yes (read-only) |
| Archive / redo gap detection | L1 | T1 | sqlcl-mcp:`sql/archive_gap_detect.sql`; wrapper: `scripts/archive_gap_resolution.{sh,ps1} detect` | yes (read-only) |
| Archive / redo gap nudge (restart apply -> FAL refetch) | L2 | T2 | script:`scripts/archive_gap_resolution.{sh,ps1} nudge` | yes (no gap -> noop) |
| Standby redo log (SRL) assessment | L1 | T1 | sqlcl-mcp:`sql/standby_redo_assess.sql`; wrapper: `scripts/standby_redo_management.{sh,ps1} assess` | yes (read-only) |
| Add a standby redo log group | L2 | T2 | script:`scripts/standby_redo_management.{sh,ps1} add`; in-DB DDL: `sql/standby_redo_add.sql` | yes (group exists -> noop) |
| DG broker - show configuration | L1 | T1 | script:`scripts/dg_broker_config.{sh,ps1} show` | yes (read-only) |
| DG broker - set (safe) property / enable config | L2 | T2 | script:`scripts/dg_broker_config.{sh,ps1} set\|enable` (refuses FSFO/protection props -> runbook) | yes (already-set -> noop) |
| FSFO observer - status | L1 | T1 | script:`scripts/dg_observer.{sh,ps1} status` | yes (read-only) |
| FSFO observer - start / stop | L2 | T2 | script:`scripts/dg_observer.{sh,ps1} start\|stop` | yes (already running/stopped -> noop) |
| Active Data Guard - open-mode toggle | L2 | T2 | script:`scripts/active_data_guard.{sh,ps1} enable\|disable`; in-DB: `sql/active_dg_enable.sql` / `sql/active_dg_disable.sql` | yes (already-in-state -> noop) |
| Data Guard setup / configuration | L3 | T3 | runbook:`references/runbooks.md#dg-setup` (T1 assess via `sql/dg_config_check.sql`, `sql/dg_status.sql`) | n/a (human-gated; setup is additive/reversible) |
| Failover operation (+ switchover branch) | L3 | T3 | runbook:`references/runbooks.md#failover` (pre-loss assess: `sql/dg_apply_lag.sql`) | n/a (one-way role change) |
| Reinstate failed primary | L3 | T3 | runbook:`references/runbooks.md#reinstate` (verify: `sql/dg_status.sql`) | n/a (recovery op) |
| Standby database build / rebuild | L3 | T3 | runbook:`references/runbooks.md#standby-build` (prereq: `sql/dg_config_check.sql`) | n/a (structural; overwrites on rebuild) |
| Active Data Guard - architecture (Far Sync, cascade, DML redirect, licensing) | L3 | T3 | runbook:`references/runbooks.md#active-dg` (toggle handled by the T2 script above) | n/a (architecture/licensing) |
| Fast-Start Failover (FSFO) configuration | L3 | T3 | runbook:`references/runbooks.md#fsfo` (observer start/stop is the T2 `dg_observer` script) | n/a (HA automation config) |
| Snapshot standby conversion | L3 | T3 | runbook:`references/runbooks.md#snapshot-standby` | n/a (conversion is its own reverse) |
| Cascaded standby setup | L3 | T3 | runbook:`references/runbooks.md#cascaded-standby` | n/a (multi-tier topology) |

> **Routing note for the T2 helper scripts.** `archive_gap_resolution`,
> `dg_broker_config`, and `dg_observer` deliberately STOP and route to a runbook
> when a request crosses into T3. Manual RMAN restore/catalog of missing archived
> logs or an incremental-SCN roll-forward escalates to `#standby-build` /
> `#reinstate`. A broker property that alters failover/protection posture
> (`FastStartFailover*`, `*Protection*`) or `ENABLE FAST_START FAILOVER`
> escalates to `#fsfo`. Switchover/failover escalate to `#failover`. These
> scripts refuse those operations rather than self-executing them.

## Preconditions
- **SQLcl MCP connection `dba_ai_conn`** reachable and wallet-backed. It is NOT
  SYS/SYSTEM; the in-DB DDL paths (`standby_redo_add.sql`, the Active DG toggle,
  the gap nudge) require the `dba_ai_conn` user to hold `ALTER DATABASE`, granted
  out of band per site policy. SYSDBA is used ONLY inside a T3 runbook, supplied
  interactively by the operator - never the default, never auto.
- **Secrets resolve from the Oracle Wallet / external password store** only. No
  plaintext in argv, env, or files. `dgmgrl`/`rman` connect with `/` (OS auth) or
  a wallet-backed alias; every host script refuses a `user/pass@db` or
  `password=` connect identifier (`EX_SECRET`=8). The redo-transport (SYS)
  password is propagated by copying the password file, never by typing it.
- **Helpers** sourced from `_common/scripts/lib.sh` / `lib.ps1`
  (`connect_db`/`Connect-Db`, `log_event`, `emit_metric`, `require_dry_run`,
  `require_approval_token`, `precheck`/`postcheck`, `print_banner`). Scripts do
  not reinvent them.
- **Canonical logs**: Linux `/var/log/oracle-dba/data-guard/data-guard.log`;
  Windows `C:/ProgramData/oracle-dba/logs/data-guard/data-guard.log`.
- **Run the read-only assessment first.** Before ANY T2/T3 action, capture role,
  open_mode, protection mode, apply/transport lag, and gaps with
  `scripts/dg_status.{sh,ps1} all` + `sql/dg_config_check.sql`. Recovering or
  transitioning the wrong database is the classic Data Guard catastrophe.
- **Version**: works on 19c and 23ai (23ai is CDB-only; DG operates at the CDB
  level, PDBs follow the CDB role). Deltas in `references/version-notes.md`.

## Procedures

Each procedure: **Trigger -> Precheck -> Action -> Postcheck -> Rollback.**
Read-only assessment (`dg_status`, the lag/apply/gap/SRL/config-check SQL) has no
precheck/rollback beyond "it changes nothing" and is run freely; the entries
below cover the acting (T2) and gated (T3) tasks.

### T1 - observe (self-execute freely, read-only)
- **DG status / lag / apply / gaps / SRL / readiness.** Trigger: any "how is DR /
  the standby doing" question, or as the mandatory first step before a change.
  Action: `scripts/dg_status.{sh,ps1} [db|broker|all]`, and via `dba_ai_conn`
  `sql/dg_status.sql`, `sql/dg_lag_monitor.sql`, `sql/dg_apply_lag.sql`,
  `sql/standby_apply_status.sql`, `sql/archive_gap_detect.sql`,
  `sql/standby_redo_assess.sql`, `sql/dg_config_check.sql`. Postcheck: n/a (these
  ARE the check). Rollback: none (no changes made).

### T2 - reversible (dry-run default; `--execute` to act; idempotent)
- **Archive/redo gap nudge.** Trigger: `archive_gap_detect` shows a gap on a
  standby and you want to prod automatic FAL re-fetch without destructive
  recovery. Precheck: must be a PHYSICAL STANDBY; `gap_count>0` (else noop).
  Action: `scripts/archive_gap_resolution.{sh,ps1} nudge --execute` (cancels then
  restarts Redo Apply with real-time apply). Postcheck: MRP running again.
  Rollback: apply simply continues; nothing destroyed. If the gap persists,
  escalate to `references/runbooks.md#standby-build` (manual restore/roll-forward).
- **Add a standby redo log group.** Trigger: `standby_redo_assess` /
  `dg_config_check` flags too few or wrong-sized SRLs. Precheck: group not already
  present (else noop). Action: `scripts/standby_redo_management.{sh,ps1} add
  --group N --thread T --bytes B --member PATH --execute` (in-DB DDL in
  `sql/standby_redo_add.sql`). Postcheck: group present and sized. Rollback: a
  quiescent SRL can be dropped (`ALTER DATABASE DROP STANDBY LOGFILE GROUP N`).
- **DG broker set / enable.** Trigger: adjust a safe broker property
  (e.g. `LogXptMode`, `DelayMins`) or `ENABLE` the configuration/a member.
  Precheck: property already at desired value / config already SUCCESS -> noop;
  FSFO/protection properties are refused (-> `#fsfo`). Action:
  `scripts/dg_broker_config.{sh,ps1} set --property .. --value .. --member .. --execute`
  or `... enable [--member ..] --execute`. Postcheck: property reads back /
  status SUCCESS. Rollback: re-`EDIT ... SET PROPERTY` to the prior value;
  `DISABLE` the member/config.
- **FSFO observer start / stop.** Trigger: bring up / take down the observer
  process (NOT enabling FSFO). Precheck: observer already running for this config
  / already stopped -> noop; refuses to enable FSFO. Action:
  `scripts/dg_observer.{sh,ps1} start --config-file PATH [--name N] --execute` /
  `... stop [--name N] --execute`. Postcheck: `SHOW OBSERVER` state matches.
  Rollback: `stop` reverses `start` (observer changes nothing in the databases).
- **Active Data Guard open-mode toggle.** Trigger: open a healthy physical standby
  READ ONLY WITH APPLY (real-time query) or revert it to MOUNTED + managed
  recovery. Precheck: role = PHYSICAL STANDBY; already in target open_mode -> noop.
  Action: `scripts/active_data_guard.{sh,ps1} enable --execute` /
  `... disable --execute` (in-DB: `sql/active_dg_enable.sql` /
  `sql/active_dg_disable.sql`). Postcheck: `open_mode` is the intended state.
  Rollback: `disable` reverses `enable` and vice-versa (fully reversible).
  Licensing/Far Sync/cascade/DML-redirect architecture is T3 -> `#active-dg`.

### T3 - destructive / role-change / structural (runbook-only; never self-executes)
For each: Trigger -> Precheck (read-only readiness report) -> Action (PRINT the
runbook, obtain approval token, operator runs the commands by hand) -> Postcheck
(verify in the runbook) -> Rollback (in the runbook). Precheck for ALL of these is
`scripts/dg_status.{sh,ps1} all` + `sql/dg_config_check.sql` on both sites.

- **Data Guard setup** -> `references/runbooks.md#dg-setup`. Rollback: additive;
  `DISABLE`/`REMOVE CONFIGURATION` + revert `log_archive_*` params.
- **Failover (and switchover decision branch)** -> `references/runbooks.md#failover`.
  Rollback: no in-place undo after FAILOVER - reinstate or rebuild the old primary;
  before issuing FAILOVER, prefer a reversible switchover if the primary is alive.
- **Reinstate failed primary** -> `references/runbooks.md#reinstate`. Rollback: if
  flashback can't reach `standby_became_primary_scn`, abandon and rebuild.
- **Standby build / rebuild** -> `references/runbooks.md#standby-build`. Rollback:
  new build doesn't touch the primary; rebuild intentionally overwrites the target.
- **Active Data Guard architecture** -> `references/runbooks.md#active-dg`.
  Rollback: the open-mode part is the reversible T2 script above; remove Far Sync /
  reset RedoRoutes.
- **Fast-Start Failover (FSFO)** -> `references/runbooks.md#fsfo`. Rollback:
  `DISABLE FAST_START FAILOVER` + `STOP OBSERVER`; revert protection mode.
- **Snapshot standby conversion** -> `references/runbooks.md#snapshot-standby`.
  Rollback: the convert-back IS the reverse (discards interim RW changes via the
  auto guaranteed restore point - do not drop it).
- **Cascaded standby** -> `references/runbooks.md#cascaded-standby`. Rollback:
  `DEFER`/clear the forwarding dest (or remove cascade RedoRoutes); narrow
  `DG_CONFIG`; drop tier-2 files.

## Tier-3 runbooks
See **`references/runbooks.md`**. Every section there is TIER 3: it PRINTS exact
commands for a human operator (both broker `dgmgrl` and manual `ALTER DATABASE`
paths, for 19c and 23ai) and **NEVER self-executes**. Each is gated by
`require_approval_token` (a real change ticket; placeholders rejected,
`EX_APPROVAL`=6) and carries a pre-flight checklist, diagnostics, explicit
verification, and a rollback/abort path. An automated agent's only sanctioned
action there is to print the relevant section and stop until a human supplies a
valid token. When in doubt, runbook.