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

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.