LOB tables: phantom events remain after partial fix in #15

[rophy]

Summary

OLR emits ~0.3% phantom INSERT/UPDATE events on tables with LOB columns (CLOB/BLOB) on Oracle RAC. These are rows that were never committed to the source database.

This is a follow-up to #15, which was partially fixed by PR #16 (restored FLG_ROLLBACK_OP0504 check, extended phantom undo detection). The remaining phantoms cannot be eliminated at the raw redo level.

Root Cause

Oracle's internal LOB segment management generates redo records that are indistinguishable from user DML. Only Oracle's COMMITTED_DATA_ONLY mode (used internally by LogMiner) can filter them, using Oracle-internal state not available in the redo stream.

Full investigation: documentation/LOB-PHANTOM-INVESTIGATION-REPORT.md

Key findings:

• Phantom events are Oracle's internal LOB maintenance ops embedded inside real user transactions
• Same phantom row re-appears across multiple unrelated committed transactions
• All investigated signals (dataObj, bdba+slot, suppLogBdba, opcode pairing) failed to reliably discriminate internal from user ops
• Non-LOB tables: 100% accurate (verified across 69K+ events, 41K+ rows)

Current Workaround

skip-lob-tables format option (commit f1243f1):

{ "format": { "skip-lob-tables": 1 } }

When enabled, OLR silently skips all DML events for tables with LOB columns. Users can replicate LOB tables through LogMiner (COMMITTED_DATA_ONLY) while using OLR for non-LOB tables.

Impact

Use case	Status
Non-LOB tables on RAC	100% accurate
LOB tables with skip-lob-tables: 1	No events emitted (use LogMiner)
LOB tables without workaround	~0.3% phantom events

What Would Fix This

A fix would require one of:

1. Oracle exposing LOB-internal operation metadata in redo (unlikely)
2. OLR deferring LOB transaction emission and waiting for potential rollbacks (adds latency)
3. OLR emitting compensating DELETE events after detecting rollbacks (requires keeping emitted XIDs in memory)

Options 2 and 3 are architecturally complex and may not catch all cases.

Related

• LOB phantom transactions: OLR doesn't distinguish commit from rollback in opcode 5.4 #15 — Original issue + partial fix (PR fix: LOB phantom undo suppression + fuzz test improvements #16)
• LOB INSERT dropped in multi-row transaction via Debezium network adapter olr#10 — LOB INSERT dropped (fixed)
• LOB phantom committed transactions in RAC online redo olr#26 — Non-LOB fixes complete (closed)

[rophy]

Status Update (2026-04-06)

PR #18 merged — implements the skip-lob-tables workaround and improves the fuzz test framework.

What was done

1. skip-lob-tables format option — When set to 1, OLR silently skips all DML events for tables with LOB columns (CLOB/BLOB/NCLOB). Users can replicate LOB tables through LogMiner while using OLR for everything else.

2. Investigation report — documentation/LOB-PHANTOM-INVESTIGATION-REPORT.md documents the root cause: Oracle's internal LOB segment management generates redo records indistinguishable from user DML. All investigated discrimination signals (dataObj, bdba+slot, suppLogBdba, opcode pairing) failed.

3. Fuzz test framework — Switched to KafkaOffsetBackingStore with SCN pre-seeding for clean, reproducible test runs. No stale events from previous runs.

Verified results

Non-LOB accuracy (SKIP_LOB=1, 10-min RAC fuzz):

validate:  69,359 events, 0 mismatches
db-check:  41,641 rows — DB=LM=OLR, 0 diffs, 0 missing, 0 extra

Remaining

The underlying LOB phantom issue is unchanged — ~0.3% extra events on LOB tables when skip-lob-tables is not enabled. No known fix path at the raw redo level.