SCN number not getting updated

[Diwakar Kappatrala]

Hi,

Need help with an issue we are facing with Debezium with Confluent Kafka.

We are using Debezium 2.4 version with Confluent Kafka connector to capture CDC messages from Oracle 19.

The connector works well for a few hours and then it stops updating the SCN number. Even when the SCN number doesnt update the connector still captures data until the scn number is removed from the redo/archive logs due to purging.

In the logs I can see commit_scn, snapshot_scn and scn numbers printed. The commit_scn numbers keep moving upwards but snapshot_scn remains same which is expected as its the scn number from the time initial snapshot was taken.

But the scn number stops refreshing after a few hours for some unknown reason and then after few days the scn number will not be available in the redo/archive logs because of purging and connector throws error.

Why does the connector stops updating the scn number even though it prints the startSCN and prevEndSCN number and says using current SCN.

Is this a issue with Debezium 2.4? We are not using offset.flush.interval.ms property.

There is a warning also "Detected Possible SCN gap, using current SCN, startSCN, prevEndSCN".

Regards

Diwakar

[Chris Cranford]

Hi Diwakar -

So there are a number of reasons why the low watermark stops updating:

    - Long running transaction
    - Integration component like GoldenGate may have logged a REPLICATION_MARKER transaction in the redo logs
    - Oracle advanced the CURRENT_SCN by a large window unexpectedly
   
For the first two, the best course of action is to set `log.mining.transaction.retention.ms`. This helps avoid long running transactions from remaining in the buffer and causing the low watermark from advancing. For the latter, if you update to the most recent versions of Debezium and you are not using the `lob.enabled` configuration option, the REPLICATION_MARKER transactions are omitted automatically. If you are using the `lob.enabled` configuration property, there will be a PR that will skip these for you in an upcoming build.

For the last one, this ties more into your "Detected possible SCN gap" message.

The connector works based on an SCN mining range, and there's no way to predict ahead of time whether that range has any changes or not. If the last captured change position, the current read position, and the CURRENT_SCN value that are fed into a calculation indicates that the gap between these SCNs is greater than 1M within a window of 20 seconds, this log message will be written. The first reason this happens is based on user feedback where they detected that the SCN values jumped by a factor of 1M or more and they had no explanation for it. The community wanted a log message to indicate this use case so they could intervene manually if needed. The second reason this happens is if a database user performs a large bulk operation of several million rows. This can also trigger this log message since the same calculation in SCNs within a 20 second window will be true.

The connector will eventually stop logging the messages and recover on its own.

Thanks,
-cc

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/0e7357e9-dd81-486f-99f6-f1355cf8d60an%40googlegroups.com.