Debezium on Oracle: One Idle Transaction, One Log Purge, and a Connector That Can't Resume
The CDC pipeline from Oracle to Kafka has run for weeks with near-zero lag. One night the Debezium task fails:
LogFileNotFoundException: None of the log files contain offset SCN: 2303670 after 6 attempts.
This could be due to a slow archiver process in the database.
Someone restarts it, and it fails again, differently:
DebeziumException: The connector is trying to read change stream starting at OracleOffsetContext [scn=2303670, ...],
but this is no longer available on the server. Reconfigure the connector to use a snapshot mode when needed.
The archiver isn’t slow. The connector wasn’t behind. A session somewhere opened a transaction and never ended it, and the nightly RMAN job deleted the archived logs after the backup, as it always does. That combination is enough.
This post shows why, with the same table and the same changes: how Debezium decides where to read from, how to find
the transaction that holds it back, a purge that’s safe, the retention setting that looks like a fix and drops
committed data, and one more way to stop the connector: DDL as SYS. It ends with a lab on Debezium 3.7 and Oracle
Database Free 26ai that proves each step. The previous post covered the
other half: a connector that runs and delivers wrong data.
Two SCNs in the offset
Debezium’s LogMiner adapter keeps every transaction in memory until it sees the commit, then sends its changes, in commit order. A rollback sends nothing. Its offset, stored in Kafka, has two positions:
{"scn": "2291852", "commit_scn": "2291928:1:...", ...}
commit_scn: how far it has delivered. Everything committed up to here is in Kafka.scn: where it has to start reading to rebuild the transactions that are still open. It can’t be later than the start of the oldest open transaction, because that transaction’s first changes are behind it.
In the lab, a session updated one row and then sat idle with the transaction open. Three more changes were committed in other sessions, with log switches between them. All three arrived in Kafka, and the offset said:
open transaction START_SCN = 2291853
offset scn = 2291852 <- restart from here
offset commit_scn = 2291928 <- delivered up to here
scn stayed just below the open transaction’s start, while commit_scn moved on. When the transaction ended (it
rolled back), scn moved forward on its own.
Finding the transaction
The session doesn’t have to be doing anything. In the lab it was INACTIVE: a tool that ran an update and was
left open, or a pooled connection that never committed.
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.status,
t.start_scn,
ROUND((SYSDATE - t.start_date) * 86400) AS open_seconds,
s.last_call_et AS idle_seconds
FROM v$transaction t
JOIN v$session s ON s.saddr = t.ses_addr
ORDER BY t.start_scn;
SID USERNAME STATUS START_SCN OPEN_SECONDS IDLE_SECONDS
64 APP INACTIVE 2294631 13 13
The first row is the one holding the connector back. Compare its START_SCN with the connector’s offset scn.
Ending it (commit, rollback, or killing the session, which rolls it back) is a decision for whoever owns that
session, not for the CDC team; the point is to find it before the logs are gone.
Then the logs are purged
With that transaction still open, the lab ran what a nightly job after a backup typically runs:
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
Right after the purge the connector was still RUNNING. At the next log switch the task failed with the first
error above. The SCN it couldn’t find was the restart position, just below the open transaction’s start. A change committed after the purge was in Oracle and never reached
Kafka. A restart failed with the second error.
The connector didn’t need the old logs only for a restart. Each new LogMiner session (the connector starts one
after a log switch) begins at the offset scn, so it needs every log from there on, archived or online, for as long
as that transaction stays open.
The way back is a new snapshot. With snapshot.mode set to when_needed, the connector took one on the next
restart and ran again: every row was sent again as a snapshot event (op: r), and the change committed during the
outage arrived only as part of that snapshot. Consumers have to handle a full reload, and anything that relied on
each individual change, not just the final state, missed the ones in between.
A purge that’s safe for the connector
Archived-log deletion has to respect the connector’s offset scn, not only the backup. Read the offset (Kafka
Connect’s REST API: GET /connectors/<name>/offsets), then delete only the logs that end at or before it:
SELECT MAX(sequence#)
FROM v$archived_log
WHERE deleted = 'NO'
AND next_change# <= :offset_scn;
RMAN> DELETE NOPROMPT ARCHIVELOG SEQUENCE BETWEEN 1 AND <that sequence>;
In the lab, with the same open transaction, that deleted the older logs and kept the ones that reach past the offset. A task restart resumed from the offset with no new snapshot, and the next change arrived. With more than one connector, or a standby that also needs the logs, use the lowest of their positions.
Two more things help:
- Alert on the gap. The age of the oldest open transaction (
START_DATEinV$TRANSACTION) is a direct warning: the connector needs every log since then. So is an offsetscnthat stops moving whilecommit_scnadvances. - Keep the logs longer than the longest transaction you tolerate. If the logs live for 24 hours and a session can stay open for 25, the purge will win.
The retention setting that drops committed data
log.mining.transaction.retention.ms looks like the fix: Debezium discards a transaction that’s been open longer
than this, so the offset can move past it. The catch is in the word discards. The documentation says the
operations of a discarded transaction are not emitted, and that includes transactions that commit later.
The lab set it to 30 seconds and ran a transaction that inserted a row and committed after 60:
in Oracle: 1 row with ID 9 (committed)
in Kafka: 0 events for ID 9
log: WARN Transaction 070001004b020000 (start SCN 2308569, ...) is being abandoned.
A row committed in the source and never delivered, with a WARN as the only trace. If you use it, set it well above
your longest legitimate transaction (the month-end batch, the large data fix), and alert on that warning.
DDL as SYS or SYSTEM stops the connector
With the default log.mining.strategy (online_catalog), the connector tracks a captured table’s columns in its
schema history, from the DDL it reads in the redo. In the lab, ALTER TABLE app.orders ADD (...) was run four times,
by four accounts, each followed by an insert that used the new column:
DDL run by next insert
APP (the owner) delivered, connector RUNNING
DEPLOYER (a separate account, ALTER ANY TABLE) delivered, connector RUNNING
SYSTEM task FAILED: DmlParserException ... ArrayIndexOutOfBoundsException
SYS task FAILED, the same way
The first row with the new column couldn’t be parsed: the connector’s model of the table still had the old column list. A restart failed the same way. In the lab, what ran again was a new connector with a new snapshot.
The 3.7.1 connector has a setting, log.mining.schema_changes.username.exclude.list, described as “A
comma-separated list of usernames that schema changes will be skipped for. Defaults to ‘SYS,SYSTEM’.” I didn’t find
it on the connector’s documentation page. It looks like the explanation, but it isn’t the whole one: with it set to
another user, DDL by SYS still stopped the connector in the lab. What the lab does show is the pattern.
Run DDL on captured tables as the owner or a deploy account. A DBA’s quick fix as SYS is exactly the case that
breaks it.
Prove it
The lab runs Oracle Database Free 26ai (23.26.3), a single-node Kafka 4.3.1 and Kafka Connect with Debezium 3.7.1.Final. Each drill registers its own connector and makes its changes in Oracle:
drill what happens result
pinned idle session holds an open transaction; 3 commits all 3 delivered; offset scn < START_SCN,
commit_scn past it; scn moves after rollback
purge same, then RMAN DELETE ARCHIVELOG ALL RUNNING until the next log switch, then FAILED:
"None of the log files contain offset SCN";
a later commit not delivered; restart fails:
"no longer available on the server"
snapshot.mode=when_needed RUNNING after a new snapshot (op=r again)
safepurge delete only logs with NEXT_CHANGE# <= offset scn restart resumes, no new snapshot
retention retention.ms=30000; a 60-second transaction commits row in Oracle, not in Kafka; "is being abandoned"
sysddl ADD COLUMN as APP, then as DEPLOYER next inserts delivered
ADD COLUMN as SYSTEM; as SYS (new connector) FAILED: DmlParserException; restart fails
SYS, exclude list set to another user still FAILED
Don’t take my word for it — run it. The debezium-cdc lab asserts every row: the offset
scnbelow the open transaction’sSTART_SCNwhilecommit_scnpasses it; the task failing at the next log switch with the restart SCN in the message, the undelivered change and the failed restart; the new snapshot withwhen_needed; the clean restart after the safe purge; the committed row that never arrives with transaction retention; and the DDL cases. If any of that doesn’t reproduce, the run fails. Proven on every CI push.
What the lab doesn’t cover: RAC, a standby that also needs the logs, the unbuffered and XStream adapters, and how long a real mining session takes when the offset is hours behind on a busy database.
What teams get wrong
- “The connector isn’t behind, so it doesn’t need old logs.” It delivers up to
commit_scnbut restarts, and starts every mining session, fromscn. One idle transaction keeps that in the past. - Archived-log deletion based only on backups or age. Add the connector’s offset
scnto the rule. - Believing the error message. “Slow archiver” was a deleted log in the lab.
- Using transaction retention as the fix. It moves the offset by dropping the transaction, committed or not.
- Restarting and hoping. Once the log at the offset
scnis gone, the way back is a new snapshot. Plan for it before it’s needed. - DDL as SYS. A column added by
SYSorSYSTEMstopped the connector at the next row that used it. Use the owner or a deploy account.
Frequently asked questions
What does 'None of the log files contain offset SCN' mean in the Debezium Oracle connector?
That the redo or archived log containing the connector's offset scn no longer exists. The message suggests a slow archiver, but in the companion lab the cause was RMAN deleting the archived logs while a transaction was open. The connector's offset scn stays at the start of the oldest open transaction, and every new LogMiner session starts there, so the task failed at the next log switch.
Why does Debezium say the offset is 'no longer available on the server' after a restart?
The log at the stored offset scn has been deleted, so the connector can't resume from it. A restart doesn't help. In the companion lab, the way back was snapshot.mode=when_needed, which took a new snapshot and sent every row again. To prevent it, don't delete archived logs past the connector's offset scn, and find long open transactions in V$TRANSACTION.
Why is the Debezium offset scn much older than commit_scn?
Because a transaction is still open. commit_scn is how far changes have been delivered; scn is where the connector must start reading to rebuild the transactions still in progress, so it can't pass the start of the oldest open one. In the companion lab, an idle session's open transaction kept scn just below its START_SCN while commit_scn moved on.
Is log.mining.transaction.retention.ms safe to use?
Only with a value longer than any legitimate transaction. Debezium discards a transaction that's been open longer than the setting, and doesn't emit it even if it commits later. In the companion lab, with 30 seconds, a transaction that committed after 60 seconds was in Oracle and never reached Kafka; the only trace was a 'being abandoned' warning.
Why did DDL by SYS break the Debezium Oracle connector?
In the companion lab with Debezium 3.7.1, a column added by SYS or SYSTEM never reached the connector's model of the table, and the next insert that used it failed with a DmlParserException; a restart failed the same way, and a new connector with a new snapshot ran again. The same DDL by the owner or by a deploy account with ALTER ANY TABLE worked. The connector has a setting, log.mining.schema_changes.username.exclude.list, that defaults to SYS,SYSTEM, but changing it didn't fix this in the lab. Run DDL on captured tables as the owner or a deploy account.
A Debezium connector on Oracle is only as safe as the oldest open transaction and the log retention behind it. Know
where its offset scn is, find the session that’s holding it, delete archived logs below it and not past it, and
keep DDL away from SYS. Then prove it with the debezium-cdc
lab, where one idle session and one routine RMAN
command take down a connector that was fully caught up.
Have a question or some feedback?
I write here in a personal capacity and enjoy comparing notes with other Oracle folks. Say hello.
Get in touch