Data Platform

Debezium on Oracle: One Idle Transaction, One Log Purge, and a Connector That Can't Resume

· 8 min read

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.

Committed changes keep flowing past an open transaction, but the connector's restart point stays at the transaction's start. Every LogMiner session starts there, so the archived logs from that point on are still needed. Delete them, and the next session fails.

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_DATE in V$TRANSACTION) is a direct warning: the connector needs every log since then. So is an offset scn that stops moving while commit_scn advances.
  • 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 scn below the open transaction’s START_SCN while commit_scn passes 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 with when_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_scn but restarts, and starts every mining session, from scn. One idle transaction keeps that in the past.
  • Archived-log deletion based only on backups or age. Add the connector’s offset scn to 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 scn is gone, the way back is a new snapshot. Plan for it before it’s needed.
  • DDL as SYS. A column added by SYS or SYSTEM stopped 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