Offset SCN stucks and not proceeding during HIGH Transactions
Vinoth Kumar
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,
Chris Cranford
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
To view this discussion visit https://groups.google.com/d/msgid/debezium/3211befd-77c2-4d0d-ab93-76753411ba04%40gmail.com.
Chris Cranford
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
To view this discussion visit https://groups.google.com/d/msgid/debezium/ef147fb8-ab3c-40e9-87f0-2d4c502f39ea%40gmail.com.
Vinoth Kumar
Vinoth Kumar
Post the Debezium update, Please see the below updates.
Monitored via SUMO Logic, where we noticed that the offset SCN remains stuck even though LogMiner has completed processing those archives.
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.
Vinoth Kumar
Chris Cranford
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
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.schema: true
snapshot.mode.configuration.based.snapshot.data
To view this discussion visit https://groups.google.com/d/msgid/debezium/01fcd90a-c548-4e93-91b5-4845ee346968%40gmail.com.
Vinoth Kumar
#commented
"snapshot.mode: initial"
Added
snapshot.mode: configuration_based
snapshot.mode.configuration.based.snapshot.schema: true
snapshot.mode.configuration.based.snapshot.data
Chris Cranford
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
Actually enabled CDC and disabled initial load to have Incremental load via signal for the new tables.
snapshot.mode.configuration.based.snapshot.schema: true
snapshot.mode.configuration.based.snapshot.data: false
snapshot.mode.configuration.based.start.stream: true
To view this discussion visit https://groups.google.com/d/msgid/debezium/abb9bf87-459b-4829-a4cb-84292a8cc01c%40gmail.com.
Chris Cranford
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
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}."
To view this discussion visit https://groups.google.com/d/msgid/debezium/6b6c578a-1786-4312-9102-4e4b17b025e3%40gmail.com.
Chris Cranford
- Change log.mining.scn.gap.detection.gap.size.min from 1 million to perhaps 2 or 5 million.
- 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
To view this discussion visit https://groups.google.com/d/msgid/debezium/0969c716-ce80-46a2-afe7-f63bb4c61eab%40gmail.com.
Vinoth Kumar
max.batch.size: 8192
max.queue.size: 65536
max.queue.size.in.bytes: 536870912 #512MB
log.mining.query.filter.mode: in
topic.prefix: BCPROD
topic.creation.enable: true
topic.creation.default.replication.factor: -1
topic.creation.default.partitions: -1
topic.creation.default.retention.ms: 1209600000
schema.include.list: APPNAME,DBZUSER
table.include.list: APPNAME.TABLE1,APPNAME.TABLE2,DBZUSER.HEARTBEAT,DBZUSER.SIGNAL
log.mining.batch.size.default: 60000
log.mining.batch.size.max: 1000000
log.mining.batch.size.min: 10000
log.mining.scn.gap.detection.gap.size.min: 1000000 # 1 million SCNs
log.mining.scn.gap.detection.time.interval.max.ms: 10000 # 10 seconds
#Special character
field.name.adjustment.mode: avro
#BLOB, RAW
binary.handling.mode: base64
Offset SCN 9423036857229 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active. Commit SCNs {1=9423062374267, 2=9423062373495}."
LOGMINER: Summary for session 2164177921, OS id 374401
LOGMINER: StartScn: 9423036857229 (0x00000891f8c3bb8d)
LOGMINER: EndScn: 9423069174443 (0x00000891fab0daab)
LOGMINER: HighConsumedScn: 0
LOGMINER: PSR flags: 0x0
LOGMINER: Session Flags: 0x4000441
LOGMINER: Session Flags2: 0x400
LOGMINER: Read buffers: 4
LOGMINER: Region Queue size: 256
LOGMINER: Redo Queue size: 4096
LOGMINER: Memory Size = 15M, HWM 14M, LWM 13M, 90%
LOGMINER: Memory Release Limit: 0M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2025-12-01T22:48:04.942578-08:00
Vinoth Kumar
LOGMINER: StartScn: 9423036857229 (0x00000891f8c3bb8d)
LOGMINER: EndScn: 9423100213632 (0x00000891fc8a7980)
To view this discussion visit https://groups.google.com/d/msgid/debezium/38e4b5fa-b051-4adf-8ce9-584e99e0d89cn%40googlegroups.com.
Chris Cranford
Can you checkout the code from https://github.com/Naros/debezium-oracle-query.tool and follow the README on how to compile/build the code. Once you have the code built, could you please run the following:
java -jar target/quarkus-app/quarkus-run.jar \
list-changes \
--hostname <your database.host> \
--username <your database.user> \
--password <your database.password> \
--port <your database.port> \
--service <your database.dbname> \
--start-scn 9423036857200 \
--end-scn 9423069174443 \
--output extract.csv
That will generate a CSV file on all the data changes between the two SCN ranges. Can you please ZIP that up and share that with my email privately so I can review the data. The log entry indicates that a transaction was started and is still in-progress, which would explain why the lower SCN does not change. The extract will help identify if thats the case or if there is a potential bug in the connector.
Thanks,
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/38e4b5fa-b051-4adf-8ce9-584e99e0d89cn%40googlegroups.com.
Chris Cranford
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xau9d2Ap-3ODRA%2Bbri%2B%3D19GreJPrrij%3DYRGHpG_aXhqMA%40mail.gmail.com.
Vinoth Kumar
Chris Cranford
To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xan3zpJmkx3zpL7MGLGviVURu2m%3D4GaK4P7_DwOonBg%3DA%40mail.gmail.com.
Vinoth Kumar
Chris Cranford
Having multiple connectors running can create an IO bottleneck if the redo subsystem is already saturated by normal user load.
To view this discussion visit https://groups.google.com/d/msgid/debezium/CAAMG8Xaz0QqpgPSvjCNjhZ0Ak6KUuAGDPkWDX01CgChd2xEZyA%40mail.gmail.com.
Vinoth Kumar
Hi Chris,
We exported the CSV as per the given tool where it comes around 3.1G size, later for better analysis we imported into one of the test MySQL instance where we noticed the stuck SCN 9423036857229 not exists, attached details for reference.
Due to this its stuck, it never moved ahead and waiting for the transaction to complete ? If that's the case how can we overcome such scenario. please suggest.
Also please let us know if you need any other additional details for analysis.
"Offset SCN 9423036857229 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active. Commit SCNs {1=9423127090737, 2=9423127088242}."
Chris Cranford
To view this discussion visit https://groups.google.com/d/msgid/debezium/0e4bc93e-2b72-4c84-82d0-243fc928eb65n%40googlegroups.com.
Vinoth Kumar
Thanks,
Chris Cranford
To view this discussion visit https://groups.google.com/d/msgid/debezium/86f3e854-30fd-4edf-835b-1fe049b142bdn%40googlegroups.com.
Chris Cranford
I took a look at the code, and this is one of those log entries where the value is misaligned with intent. The transaction in question that causes this would have SCN 9423036857230. And as we can see from your SQL output, it appears those are still in-progress right?
Thanks,
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/4e3788c5-3154-4577-9ce5-b90aa1b99596n%40googlegroups.com.
Chris Cranford
This should avoid any level of ambiguity about what transactions Debezium refers.
-cc
[1]: https://github.com/debezium/dbz/issues/20
Vinoth Kumar
mysql> SELECT XID, SCN, START_SCN, COMMIT_SCN, ROLLBACK, STATUS, START_TIMESTAMP, COMMIT_TIMESTAMP, operation FROM oracle_changes WHERE SCN=9423036857230;
+------------------+---------------+-----------+------------+----------+--------+---------------------+---------------------+-----------+
| XID | SCN | START_SCN | COMMIT_SCN | ROLLBACK | STATUS | START_TIMESTAMP | COMMIT_TIMESTAMP | operation |
+------------------+---------------+-----------+------------+----------+--------+---------------------+---------------------+-----------+
| 13001f00913c2200 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | INTERNAL |
| 13001f00913c2200 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | UPDATE |
| 70000a0007950600 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | UPDATE |
| 70000a0007950600 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | UPDATE |
| 64001a00b24d0500 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | START |
| 64001a00b24d0500 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | INSERT |
| 64001a00b24d0500 | 9423036857230 | 0 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | INSERT |
+------------------+---------------+-----------+------------+----------+--------+---------------------+---------------------+-----------+
7 rows in set (1.66 sec)
mysql> SELECT XID, SCN, START_SCN, COMMIT_SCN, ROLLBACK, STATUS, START_TIMESTAMP, COMMIT_TIMESTAMP, operation FROM oracle_changes WHERE START_SCN=9423036857230;
Empty set (1.65 sec)
mysql> SELECT XID, SCN, START_SCN, COMMIT_SCN, ROLLBACK, STATUS, START_TIMESTAMP, COMMIT_TIMESTAMP, operation FROM oracle_changes WHERE SCN=9423036857229;
Empty set (1.69 sec)
mysql> SELECT XID, SCN, START_SCN, COMMIT_SCN, ROLLBACK, STATUS, START_TIMESTAMP, COMMIT_TIMESTAMP, operation FROM oracle_changes WHERE START_SCN=9423036857229;
Empty set (1.65 sec)
mysql>
Chris Cranford
Regarding (1), the message as it is currently written requires understanding how the connector handles offsets. The offset is always 1 SCN before where we want to read, which means we've read up to and including 9423036857229. So this means the transaction that starts at 9423036857230 is what is causing the issue. This is where the enhancement will help clarify which transactions are problematic and allow you to look for them by transaction id rather than SCN, since transaction ids are unique and SCNs are not.
Regarding (2), you should never trust START_SCN nor COMMIT_SCN when using LogMiner in uncommitted data mode. These fields are only populated if and only if a transaction starts and ends within the SCN boundary you've specified in the LogMiner session arguments. We don't use these in Debezium's buffered implementation, only SCN is used. So you should go by that column only and ignore the other two.
Lastly regarding long-running transactions, this really depends on whether LOB is enabled or disabled. The algorithm here is very technical in nature and varies on a variety of factors and current database state. But what's most important is that Debezium will continue to load logs starting from the position where that long running transaction starts. This means potentially more and more data that needs to be handled by LogMiner over that 2 hour period. If your database is generating tens to hundreds of GBs of redo during this same window, this can compound into growing latency.
The more critical point to remember is long running transactions not only create higher IO for the LogMiner database process, but they also continue to consume more and more heap in the connector's JVM process. Moreover, if the Connect environment suffers an OutOfMemoryError, the environment undergoes a rebalance, or the configuration of the connector is updated while such a long running transaction is in progress, the connector will restart where that long running transaction started several hours prior. This is all because Oracle writes uncommitted transactions to its log, and they're only published to your topics when the transaction commits.
To handle a gap of 500M, this is where setting ideal `log.mining.batch.size.*`, `log.mining.sleep.time.*`, `log.mining.query.filter.mode`, and `query.fetch.size` connector settings can really help.
But one question on "63M SCN taking 12 hours and 1GB archive log in 1hr range", are you saying you had 63M unique SCNs, and they were all in a single 1GB archive log, and it took 12 hours to mine that? If my understanding is correct, this is far too long for so little and this sounds not an issue with Debezium but one with Oracle, and I would ask that during this period of time, share your database alert logs for all RAC nodes.
Thanks,
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/6c45e5cc-61a5-4d86-8432-04900870fd12n%40googlegroups.com.
Vinoth Kumar
Hi Chris,
Thank you for the details, Just a note this is Oracle 19c CDB !
We almost have the params already except log.mining.sleep.time.* & query.fetch.size and below is the connector config. Kindly Note: Onboarded 500+ tables. 1st connector - 480 tables and table size is small, 2nd Connector - 15 tables which has huge LOBs and rest all 3 connectors has not more than 3-5 tables which are either BIG in size or some tables don't have PK/Unique key.
To answer your question, if you see the below logs. To process 32M SCNs btwn 9423036857229 & 9423037474443 it took atleast 10hrs and need to check 6 unique archivelog and redolog allocated with 1G. Also no commits were found (Verified this info in MySQL DB for the XIDs “13001f00913c2200, 70000a0007950600 , 64001a00b24d0500 with SCN 9423036857230).
Can you please assist the next steps.
- Should we need to revisit and reduce the no.of connectors which contributes more IO to process same info again with LogMiner however there is NO Guarantee that it will not stuck SCN.
- Even if this is genuine long running transaction/batch with high gap in SCN, how can we overcome such scenarios.
- Also as per below logs Commit SCNs {1=9423062374267, 2=9423062373495} found but LogMiner already processed all those SCNs. No commits were found for all the XIDs (Verified this info in MySQL DB for the XIDs “13001f00913c2200, 70000a0007950600 , 64001a00b24d0500 with SCN 9423036857230).
Offset SCN 9423036857229 has not changed in 25 mining session iterations. This indicates long running transaction(s) are active. Commit SCNs {1=9423062374267, 2=9423062373495}."
select inst_id, thread#, sequence#, first_change#, first_time,next_change#,next_time,completion_time from gv$archived_log
where dest_id=1 and first_change#>=9423036857229
and next_change#<=9423069174443 order by first_change# ;
INST_ID THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME COMPLETION_TIME
---------- ---------- ---------- --------------- -------------------- --------------- -------------------- --------------------
2 1 43921 9423037957169 01 DEC 2025 12:44:57 9423049206943 01 DEC 2025 13:14:58 01 DEC 2025 13:14:59
1 1 43921 9423037957169 01 DEC 2025 12:44:57 9423049206943 01 DEC 2025 13:14:58 01 DEC 2025 13:14:59
2 2 38771 9423037968932 01 DEC 2025 12:44:59 9423049226816 01 DEC 2025 13:15:00 01 DEC 2025 13:15:00
1 2 38771 9423037968932 01 DEC 2025 12:44:59 9423049226816 01 DEC 2025 13:15:00 01 DEC 2025 13:15:00
1 1 43922 9423049206943 01 DEC 2025 13:14:58 9423060091763 01 DEC 2025 13:44:56 01 DEC 2025 13:44:58
2 1 43922 9423049206943 01 DEC 2025 13:14:58 9423060091763 01 DEC 2025 13:44:56 01 DEC 2025 13:44:58
2 2 38772 9423049226816 01 DEC 2025 13:15:00 9423060101162 01 DEC 2025 13:44:58 01 DEC 2025 13:44:58
1 2 38772 9423049226816 01 DEC 2025 13:15:00 9423060101162 01 DEC 2025 13:44:58 01 DEC 2025 13:44:58
1 1 43923 9423060091763 01 DEC 2025 13:44:56 9423068040157 01 DEC 2025 14:14:57 01 DEC 2025 14:14:59
2 1 43923 9423060091763 01 DEC 2025 13:44:56 9423068040157 01 DEC 2025 14:14:57 01 DEC 2025 14:14:59
1 2 38773 9423060101162 01 DEC 2025 13:44:58 9423068040313 01 DEC 2025 14:14:59 01 DEC 2025 14:15:00
2 2 38773 9423060101162 01 DEC 2025 13:44:58 9423068040313 01 DEC 2025 14:14:59 01 DEC 2025 14:15:00
2025-12-01T12:46:12.662021-08:00
LOGMINER: Summary for session 2164108289, OS id 374401
LOGMINER: StartScn:
9423036857229 (0x00000891f8c3bb8d)
LOGMINER: EndScn: 9423037474443 (0x00000891f8cd268b)
LOGMINER: HighConsumedScn: 0
LOGMINER: PSR flags: 0x0
LOGMINER: Session Flags: 0x4000441
LOGMINER: Session Flags2: 0x0
LOGMINER: Read buffers: 4
LOGMINER: Region Queue size: 256
LOGMINER: Redo Queue size: 4096
LOGMINER: Memory Size = 15M, HWM 14M, LWM 13M, 90%
LOGMINER: Memory Release Limit: 0M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2025-12-01T22:48:04.878580-08:00
LOGMINER: Summary for session 2164177921, OS id 374401
LOGMINER: StartScn:
9423036857229 (0x00000891f8c3bb8d)
LOGMINER: EndScn: 9423069174443 (0x00000891fab0daab)
LOGMINER: HighConsumedScn: 0
LOGMINER: PSR flags: 0x0
LOGMINER: Session Flags: 0x4000441
LOGMINER: Session Flags2: 0x400
LOGMINER: Read buffers: 4
LOGMINER: Region Queue size: 256
LOGMINER: Redo Queue size: 4096
LOGMINER: Memory Size = 15M, HWM 14M, LWM 13M, 90%
LOGMINER: Memory Release Limit: 0M
LOGMINER: Max Decomp Region Memory: 1M
LOGMINER: Transaction Queue Size: 1024
2025-12-01T22:48:04.942578-08:00
Connector Info:
#Debezium Engine/Queing Settings
max.batch.size: 8192
max.queue.size: 65536
max.queue.size.in.bytes: 536870912 #512MB
log.mining.query.filter.mode: in
topic.prefix: DBNAME
topic.creation.enable: true
topic.creation.default.replication.factor: -1
topic.creation.default.partitions: -1
topic.creation.default.retention.ms: 1209600000
schema.include.list: APPNAME,DBZUSER
table.include.list:
APPNAME.TABLE1,APPNAME.TABLE2,DBZUSER.HEARTBEAT,DBZUSER.SIGNAL
log.mining.batch.size.default: 60000
log.mining.batch.size.max: 1000000
log.mining.batch.size.min: 10000
log.mining.scn.gap.detection.gap.size.min: 1000000
# 1 million SCNs
log.mining.scn.gap.detection.time.interval.max.ms:
10000 # 10 seconds
#Special character
field.name.adjustment.mode: avro
#BLOB, RAW
binary.handling.mode: base64
Chris Cranford
If no commits were found, did you look for rollbacks? If neither are found, then it sounds to me as though these transactions were still in-progress, right?
(1): The number of connectors you deploy will have a direct impact on the system. The more LogMiner processes that are competing to read the archive and redo logs, the more IO saturation you're going to see. This is one of the reasons we generally advocate users to use a single connector and only horizontally scale as needed after verifying performance.
(2): Can the long running transaction/batch be split into smaller transactions allowing the process to commit more often?
(3): Yes, the Commit SCNs here describe the highest commit point per redo thread we've processed.
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/392f7dcc-638b-4310-aa6d-1edfadd90907n%40googlegroups.com.
Vinoth Kumar
Yes no commit/rollback observed with the LogMiner data which we captured for those 32M SCNs for the transactions associated with the SCN 9423036857230, also took 10hrs to process this. Even to monitor the performance we stopped the other connectors and currently left with only 2 connectors but still that takes huge latency to process 60M SCN atleast 6-10hrs which I observed randomly.
Honestly we don’t have a control on those long running transactions. Also we do have a Goldengate running on the same database where we don’t see issues. I agree that Debezium waits for a transaction to commit/rollback for all transactions associated with the SCN however Goldengate doesn’t so is there any way to optimize or achieve similar performance or that’s the limitation with Debezium. Please clarify.
Chris Cranford
This is the significant difference between adhoc mining with LogMiner versus using Oracle's continuous mining features available with XStream and GoldenGate. Perhaps you might want to consider using the XStream adapter if you have an Oracle GoldenGate license, rather than LogMiner?
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/7452560d-024e-47c2-bf25-c9e978fec7f6n%40googlegroups.com.
Vinoth Kumar
Hi Vinoth -
Chris Cranford
In practice, long running transactions and CDC don't mix well because something has to buffer that transaction until its committed. In some database platforms like SQL Server, PostgreSQL, MySQL, and MariaDB; the database handles this natively. The only things we see in the transaction logs are the things that have been committed, and so the connector is simply reading entry by entry, doing a conversion and dispatching the event on to your topic. For other connectors like Oracle, this depends on the technology interface you use on how the changes are managed.
When using Debezium with LogMiner, Debezium is responsible for buffering the transaction. This means we need to properly handle transaction management boundaries in a way that guarantees consistency. LogMiner has a number of quirks and behaviors that Debezium has to unfortunately manage if we expect to provide any reliable stream of changes. When you have long running transactions, this impacts the efficiency of LogMiner's ability to provide consistency. That transaction's start position needs to be included in the window for LogMiner to properly stitch state across the entire transaction.
One might think to use the new Debezium Unbuffered LogMiner mode we introduced several releaes ago; however it shares similar limitations. It still must read the transaction logs in the exact same way. The only technical difference is that rather than the memory buffer being managed by Debezium at the connector level, this buffer is managed at the LogMiner process level. And because memory management in Oracle databases are much more rigid and depends on what other processes are doing, you can easily face PGA_AGGREGATE_LIMIT exceptions and the connector will simply be able to generate the event stream. This often happens as a result of long running transactions or transactions that change millions of rows or rows with lots of large data columns.
This is where Debezium with Oracle XStream shines. The Oracle XStream technology provides access to continous mining, and the buffering and mining is handled transitively by the database. In this case, Debezium is simply a receiver of Oracle's payloads. In other words, Debezium with Oracle XStream makes the Oracle connector work more like you see with SQL Server, PostgreSQL, MySQL, and MariaDB. It's just a thin client that receives payload data, validates, transforms, and emits the events without any buffering. And since you have an existing Oracle GoldenGate license for this instance, that's why I am recommending it.
It's certainly something to explore if you cannot control these long running transactions that can take hours to finish.
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/CAAMG8XZUVg%3Drq1CdDhwaG51b%2BNtAtb%2Bw4-y8_uk2S%2BPb%3DNfrOQ%40mail.gmail.com.
Vinoth Kumar
Wanted to check how OpenLog Replicator (OpenSource) would handle this use case, especially with long-running transactions. Would it behave more like XStream, processing SCNs as transactions are committed or would it face similar limitations as LogMiner, waiting for transactions to complete?
Additionally, it would be very helpful if you could share your experience using OpenLog Replicator for the Oracle connector in production scenarios.
Chris Cranford
OLR buffers the transactions locally, but it does so using a similar continuous mining feature as XStream. As for production experiences, I know some members of the community have at least tried it in development, but I am not aware of anyone who has taken it to production.
-cc
To view this discussion visit https://groups.google.com/d/msgid/debezium/9660957d-8c1b-4cc0-858b-99b13659d3e4n%40googlegroups.com.