Oracle Dba Goldengate Replication

Oracle DBA - GoldenGate / Replication

In-scope replication mechanisms: Oracle GoldenGate (Integrated Extract / Integrated Replicat / Distribution path, Classic ggsci or Microservices adminclient), logical replication / Logical Standby signals, and materialized-view replication (MVs over DB links, scheduled or on-demand refresh). Oracle DB 19c & 23ai, EE, single_instance / Data Guard, on-prem.

Host-side GoldenGate commands (ggsci / adminclient: INFO ALL, LAG, STATS, START, STOP, PURGEOLDEXTRACTS) CANNOT go through the SQLcl MCP server (restrict level 4 = no host commands). They run from the scripts/* host wrappers, which authenticate to GoldenGate via the credential store (USERIDALIAS / wallet) - never a password on the CLI. All in-database work goes through the wallet-backed SQLcl saved connection dba_ai_conn.

Scope & risk map

Mechanism legend: sqlcl-mcp:sql/... = in-DB query via dba_ai_conn; script:scripts/... = host wrapper (ggsci/adminclient + OS files); runbook:references/runbooks.md#... = print-only operator runbook (T3).

mview_refresh ships a Linux .sh only; on Windows run sql/mview_refresh.sql via the wrapper contract (the SQL is the OS-agnostic EXECUTE payload). Treat a missing .ps1 pair as "Linux-only host wrapper" for this task.

Preconditions

• SQLcl MCP connection dba_ai_conn reachable (wallet-backed). Least privilege; never SYS/SYSTEM unless a T3 runbook explicitly requires SYSDBA (operator-supplied, interactive, never default).
• GoldenGate credential store (USERIDALIAS / wallet) configured in the GG home for ggsci / adminclient. Never a password on the CLI.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere (expdp/impdp parfiles reference /@<tns_alias> only).
• Helpers sourced from _common/scripts/lib.sh / lib.ps1 (connect_db, log_event, require_dry_run, require_approval_token, precheck, postcheck, print_banner). The .sh / .ps1 of a task pair are behaviour-equivalent.
• GoldenGate mode (Classic ggsci vs Microservices adminclient) is auto-detected by the host wrappers; override with --mode / --deployment.

Procedures

Each procedure: Trigger -> Precheck -> Action -> Postcheck -> Rollback. Action links reference files by path; see the file for full flags/usage.

T1 - Observation (self-execute freely; safe to schedule)

Process status

• Trigger: "is replication running?", "Extract abended", "Replicat stopped", process inventory, scheduled health.
• Precheck: none (read-only); wrapper checks GG home + dba_ai_conn.
• Action: scripts/process_monitor.sh / .ps1 (ggsci/adminclient INFO ALL
  • per-group INFO/STATS) cross-checked by sql/process_status.sql (DBA_CAPTURE / DBA_APPLY / V$GOLDENGATE_*). --no-db host-only, --db-only in-DB only. Emits abended/stopped counts as metrics.
• Postcheck: same report; non-zero abended/stopped count -> escalate.
• Rollback note: none (read-only). To restart a stopped process use the T2 process-control procedure below.

Replication lag

• Trigger: "replication is behind / lagging", lag alert, SLA check.
• Precheck: none (read-only).
• Action: scripts/lag_monitor.sh / .ps1 (LAG EXTRACT/REPLICAT * + INFO ALL at-checkpoint lag) + sql/replication_lag.sql (in-DB GG_LAG heartbeat lag, Integrated apply/capture latency). --warn/--crit thresholds (default 60s/300s). Always exits 0; severity travels in the metric.
• Postcheck: re-run; compare worst lag to thresholds.
• Rollback note: none. Sustained lag -> logical-repl troubleshooting (T1 triage) then #logical-repl-troubleshooting (T3) if a repair is needed.

Heartbeat-table health + freshness

• Trigger: "heartbeat stale", stall early-warning, verify ADD HEARTBEATTABLE.
• Precheck: none (read-only).
• Action: scripts/heartbeat_monitor.sh / .ps1 -> sql/heartbeat_status.sql (heartbeat INSTALLED?, seed-row freshness, per-path in/out lag). --stale SEC (default 120) drives severity. No ggsci call - freshness is wholly in-DB.
• Postcheck: re-run; freshness within --stale.
• Rollback note: none.

Supplemental-logging readiness gate

• Trigger: before adding capture / enabling CDR; "ORA-26xxx", "why is a column missing on target?". Also used as precheck/postcheck by other tasks.
• Precheck: this IS the precheck SQL.
• Action: sql/supplemental_log_check.sql via dba_ai_conn (DB- and table-level supplemental logging for key / before-image columns).
• Postcheck: re-run after a runbook enables logging; KEYVAL must show ready.
• Rollback note: none (read-only). Enabling logging is done in a runbook.

CDR status

• Trigger: bidirectional/multi-master, "ORA-00001 conflict on target", "ORA-01403 no data found", COMPARECOLS / RESOLVECONFLICT review.
• Precheck: none (read-only).
• Action: sql/cdr_status.sql via dba_ai_conn (in-DB CDR signals; resolution rules themselves live in the Replicat parameter file on the host).
• Postcheck: re-run after conflict-rule change.
• Rollback note: none.

Initial-load readiness + instantiation SCN

• Trigger: planning an instantiation / re-sync; "what SCN do I use for AFTERCSN/HANDLECOLLISIONS?".
• Precheck: this IS the readiness SQL.
• Action: sql/initial_load_readiness.sql via dba_ai_conn (current SCN, source readiness, supplemental-log confirmation).
• Postcheck: re-run to re-pin SCN before generating artifacts.
• Rollback note: none. Drives the T2 initial-load-config procedure.

Logical-replication troubleshooting (triage collect)

• Trigger: "rows not arriving", "replication broken / lagging", ABENDED process triage, "OGG-xxxxx" / "ORA-26xxx" investigation.
• Precheck: none (read-only).
• Action: scripts/logical_repl_healthcheck.sh / .ps1 runs sql/logical_repl_diagnostics.sql + sql/replication_lag.sql via dba_ai_conn, optionally a READ-ONLY admin-client OBEY (INFO ALL/LAG/STATS/ DETAIL), and bundles a timestamped triage report. --max-lag-secs N fails (exit 7) above N.
• Postcheck: review the triage bundle.
• Rollback note: none. Actual FIXES (reposition Extract, SKIPTRANSACTION, reinstantiate, repair corrupt trail) are T3 -> references/runbooks.md#logical-repl-troubleshooting.

Materialized-view replication health/staleness

• Trigger: "MV is stale", "MV won't refresh", refresh-outcome review.
• Precheck: none (read-only).
• Action: sql/mview_status.sql via dba_ai_conn (freshness/staleness, last-refresh outcome, refresh mode).
• Postcheck: re-run after a refresh.
• Rollback note: none. To refresh, see the T2 mview-refresh procedure.

T2 - Reversible (dry-run is DEFAULT; --execute to act)

All T2 scripts: with no flags they PREVIEW and change nothing. Add --execute to act. Each runs precheck -> act -> postcheck and logs outcome=.

Start/stop/restart GG processes

• Trigger: restart an abended/stopped Extract or Replicat; planned start/stop of a group or ER * / RE *.
• Precheck: read current state; if already in the requested state -> no-op (exit 0). KILL/DELETE/UNREGISTER/CLEANUP are NOT supported (T3).
• Action: scripts/process_control.sh / .ps1 start|stop|restart --group NAME (or --all-extracts / --all-replicats), --force-stop for non-graceful stop. --execute required.
• Postcheck: verify the process reached the requested run state.
• Rollback note: START and STOP are mutual inverses - re-run with the opposite subcommand. No definition/trail is removed.

Trail-file purge / retention

• Trigger: "trail files filling the disk", scheduled retention.
• Precheck: inventory trail dir; compute eligible = older than --retention-hours AND seqno < --min-seq (min still needed downstream, from INFO ... DETAIL). No --min-seq -> age-only with doubled margin. Nothing eligible -> no-op.
• Action: scripts/trail_management.sh / .ps1 --trail-dir PATH --prefix XX [--retention-hours N] [--min-seq N] [--keep-min N]. --execute required. Refuses to delete a still-needed trail.
• Postcheck: re-inventory; only consumed+aged files removed; --keep-min honored.
• Rollback note: deleted trails are already-consumed (applied downstream), so reversible operationally. Deleting a STILL-NEEDED trail forces re-sync -> that recovery is T3 references/runbooks.md#trail-management.

Initial-load artifact generation

• Trigger: prepare a Data-Pump-based instantiation (parfiles + OBEY).
• Precheck: sql/initial_load_readiness.sql captures the instantiation SCN
  • source readiness. Same SCN + map as existing artifacts -> no-op.
• Action: scripts/initial_load_config.sh / .ps1 GENERATES (does NOT run) the initial-load Replicat parfile (HANDLECOLLISIONS / AFTERCSN guidance), an admin-client OBEY (ADD/START in order), and expdp/impdp parfiles pinned to the SCN, under a config dir. New SCN -> backs up prior set + regenerates. --execute required to write.
• Postcheck: artifacts exist for the pinned SCN; operator reviews them.
• Rollback note: regeneration backs up the prior artifact set (reversible). RUNNING expdp/impdp + cutover is T3 references/runbooks.md#initial-load.

Materialized-view replication refresh (on-demand)

• Trigger: refresh a stale MV after mview_status shows staleness.
• Precheck: wrapper enforces dry-run-default / --execute; confirms intent before invoking the SQL payload.
• Action: scripts/mview_refresh.sh (Linux) drives sql/mview_refresh.sql via dba_ai_conn (the EXECUTE-path payload; on Windows run the SQL directly under the same contract).
• Postcheck: sql/mview_status.sql shows fresh + a successful last refresh.
• Rollback note: a refresh recomputes the MV from its master(s) - re-runnable / idempotent; no master data is changed.

Tier-3 runbooks

See references/runbooks.md. These PRINT exact commands for a human operator and NEVER self-execute. Gated by require_approval_token (real change ticket via --token <TICKET> / ODB_APPROVAL_TOKEN; placeholders rejected; token value never logged). SYSDBA, where needed, is operator-supplied inside the runbook only.

• #gg-install - KILL / DELETE / UNREGISTER / CLEANUP a GoldenGate process (structural/destructive teardown).
• #initial-load - execute the instantiation: run expdp/impdp and perform the cutover (one-way destructive against the target).
• #trail-management - recovery when a still-needed trail was lost / a process must be repositioned / re-instantiated.
• #logical-repl-troubleshooting - repair actions: reposition Extract, SKIPTRANSACTION, repair a corrupt trail, reinstantiate a table/schema.
• #bidirectional-multimaster - stand up bidirectional / multi-master (active-active) replication: per-direction Extract/Replicat, loop suppression (TRANLOGOPTIONS EXCLUDETAG + EXCLUDEUSER/EXCLUDEUSERID), and Conflict Detection & Resolution rule wiring (COMPARECOLS / RESOLVECONFLICT). Pairs with the T1 #cdr-status query and the bidirectional sub-section of #logical-repl-troubleshooting.

See references/version-notes.md for 19c vs 23ai deltas relevant to this domain.