faucet-source-oracle-cdc

Oracle Database change data capture for the faucet-stream ecosystem, built on LogMiner (DBMS_LOGMNR) over the online and archived redo logs. Emits every committed insert, update and delete on the captured tables as a change envelope, resumable by SCN with no gap and no duplicate.

Runtime requirement: Oracle Instant Client must be installed where ODPI-C can load it — see faucet-common-oracle.

Highlights

• Transactions emitted on commit only — changes are buffered per transaction and released when its COMMIT is mined; rolled-back work and savepoint-rolled-back statements never appear. Each committed transaction is one page with its own bookmark.
• SCN bookmarks — resume reaches back to the start of any transaction still open at the bookmark, so long transactions spanning log switches are captured whole.
• Exactly-once — supports_exactly_once() = true; pair with an idempotent sink.
• Snapshot handoff — capture_resume_position() returns the current SCN for faucet mirror.
• LOBs — CLOB/BLOB values LogMiner reports in pieces (LOB_WRITE) are reassembled into the row.
• Never skips silently — missing redo fails the run naming the SCN range; changes LogMiner cannot render fail by default.

Database prerequisites

1. ARCHIVELOG mode, with archived logs kept until faucet has mined them.

2. Supplemental logging — minimal logging at the database (SYSDBA, in the CDB root), and all-column logging on each captured table for full before/after images:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ALTER TABLE APP.ORDERS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

   With only primary-key logging, updates carry the key and changed columns. A table that logs no key columns is rejected at startup.

3. Privileges for the capture user:

   GRANT CREATE SESSION, CREATE TABLE, LOGMINING, SELECT ANY TRANSACTION,
         SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE TO cdc_user;
   GRANT SELECT ON APP.ORDERS TO cdc_user;
   

   CREATE TABLE is for the flush table (below).

4. Where to connect — to the PDB that owns the tables (Oracle 21c+ mines per PDB and locates logs itself), or to a non-CDB / the CDB root (faucet registers the log files).

faucet doctor checks supplemental logging, full-row images and redo-log visibility, and prints the exact ALTER statement to run.

Configuration

Connection fields are documented in faucet-common-oracle.

source:
  type: oracle-cdc
  config:
    host: db.example.com
    service_name: ORCLPDB1
    username: cdc_user
    password: ${secret:ORACLE_PASSWORD}
    tables: [APP.ORDERS, APP.ORDER_LINES]

Change envelope

{ "op": "u", "schema": "APP", "table": "ORDERS",
  "before": {"ID": 1, "STATUS": "new"}, "after": {"ID": 1, "STATUS": "paid"},
  "scn": 2146947, "commit_scn": 2146951, "xid": "07000C00F3010000",
  "ts": "2024-01-02T03:04:05" }

op is i / u / d; DDL on a captured table arrives as ddl (with sql) and TRUNCATE as truncate, both dropped by the cdc_unwrap transform. Values are typed from the column's data type exactly as the query source types them. before is the logged before image (where clause) and never includes LOB columns.

Bookmark

{ "commit_scn": 2146951, "restart_scn": 2146790, "committed_xids": ["07000C00F3010000"] }

Transactions committed below commit_scn, or at it and listed in committed_xids, were emitted. Mining resumes at restart_scn, the first SCN of the oldest transaction open at the bookmark.

Failure behaviour

• Missing redo — if the logs covering the resume range were recycled or deleted, the run fails with the missing SCN range. Re-snapshot the tables, then restart capture.
• Dictionary mismatch — mining uses the current data dictionary (DICT_FROM_ONLINE_CATALOG). Redo written before a DDL on a captured table cannot be rendered after it; if a restart has to re-mine such redo the run fails (or skips, with on_unsupported: skip). Mining near real time avoids this; after DDL during downtime, re-snapshot the table.
• Unsupported changes (UNSUPPORTED operations, types LogMiner cannot render, binary XMLType changes) fail by default.
• Partial LOB writes — an append, a write at an offset above 1, or a trim on a LOB whose current value is not part of the same transaction cannot be reassembled (LogMiner logs only the piece). It is treated as an unsupported change rather than emitted as a padded or truncated value. CLOB positions are UTF-16 code units, as Oracle counts them; when LogMiner splits one write into several pieces, it advances each later piece by the previous piece's character count, and the source corrects for that, so characters outside the Basic Multilingual Plane (emoji and the like) land where they were written. Writes that start at offset 1 and run on without a gap, with no trim, are read as a whole new value — that is how LogMiner logs UPDATE … SET lob_col = …. A DBMS_LOB.WRITE that overwrites only the start of a longer existing value at offset 1, without trimming it, looks identical in the redo log, so the row then carries only the written part; if your application patches LOBs in place that way, re-read those rows from the table (a snapshot or faucet verify) rather than relying on the change stream.
• DDL — column metadata is reloaded at the DDL row, so later changes in the same mining window are typed by the new columns.
• RESETLOGS — the bookmark records the database incarnation (resetlogs_scn); only archived logs of the current incarnation are mined, and resuming a bookmark from another incarnation (after a point-in-time recovery or flashback) fails instead of mining an abandoned branch. Connected to a pluggable database, it also records the PDB's incarnation (pdb_incarnation_scn, from V$PDB_INCARNATION), so a PDB-level point-in-time recovery or flashback — which leaves the database-wide RESETLOGS SCN unchanged — fails the resume the same way. SELECT_CATALOG_ROLE (above) covers reading it; a user who cannot read it skips the check with a warning. A bookmark written before either field existed is bound to the current incarnation on its first resume, with a warning when it lies after that incarnation's start, because a recovery before the upgrade cannot be detected — reset such a bookmark yourself if the database or PDB was recovered since.
• RAC — the redo flush reaches only the connected instance, so with several open redo threads the mining window never passes the lowest thread checkpoint SCN (V$THREAD). Changes from every instance therefore arrive once the slowest thread checkpoints past them. A thread checkpoints on a log switch or a full checkpoint, so a quiet instance can hold capture back for as long as it goes without a log switch; a warning names the delay after a minute. Set ARCHIVE_LAG_TARGET (for example ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 60 SCOPE=BOTH) to force a switch on every thread at that interval, which bounds the latency. When capture is anchored at the current SCN (start_position: current, or the resume position faucet mirror captures), the oldest transaction still open on any instance is read from GV$TRANSACTION, so its earlier changes are mined too; a user who cannot read GV$TRANSACTION falls back to the connected instance's V$TRANSACTION with a warning.

Source lag

Source::lag (#733) reports seconds: the commit time of the database's current SCN minus that of the captured commit_scn (this run's last emitted bookmark, else the applied start bookmark), via SCN_TO_TIMESTAMP. SCN distances are not event counts, so no events or bytes are reported. When the position is older than the database's SCN-to-time mapping (ORA-08181) no lag is reported. The pipeline polls it on the first page, at most every 15 s after, and when the run ends, exporting faucet_source_lag_seconds{pipeline,row,connector}; faucet status --probe and faucet doctor ask it from the stored bookmark, and sla.max_lag_seconds turns it into an SLA. A failing lag query is logged once and reported as no lag — it never fails a run.

Mirroring

Pair with cdc_unwrap and an upsert-capable sink (write_mode: upsert, delete_marker: { field: __op, values: [d] }). For an initial load, faucet mirror captures capture_resume_position() before bulk-copying with the oracle query source, then streams changes from that SCN.

Capabilities

Declares exactly_once (deterministic replay from the SCN bookmark) and reports_lag (seconds behind the current SCN). faucet list --json prints the declaration.

Timeouts, cancellation and retries

The wait for each captured page, the pause between LogMiner polls and the lag and capture-position queries run through the pipeline's CallContext (#880): the run's deadline and cancel token end an idle cycle promptly with an error (the pipeline has already stopped reading and flushed what it took), and the capture thread stops at its next poll. Queries count as query round trips in faucet_source_roundtrips_total. Nothing is retried; the next run resumes from the last persisted SCN.

License

Licensed under either of Apache License, Version 2.0 or MIT license at your option.