LOB phantom transactions: OLR doesn't distinguish commit from rollback in opcode 5.4

[rophy]

Summary

OLR emits LOB phantom transactions that Oracle internally rolled back. This causes ~2% extra INSERT/UPDATE events on LOB tables that don't exist in the source database.

Follows up from rophy/olr#26, which is now closed for non-LOB fixes.

Root Cause

Oracle's LOB management internally:

1. Commits a transaction (opcode 5.4) containing LOB INSERTs/UPDATEs
2. Then in the same XID, records DELETEs + a rollback (opcode 5.4 with FLG_ROLLBACK_OP0504 flag 0x0004)

LogMiner handles this with COMMITTED_DATA_ONLY — it reads the full XID lifecycle and filters transactions ending in ROLLBACK.

OLR treats all 0x0504 opcodes as commits (Parser.cpp:551). The FLG_ROLLBACK_OP0504 flag is parsed in OpCode0504.h but only used for dump logging — never checked to distinguish commit from rollback. When OLR sees the first 0x0504 (commit), it emits and drops the XID. The second 0x0504 (rollback) is ignored.

Evidence

Direct LogMiner query on phantom transaction 30000E00820A0000:

SCN 29570018: START → 5 INSERTs + 2 UPDATEs on FUZZ_LOB
SCN 29570023: 5 DELETEs on FUZZ_LOB (Oracle internal cleanup)
SCN 29570024: ROLLBACK

Pattern confirmed on 3 phantom transactions. All 91 phantom transactions from a 30-min fuzz test (212 events) are FUZZ_LOB only.

Impact

• ~2% of LOB table events are phantom (extra INSERTs/UPDATEs for rows that don't exist)
• Non-LOB tables: not affected (confirmed 0 mismatches on 177K events)
• LOB tables are common in production Oracle databases (CLOB, BLOB columns)

Possible Fixes

1. Check rollback flag: In appendToTransactionCommit(), if FLG_ROLLBACK_OP0504 is set, discard instead of emit. But the COMMIT arrives before the ROLLBACK in the redo stream — OLR emits on the first 0x0504 and the XID is already gone when the rollback arrives.
2. Hold LOB transactions: Defer emission of LOB-only transactions for a grace period to catch subsequent rollbacks. Adds latency.
3. Emit compensating events: After detecting the rollback, emit DELETE events to undo the phantom INSERTs. Requires keeping emitted XIDs in memory.

Reproduction

cd tests/dbz-twin/rac
./fuzz-test.sh up
./fuzz-test.sh run 5
./fuzz-test.sh validate  # Reports ~20-40 LOB known issues

Environment

• OLR v1.9.0, RAC online redo mode (deferCommittedTransactions = true)
• Oracle RAC 23.26.1.0 (2-node)
• FUZZ_LOB table: (id NUMBER PK, label VARCHAR2(50), content CLOB, bin_data BLOB)

[rophy]

PR #16 only partially fixed this issue. It restored the FLG_ROLLBACK_OP0504 check and extended phantom undo detection to UPDATE→UPDATE, which reduced phantom events but did not eliminate them.

The remaining ~0.3% phantom LOB events cannot be fixed at the raw redo level — Oracle's internal LOB segment management generates redo records indistinguishable from user DML. See the full investigation report: documentation/LOB-PHANTOM-INVESTIGATION-REPORT.md (commit 2e7c655).

A skip-lob-tables format option was added (commit f1243f1) as a workaround, allowing users to exclude LOB tables from OLR and replicate them through LogMiner instead.

Follow-up issue: to be linked.

[rophy]

Follow-up issue: #17