Oracle Read-Only Incremental Snapshots

# Oracle: read-only incremental snapshots

## Summary

Oracle has no incremental snapshot support when the connector runs against a read-only database. Adding a read-only incremental snapshot implementation, in the same spirit as the MySQL and PostgreSQL ones, is feasible and Oracle arguably has better primitives for it than either of those connectors.

## Motivation

`OracleChangeEventSourceFactory` refuses to construct an incremental snapshot source at all when the connector is in read-only mode:

```java
// OracleChangeEventSourceFactory.java:97-100
// Cannot use incremental snapshots with a read-only connection
if (configuration.isLogMiningReadOnly()) {
return Optional.empty();
}
```

Oracle only ships `OracleSignalBasedIncrementalSnapshotChangeEventSource`, which extends `SignalBasedIncrementalSnapshotChangeEventSource`. That implementation INSERTs `snapshot-window-open` and `snapshot-window-close` rows into the signal table so the watermarks appear in the redo stream at the correct position. That write is the only thing preventing incremental snapshots on a read-only source.

This matters more than it used to. With `capture.mode=physical_standby` and `capture.mode=downstream`, the capture target is non-writable by definition, so this is no longer a niche configuration.

## What the framework already provides

No changes to `debezium-connector-common` are required. This is a connector-local feature.

`AbstractIncrementalSnapshotChangeEventSource` declares exactly two abstract methods for watermarking:

```java
protected abstract void emitWindowOpen(P partition, OffsetContext offsetContext) throws SQLException; // :214
protected abstract void emitWindowClose(P partition, OffsetContext offsetContext) throws Exception; // :219
```

The signal-based subclass writes rows; the read-only subclasses instead capture a stream position. `EventDispatcher` already invokes the remaining hooks generically:

| Hook | `EventDispatcher` line |
| --- | --- |
| `processMessage` | 327 |
| `processFilteredEvent` | 381 |
| `processTransactionCommittedEvent` | 388 |
| `processSchemaChange` | 427 |
| `processHeartbeat` | 470, 488 |

PostgreSQL is the better model to copy than MySQL. MySQL compares GTID *sets*, which requires set-containment logic. PostgreSQL compares a `pg_current_snapshot()` triple (xmin / xmax / in-progress list). Oracle needs neither, because SCN is a single monotonic scalar.

## Proposed design

### Watermarks

Replace the signal-table write with an SCN read, via `SELECT CURRENT_SCN FROM V$DATABASE` or `DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER`:

```java
@Override
protected void emitWindowOpen(P partition, OffsetContext offsetContext) {
getContext().setLowWatermark(currentScn());
}

@Override
protected void emitWindowClose(P partition, OffsetContext offsetContext) {
getContext().setHighWatermark(currentScn());
}
```

### Window state

The state machine collapses to scalar comparison. Compare against the PostgreSQL version, which needs `eventTxId >= lowWatermark.getXMin()` and `eventTxId > Math.max(highWatermark.getXMax(), lowWatermark.getXMax())`:

```java
if (!windowOpened && lowWatermark != null && eventScn.compareTo(lowWatermark) >= 0) {
windowOpened = true;
}
if (windowOpened && highWatermark != null && eventScn.compareTo(highWatermark) > 0) {
closeWindow();
}
```

### Critical detail: use the commit SCN, not the event SCN

In buffered mode, a transaction's events are dispatched at commit time, but `source.scn` carries the SCN at which the DML occurred. A transaction that modifies a row at SCN 105 and commits at SCN 500 is dispatched *after* transactions that committed at SCN 400, while still reporting `scn=105`.

`source.scn` is therefore **not monotonic in stream order**. Using it for window comparison would open and close windows against a position the stream has already passed.

The correct value is `SourceInfo.getEventCommitScn()`, surfaced as `commit_scn` in the source struct (`OracleSourceInfoStructMaker:79-82`). It is monotonic in dispatch order and is the proper analog of the PostgreSQL `txid`.

Caveat: `commit_scn` is written conditionally.

```java
final Scn eventCommitScn = sourceInfo.getEventCommitScn();
if (eventCommitScn != null && !eventCommitScn.isNull()) {
ret.put(SourceInfo.COMMIT_SCN_KEY, eventCommitScn.toString());
}
```

The heartbeat path therefore cannot rely on the struct and must read `getOffsetContext().getCommitScn()` or the offset SCN directly.

### Oracle-specific advantage: flashback query

MySQL and PostgreSQL both tolerate a gap between "take the low watermark" and "the SELECT actually reads". Part of what the dedup window absorbs is that gap. Oracle can close it:

```sql
SELECT ... FROM tbl AS OF SCN :lowWatermark WHERE ... ORDER BY ...
```

The chunk is then read at exactly the low watermark rather than at some unknown later SCN. The connector already has precedent: `OracleConnection.reselectColumns` performs `AS OF SCN` with an ORA-01555 / ORA-01466 fallback, so the failure-handling pattern is established.

The tradeoff is that chunk reads become dependent on undo retention. A slow snapshot over a busy table can outrun `UNDO_RETENTION` and begin failing with ORA-01555. This should probably be optional, falling back to a plain read plus the standard dedup window.

## Implementation sketch

- `OracleReadOnlyIncrementalSnapshotChangeEventSource extends AbstractIncrementalSnapshotChangeEventSource`
- `OracleReadOnlyIncrementalSnapshotContext extends AbstractIncrementalSnapshotContext`
- `OracleOffsetContext` loader selects the read-only context when configured
- `OracleChangeEventSourceFactory:97` changes from returning `Optional.empty()` to returning the read-only variant

Signaling must come from `KafkaSignalChannel` or `JmxSignalChannel`, since the source signal table is unavailable by definition. This is the same constraint MySQL read-only mode already carries.

## Open questions

These need answers before implementation, not after.

1. **Heartbeat-driven progress.** The PostgreSQL implementation relies on heartbeats to keep the window advancing when change volume is low, via `readUntilNewTransactionChange`. Oracle already dispatches heartbeats generously with an advancing offset SCN, including in the deferred transaction path, so the raw material exists. The commit-SCN versus offset-SCN distinction must be handled deliberately in `processHeartbeat`.

2. **Adapter coverage.** LogMiner buffered is the straightforward case. Unbuffered, XStream, and OpenLogReplicator each surface position differently. OLR in particular is not SCN-ordered in its stream, so a watermark comparison there needs separate design rather than being assumed to fall out.

3. **Standby semantics.** On an Active Data Guard standby, `CURRENT_SCN` reflects the applied redo position rather than the primary's current SCN. That is arguably the correct watermark, since it matches what the stream will deliver, but you should confirm it rather than assume it. For _downstream_ systems, the process needs to support primary reads for CURRENT_SCN and downstream reads for the actual change stream.

4. **Privileges.** `V$DATABASE` requires SELECT, and `DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER` requires EXECUTE on `DBMS_FLASHBACK`. Whichever is chosen needs to be added to the documented privilege set if it's not already present.