Bug report

Relates to this conversation: https://debezium.zulipchat.com/#narrow/channel/348250-community-oracle/topic/OldestScn.20not.20advancing.20in.203.2E2.2E1/near/556764402

In 3.2.3 and potentially all earlier versions DBZ seems to not be able to deal with an unusual way that Oracle handles splitting a transaction across two queries/sessions.

Here are two transactions mined in one step. Note that both rollbacks are accounted for

However, if split across two steps at a specific point Oracle will generate a partial transaction id as shown below and DBZ seems to then not be able to match the overall rollback to the transaction that's in the buffer:

I've attached also the full range that would have been mined in each fetch query for the test case I did

What Debezium connector do you use and what version?

I have reproduced on 3.2.0 and also observed on 3.0.8

What is the connector configuration?

Hybrid, with "IN" otherwise largely defaults

What is the captured database version and mode of deployment?

Oracle 19C

What behavior do you expect?

DBZ should be able to walk these back to the correct transactions in the buffer similar to partial rollbacks

What behavior do you see?

DBZ misses the rollback and so low watermark never advances

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

NO, but caveat: The reason this doesn't occur in the latest version is that it is no longer possible in recent versions to mine a transaction in two steps as the eldest open transaction is the low watermark for the session. This solves a lot of issues however in some of our environments it makes mining infeasible where there are large transaction volumes and long-running transactions.

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

Yes have attached the log which has a customisation to print out the fetch ranges as info messages.