Offset SCN stucks and not proceeding during HIGH Transactions

[Vinoth Kumar]

Hello Team,

I'm getting the below error with Oracle 19c database. I've attached the connector config details and shared the analysis sofar.

Can you please assist to move forward.

Error
"Offset SCN 9420122456881 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active.  Commit SCNs {1=9420159644839, 2=9420159644743}."

What we have tried
Verified from the database end that LogMiner is functioning correctly and successfully capturing all archive logs.
Monitored via SUMO Logic, where we noticed that the offset SCN remains stuck even though LogMiner has completed processing those archives.
Restarted the connector multiple times (both manual and automatic restarts) however, it continues to pick up from the same stuck SCN, referring to archives that have already been mined.
The SCN has not moved during such high loads, indicating that the connector offset isn't updating as expected.

Findings
After the data load, streaming stucks with the offset SCN.
This behavior is observed only in PROD, particularly during periods of high transaction volume (30-60G per hour).

Thanks,

Vinoth

[Chris Cranford]

Hi Vinoth -

First and foremost, this message isn't an error but rather just a warning, indicating that you could have a long running transaction present. In a situation where you could have a large batch operation that modifies millions of rows in a single transaction, this can happen until the transaction is completely consumed. But there are some other reasons that are related to bugs. 

What Debezium version are you using, so we can identify whether your issue may be related to a bug?

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/4fae1065-ead8-4811-9d91-2564f53e46fen%40googlegroups.com.

[Vinoth Kumar]

Hi Chris,

First of all thank you for the prompt response!

Yes its a warning but SCN not at all moved for a prolonged window and connector getting either restarted or failed with timeout. In addition, there were no long running sessions at the DB end.

Version: 3.1.3

We have Goldengate running on the same DB server where we don't see any latency issues. Also we stopped Goldengate for a while however Debezium log mining re-read from SCN from where it stuck eventhough those archives were already mined as per DB alert log.

Regards,

Vinoth Kumar

8056-274-822

To view this discussion visit https://groups.google.com/d/msgid/debezium/3211befd-77c2-4d0d-ab93-76753411ba04%40gmail.com.

[Chris Cranford]

Hi Vinoth -

Please update to 3.3.1.Final. There was a bug (DBZ-8747) that could leave transactions in-progress by mistake that were present in versions prior to 3.2.4.Final.  Could you update and see whether you continue to experience any issues with stuck OffsetScn values?

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8XZ76ZxeteAPh7tUX55j9VrPzukkkhvmmis86qYLnBisoQ%40mail.gmail.com.

[Vinoth Kumar]

Hi Chris,

Thanks much !

We will update and keep you informed on this.

Regards,

Vinoth Kumar

8056-274-822

To view this discussion visit https://groups.google.com/d/msgid/debezium/ef147fb8-ab3c-40e9-87f0-2d4c502f39ea%40gmail.com.

[Vinoth Kumar]

Hi Chris,

We've updated our Debezium to the latest version "3.3.1" and we did a clean initial load.

The large archive/transaction batch starts from 5 AM IST, will monitor and keep you updated 🤞

--

Regards,

Vinoth Kumar

8056-274-822

[Vinoth Kumar]

Hi Chris,
Post the Debezium update, Please see the below updates.

Before Debezium Update to 3.3.1

Monitored via SUMO Logic, where we noticed that the offset SCN remains stuck even though LogMiner has completed processing those archives.

Restarted the connector multiple times (both manual and automatic restarts) however, it continues to pick up from the same stuck SCN, referring to archives that have already been mined.

After Debezium Update to 3.3.1

Stuck SCN:
"9420755786214" - Archives "Thread1=767758 & Thread2=526103" --> 7:12 AM IST

Detected possible SCN gap: But SCN is moving ahead - startSCN
upperBounds SCN, startSCN 9420786218992, prevEndSCN 9420786218997  - Archives "Thread1=767958 & Thread2=526165" --> 9:04 AM IST

During shutdown SCN:
Offsets as shutdown: OracleOffsetContext [scn=9420796942649, txId=46000300bc3d0a00, txSeq=1, commit_scn=[\"9420797074942:1:38000900f8231000\",\"9420797028473:2:18000100e3eb1800\"], lcr_position=null]"

Here "9420796942649" - Archives "Thread1=768027 & Thread2=526188"   --> 11:07 AM IST

Post Restart:  Got the below message

No latest table SCN could be resolved, defaulting to current SCN 

Event with `MISSING_SCN` operation found with SCN 9420801068798 (Archives Thread1=768167 & Thread2=526233)

Here SCN is moved far ahead instead of picking from where it left. Is this expected ? Please let me know your valuable thoughts.

Note: In the current connector I kept only a small set of tables where there won't be many transactions, however the bigger tables which generate more transactions are yet to be onboarded.

Thanks,

Vinoth

[Vinoth Kumar]

Hi Chris/Community Team,

Can you please help and assist me on the below ask.

Regards,

Vinoth Kumar

8056-274-822

[Chris Cranford]

Hi Vinoth -

The "No latest table SCN could be resolved" happens only at one point in time, which is during the snapshot, which itself explains why the connector did not resume from where it left off at shutdown. As to why that happened is what we need to understand.  Did you delete the offsets and history topic, or is your snapshot.mode set to `when_needed`?  What is your connector configuration? 

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8XZN%3D9PYOcW0MHgoUnm1p4qiGeXsqr-Nzh9H-K849-A8cQ%40mail.gmail.com.

[Vinoth Kumar]

Hi Chris,

We haven't deleted anything.
Initially we had a small set of tables with "snapshot.mode: initial" and once the snapshot was completed then after sometime we stopped the connector and added the below snapshot mode. This is to add new tables when we perform Incremental load via Signal Table. We've followed a similar practice in other DBs where we don't see such messages.

 

    snapshot.mode: configuration_based
    snapshot.mode.configuration.based.snapshot.schema: true
    snapshot.mode.configuration.based.snapshot.data

Note: Small sets of tables won't have many transactions, however the newly added tables are bigger which generate more transactions and yet to be onboarded.

To view this discussion visit https://groups.google.com/d/msgid/debezium/01fcd90a-c548-4e93-91b5-4845ee346968%40gmail.com.

[Vinoth Kumar]

Hi Chris,

I mean, did this post the completion of initial snapshot.
#commented
"snapshot.mode: initial"
Added

  snapshot.mode: configuration_based
    snapshot.mode.configuration.based.snapshot.schema: true
    snapshot.mode.configuration.based.snapshot.data

[Chris Cranford]

Hi -

If you have set the following

    "snapshot.mode.configuration.based.snapshot.schema": "true",
    "snapshot.mode.configuration.based.snapshot.data": "true"

Then you have effectively told Debezium to use
    
    "snapshot.mode": "always"

This explains your circumstance.

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xb6-a7v%3DGfeU1di5_1n7PUUWb8%2B--2eC8QbA-CGTy7zfg%40mail.gmail.com.

[Vinoth Kumar]

Hi Chris,

Apologize, my bad for the typo.
Actually enabled CDC and disabled initial load to have Incremental load via signal for the new tables.

Worrying whether it is data loss or expected because transactions of those big tables were not in the replication list.

     snapshot.mode: configuration_based
    snapshot.mode.configuration.based.snapshot.schema: true
    snapshot.mode.configuration.based.snapshot.data: false
    snapshot.mode.configuration.based.start.stream: true

Thanks,

Vinoth

To view this discussion visit https://groups.google.com/d/msgid/debezium/abb9bf87-459b-4829-a4cb-84292a8cc01c%40gmail.com.

[Chris Cranford]

Hi Vinoth -

If a user performs a large transaction or if there is a period of many transactions, it's very plausible that you could see the OffsetScn plateau and not change for a period of time. This can be due to the connector waiting for data to be returned by Oracle, we're actively processing a large batch of data, or if there is an in-flight transaction that has not yet committed or rolled back.

I always suggest to folks to look at the broader picture of the connector, checking other JMX metrics to see whether or not they're updating periodically. Do you see the FetchQueryCount incrementing, or the TotalCapturedDmlCount or TotalProcessedRows increasing? These are signs that the connector is operating and consuming data. 

In the JMX metrics, what is the value of OffsetScn, OldestScn, CommittedScn, and CurrentScn ?

-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xbrz3sZRj2AMQcybjb2hKB1Dn21qFBaezhQptsPrnfPiA%40mail.gmail.com.

[Vinoth Kumar]

Hi Chris,

We've captured these logs from Sumologic and we do see these SCN values keep changing during offset scn or SCN gap so which means streaming continues as usual without hiccups, Correct If I'm wrong?
I've also asked the Infra team to share the JMX metric info for reference.

@timestamp:"2025-11-12T07:44:03.842Z  "Offset SCN 9421572168715 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active. Commit SCNs {1=9421573925565, 2=9421573925272}."

@timestamp:"2025-11-12T03:34:10.947Z" "Detected possible SCN gap, using upperBounds SCN, startSCN 9421562666535, prevEndSCN 9421562667455, timestamp 2025-11-11T19:34:03Z, upperBounds SCN 9421565658898 timestamp 2025-11-11T19:34:09Z."  

@timestamp:"2025-11-12T02:02:05.173Z" "Offset SCN 9421534993086 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active. Commit SCNs {1=9421535001588, 2=9421535001572}."

To view this discussion visit https://groups.google.com/d/msgid/debezium/6b6c578a-1786-4312-9102-4e4b17b025e3%40gmail.com.

[Chris Cranford]

That's correct, so I would say this is the classic pattern where you have a batch job that generates a substantial amount of redo in a small window, that triggers the connector to panic there could be an issue. The defaults for this check is quite conservative to work best for lower activity environments. I would recommend that you customize the defaults to see if it would help minimize the frequency of this warning.

1. Change log.mining.scn.gap.detection.gap.size.min from 1 million to perhaps 2 or 5 million.
2. Change log.mining.scn.gap.detection.time.interval.max.ms from 20000 (20 seconds) to 10000 (10 seconds)

So right now the connector looks to see if within a 20 second window does the SCN grow by 1 million. By reducing the time window and increasing the gap, you reduce the frequency.

As I think this often raises more questions/concerns than being helpful to the community, I've logged DBZ-9672 and intend to likely remove the behavior entirely in Debezium 3.4.

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xa1BMKeAKEJxAX3T99HRHZ7An_wAh3n0aJ%3D3cmc%3D32j0Q%40mail.gmail.com.

[Vinoth Kumar]

Hi Chris,

Great, thanks much for the excellent support !

Sure, will plan and modify the default settings to supress these messaga in the logs.

Regards,

Vinoth Kumar

8056-274-822

To view this discussion visit https://groups.google.com/d/msgid/debezium/0969c716-ce80-46a2-afe7-f63bb4c61eab%40gmail.com.