In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.

Bug report

For bug reports, provide this information, please:

What Debezium connector do you use and what version?

2.0.0.Beta1

What is the connector configuration?

internal.log.mining.transaction.snapshot.boundary.mode=all

What is the captured database version and mode of depoyment?

Oracle 19

PDB

What behaviour do you expect?

No missing transaction after snapshotting and streaming reach current SCN

What behaviour do you see?

Missing transaction after snapshotting and streaming reach current SCN.

• snapshot SCN = 4051315580
• Missing Tx: 5c001a002d570100, start 4051314398, end 4051315588
• Found in-progress transaction 1c00080079380a00, starting at SCN 4051306564, end 4051434547 (base on redo log)
• Found in-progress transaction 7b000d00802a0100, starting at SCN 4051314893, (not found in redo log probably because I don't enable log mining for that schema)
• In redo log: Transaction 36000200E6850500, start at SCN 4051314893, end 4051314899

=> because min SCN is 4051306564 in pending transaction => intial start SCN = 4051306564 - 1 = 4051306563

Timeline:

4051306563 (intial streaming) -> 4051314398 (start of missing tx 5c001a002d570100) -> 4051315580 (snapshot SCN) -> 4051315588 (commit tx 5c001a002d570100) 

Transaction 5c001a002d570100 doesn't appear in V$TRANSACTION. Maybe because commit SCN and snapshot SCN is too close.

Do you see the same behaviour using the latest relesead Debezium version?

Yes

Do you have the connector logs, ideally from start till finish?

2022-08-05 03:32:44,241 INFO [debezium-oracleconnector-a71ceb9a-621b-3a32-83fd-25327be9ae59-change-event-source-coordinator]  i.d.c.oracle.logminer.LogMinerAdapter     Found in-progress transaction 1c00080079380a00, starting at SCN 4051306564
2022-08-05 03:32:44,241 INFO [debezium-oracleconnector-a71ceb9a-621b-3a32-83fd-25327be9ae59-change-event-source-coordinator]  i.d.c.oracle.logminer.LogMinerAdapter     Found in-progress transaction 7b000d00802a0100, starting at SCN 4051314893
2022-08-05 03:32:44,252 INFO [debezium-oracleconnector-a71ceb9a-621b-3a32-83fd-25327be9ae59-change-event-source-coordinator]  i.d.r.RelationalSnapshotChangeEventSource Snapshot step 5 - Reading structure of captured tables
2022-08-05 03:32:48,462 INFO [debezium-oracleconnector-a71ceb9a-621b-3a32-83fd-25327be9ae59-change-event-source-coordinator]  i.d.r.RelationalSnapshotChangeEventSource Snapshot step 6 - Persisting schema history 

Implementation ideas (optional)

when check pending tx in redo log, condition in LogMinerAdapter.getTransactionIdForScn() is wrong to detect pending transaction.

• Maybe we should return list instead of a single value
• SELECT XID FROM V$LOGMNR_CONTENTS WHERE SCN = <current_scn> is not enough to detect pending transaction