# Oracle Dba Goldengate Replication

> USE THIS for ANY Oracle GoldenGate / logical-replication / materialized-view replication request - EVEN IF the user does not name a task or tool. Covers Extract / Replicat / Distribution-path / Manager status, START/STOP/RESTART, replication LAG (Integrated apply latency, capture latency, heartbeat lag), the automatic heartbeat table (ADD HEARTBEATTABLE / GG_LAG / GGS_HEARTBEAT), trail-file inventory + purge/retention, initial-load (instantiation SCN, HANDLECOLLISIONS, AFTERCSN, expdp/impdp parfiles), supplemental-logging readiness, Conflict Detection & Resolution (CDR / COMPARECOLS / RESOLVECONFLICT), logical-replication troubleshooting (missing rows, lag, ABENDED process), and materialized-view replication refresh/staleness. Trigger on symptoms too: "replication is behind / lagging", "rows not arriving on target", "Extract abended", "Replicat stopped", "OGG-xxxxx", "ggsci / adminclient", "trail files filling the disk", "heartbeat stale", "MV won't refresh", "ORA-26xxx" (LogMiner / Streams / GoldenGate apply

- Skill: `shreyas70773/oracle-dba-goldengate-replication` (Agent Skill, multi-file: 30 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-goldengate-replication`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-goldengate-replication/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-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).

| Task | Level | Tier | Mechanism | Idempotent? |
|------|-------|------|-----------|-------------|
| Process status (Extract/Replicat/path/Manager) | OBSERVE | T1 | script:scripts/process_monitor.sh\|.ps1 + sqlcl-mcp:sql/process_status.sql | yes (read-only) |
| Replication lag (host LAG + in-DB latency) | OBSERVE | T1 | script:scripts/lag_monitor.sh\|.ps1 + sqlcl-mcp:sql/replication_lag.sql | yes (read-only) |
| Heartbeat-table health + freshness | OBSERVE | T1 | script:scripts/heartbeat_monitor.sh\|.ps1 + sqlcl-mcp:sql/heartbeat_status.sql | yes (read-only) |
| Supplemental-logging readiness gate | OBSERVE | T1 | sqlcl-mcp:sql/supplemental_log_check.sql | yes (read-only) |
| CDR (conflict detect/resolve) status | OBSERVE | T1 | sqlcl-mcp:sql/cdr_status.sql | yes (read-only) |
| Initial-load readiness + instantiation SCN | OBSERVE | T1 | sqlcl-mcp:sql/initial_load_readiness.sql | yes (read-only) |
| Logical-replication troubleshooting (triage collect) | OBSERVE | T1 | script:scripts/logical_repl_healthcheck.sh\|.ps1 + sqlcl-mcp:sql/logical_repl_diagnostics.sql, sql/replication_lag.sql | yes (read-only) |
| Materialized-view replication health/staleness | OBSERVE | T1 | sqlcl-mcp:sql/mview_status.sql | yes (read-only) |
| Start/stop/restart GG processes | REVERSIBLE | T2 | script:scripts/process_control.sh\|.ps1 | yes (end-state checked; no-op if already there) |
| Trail-file purge / retention | REVERSIBLE | T2 | script:scripts/trail_management.sh\|.ps1 | yes (nothing eligible -> no-op) |
| Initial-load artifact generation (parfiles/OBEY) | REVERSIBLE | T2 | script:scripts/initial_load_config.sh\|.ps1 + sqlcl-mcp:sql/initial_load_readiness.sql | yes (same SCN+map -> no-op; new SCN backs up + regenerates) |
| Materialized-view replication refresh (on-demand) | REVERSIBLE | T2 | script:scripts/mview_refresh.sh (Linux) + sqlcl-mcp:sql/mview_refresh.sql | yes (recompute from master; re-runnable) |
| GG install/cleanup: KILL/DELETE/UNREGISTER/CLEANUP a process | DESTRUCTIVE | T3 | runbook:references/runbooks.md#gg-install | n/a (operator) |
| Initial load EXECUTION: run expdp/impdp + cutover/instantiation | DESTRUCTIVE | T3 | runbook:references/runbooks.md#initial-load | n/a (operator) |
| Trail recovery: delete still-needed trail / reposition / re-instantiate | DESTRUCTIVE | T3 | runbook:references/runbooks.md#trail-management | n/a (operator) |
| Logical-repl repair: reposition Extract, SKIPTRANSACTION, repair corrupt trail, reinstantiate | DESTRUCTIVE | T3 | runbook:references/runbooks.md#logical-repl-troubleshooting | n/a (operator) |
| Bidirectional / multi-master replication SETUP (per-direction Extract/Replicat, loop avoidance, CDR rule design) | DESTRUCTIVE | T3 | runbook:references/runbooks.md#bidirectional-multimaster | n/a (operator) |

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