Vendor Nuances — Oracle (Log-Based CDC)

Quick Health Checks (copy/paste)

Archivelog & Redo Status

SELECT log_mode FROM v$database;
SELECT sequence#, bytes/1024/1024 MB, archived, status
FROM v$log ORDER BY first_time DESC FETCH FIRST 10 ROWS ONLY;

Supplemental Logging

-- database-wide
SELECT supplemental_log_data_min, supplemental_log_data_all FROM v$database;

-- table-level (confirm keys/columns logged for merges on keyless tables)
SELECT owner, table_name, log_group_name, always, log_group_type
FROM dba_log_groups
ORDER BY owner, table_name;

Current Redo Pressure

SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24:MI') AS first_time,
       COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1/24   -- last hour
GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24:MI')
ORDER BY 1 DESC;

Connector Snapshot Sanity

• Scope restricted: include only necessary schemas/tables.
• Long-running transactions identified (they can stall snapshot start).
• Redo retention ≥ expected snapshot duration.
• If tables lack PKs, ensure table-level log groups capture enough columns for deterministic merges.

Nice-to-have SQL for big snapshots

-- largest tables by size
SELECT owner, segment_name, bytes/1024/1024 MB
FROM dba_segments
WHERE segment_type='TABLE'
ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;

Common Failure → First Aid

• Duplicates after restarts: switch the sink to idempotent MERGE/UPSERT keyed by a stable id + version/op_ts; then replay last N events.
• Missed updates: enable table-level supplemental logging for key columns (or all columns for keyless) and re-snapshot.
• Dictionary mismatch / DDL errors: pause, capture the DDL, align connector schema evolution settings, then resume from a safe SCN.
• Redo switch storm: reduce snapshot scope or increase redo log size; avoid concurrent heavy maintenance jobs.
• SCN gaps / “log not found”: extend archive retention; if a gap exists, plan a clean re-snapshot of affected tables.

Observability

• Expose SCN / commit timestamp per event (headers/columns) all the way to the sink.
• Emit connector task metrics (lag, batches/sec, records/sec) and alert on flatlining progress.
• Dead-letter queue (DLQ) enabled with payload + error string for triage.

Duplicate / Latest-wins Checks (sink)

-- generic duplicate check (replace target & pk)
SELECT COUNT(*) AS rows, COUNT(DISTINCT pk) AS distinct_keys FROM target;

-- latest-wins (replace names)
SELECT business_id, MAX(op_ts) AS last_seen, COUNT(*) AS events
FROM history_or_stage GROUP BY business_id;