Oracle to Kafka with Debezium: The Connector Says RUNNING. The Data Is Wrong.
You move a database off Oracle, or feed a lakehouse from it, with change data capture: Debezium reads the redo
through LogMiner and writes every committed change to Kafka. The connector’s status is RUNNING. Lag is low. A
week later someone compares the target with the source and finds rows that never existed, notes that are empty or
contain the text __debezium_unavailable_value, and amounts that read "J0I=".
None of that came with an error. Each one comes from a default, or from one missing ALTER TABLE, and each one is
easy to miss in a test with small rows and a handful of updates.
This post shows the four, with the same table and the same changes: what each looks like in the event, why it happens, how to find it in your own pipeline, and the fix. It ends with a lab that runs Debezium 3.7 against Oracle Database Free 26ai and proves each one. The next post covers the other way Debezium surprises a DBA: the connector that runs fine until it restarts.
The setup
Debezium’s Oracle connector with the LogMiner adapter (the default) needs the database in ARCHIVELOG mode, a
common user with the LogMiner grants from the
documentation, and minimal supplemental
logging at the database level:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
The lab registers each connector with the documented minimum and nothing else, so everything below is a default unless it says otherwise:
{
"connector.class": "io.debezium.connector.oracle.OracleConnector",
"database.hostname": "oracle", "database.port": "1521",
"database.user": "c##dbzuser", "database.password": "...",
"database.dbname": "FREE", "database.pdb.name": "FREEPDB1",
"topic.prefix": "lab",
"table.include.list": "APP.ORDERS",
"schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
"schema.history.internal.kafka.topic": "lab.history"
}
Kafka Connect uses the JSON converter with schemas.enable=false, which is what many consumers that read JSON
expect. The table:
CREATE TABLE app.orders (
id NUMBER(10) PRIMARY KEY,
customer VARCHAR2(40) NOT NULL,
amount NUMBER(10,2),
qty NUMBER,
status VARCHAR2(12),
note CLOB
);
1. An update for a row that doesn’t exist
The lab creates the table three times. The copies are identical except for table-level supplemental logging:
ORDERS has (ALL) COLUMNS, ORDERS_PK has (PRIMARY KEY) COLUMNS, ORDERS_MIN has none, only the
database-level minimal logging the connector needs to start. Then the same statement runs on each:
UPDATE orders SET status = 'PAID' WHERE id = 1;
What arrived in Kafka, as the after image and the message key:
table supplemental logging key ID CUSTOMER AMOUNT STATUS
ORDERS (ALL) COLUMNS 1 1 ACME 100.50 PAID
ORDERS_PK (PRIMARY KEY) COLUMNS 1 1 '' null PAID
ORDERS_MIN none 0 0 '' null PAID
Only the first one is right. With minimal logging alone, the redo for an update records the column that changed
and the row’s address, not the rest of the row. LogMiner can only rebuild what’s in the redo, so Debezium gets
STATUS = 'PAID' and nothing else. In the lab it filled the NOT NULL columns with a default, 0 for ID and an
empty string for CUSTOMER, and left the nullable ones null.
For a consumer that upserts by key, that’s an instruction to write a row with ID 0. For one that merges the after
image into a lakehouse table, it’s an instruction to blank out every column the update didn’t touch.
(PRIMARY KEY) COLUMNS gets the key right and still blanks the rest.
The connector stays RUNNING. The only signal is one line per table in the Kafka Connect log, when streaming
starts:
WARN Database table 'FREEPDB1.APP.ORDERS_MIN' not configured with supplemental logging "(ALL) COLUMNS";
only explicitly changed columns will be captured. Use: ALTER TABLE APP.ORDERS_MIN ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
The fix is the statement in the warning, for every captured table, before the connector starts:
ALTER TABLE app.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
It increases redo for those tables, because every update now logs the whole row. Database-wide (ALL) COLUMNS
logging also covers it, at the cost of more redo for every table. To find captured tables that don’t have it:
SELECT t.owner, t.table_name
FROM dba_tables t
WHERE t.owner = 'APP' -- the schemas in table.include.list
AND NOT EXISTS (SELECT 1
FROM dba_log_groups g
WHERE g.owner = t.owner
AND g.table_name = t.table_name
AND g.log_group_type = 'ALL COLUMN LOGGING');
A table that someone adds to the include list later, or recreates during a deploy, needs it too: the log group belongs to the table, and a new table doesn’t have one.
2. Large CLOB changes that never arrive
lob.enabled defaults to false. The
documentation says that by default
“change events have large object columns, but the columns contain no values.” What happened in the lab is less
consistent than that, which is why it’s easy to miss:
change (each in its own transaction) what arrived (lob.enabled=false)
UPDATE: NOTE 'first order' -> 8,000 characters nothing at all
UPDATE: NOTE null -> 8,000 characters an update with NOTE null
INSERT with NOTE 8,000 characters an insert with NOTE null
INSERT with NOTE 'hello' an insert with NOTE null, then an update with 'hello'
A later update to another row, in another transaction, arrived normally, so the first change wasn’t late. It was never sent. The short value did arrive, as two events. A test with a few short notes passes; production data with long ones doesn’t.
With lob.enabled=true, the same 8,000-character update arrived as one event with all 8,000 characters, and so did
an insert.
The fix is "lob.enabled": "true" for any table with a CLOB, NCLOB or BLOB you need downstream, or a
column.exclude.list for the ones you don’t. The documentation warns of extra processing overhead, and the
connector holds every transaction in memory until it commits, LOB data included. It also rules out log.mining.strategy=hybrid: the documentation says that
combination is unsupported and the connector won’t start.
3. The placeholder that becomes data
Now an update that doesn’t touch the CLOB at all:
UPDATE orders SET status = 'SHIPPED' WHERE id = 1; -- NOTE is 'first order'
With lob.enabled false and with it true, the event’s after image had:
"NOTE": "__debezium_unavailable_value"
This one is documented and isn’t a bug: Oracle only logs a LOB’s value when a statement sets it, so the connector
doesn’t have the value for an update that didn’t. It sends a placeholder instead
(unavailable.value.placeholder). A consumer that writes the after image as it is will replace a real note with
that string.
The fix is in the consumer, not the connector: treat the placeholder as “unchanged” and keep the target’s
current value. In the target’s MERGE (the syntax varies by platform), that’s a CASE per LOB column:
note = CASE WHEN s.note = '__debezium_unavailable_value' THEN t.note ELSE s.note END
Check the sink you use for how it handles this before relying on it.
4. NUMBER as base64
The snapshot event for row 1, with the default decimal.handling.mode=precise:
"AMOUNT": "J0I=",
"QTY": {"scale": 0, "value": "Aw=="}
AMOUNT is NUMBER(10,2) and the value is 100.50. Kafka Connect’s Decimal type sends the unscaled value as
bytes: 10050, base64-encoded. The scale (2) is in the message schema, and schemas.enable=false drops the schema,
so a consumer reading this JSON can’t turn it back into 100.50. QTY is an unconstrained NUMBER, whose scale can
differ per row, so it arrives as a structure with its own scale: row 2’s 1234567.891 came as scale 3.
With "decimal.handling.mode": "string":
"AMOUNT": "100.50", "QTY": "3" (row 2: "250.00", "1234567.891")
The fix is to choose one on purpose: string keeps every digit and is readable by anything; precise is
fine when the consumer gets the schema (Avro or JSON with a schema registry, or schemas.enable=true). Avoid
double for money: it’s a binary floating-point number.
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, all public images. Each drill registers its own connector, takes the initial snapshot, makes changes in Oracle and reads the topic:
drill what happens what arrives in Kafka
supplog UPDATE STATUS, table with (ALL) COLUMNS key 1, the full row
same, (PRIMARY KEY) COLUMNS key 1, CUSTOMER '', AMOUNT null
same, no table-level supplemental logging key 0, CUSTOMER '', AMOUNT null;
status RUNNING, one WARN in the log
lob lob.enabled=false: UPDATE a CLOB to 8,000 chars no event
UPDATE a null CLOB to 8,000 chars update, NOTE null
INSERT with 8,000 chars insert, NOTE null
INSERT with 'hello' insert, then update with 'hello'
lob.enabled=true: UPDATE and INSERT, 8,000 chars one event each, 8,000 chars
placeholder UPDATE STATUS only (lob.enabled false and true) NOTE "__debezium_unavailable_value"
numbers precise (default), schemas off AMOUNT "J0I=", QTY {scale, value}
decimal.handling.mode=string "100.50", "3", "1234567.891"
Don’t take my word for it — run it. The debezium-cdc lab asserts every row: the key and the columns of each table’s update event, that the connector is still
RUNNING, and Debezium’s warning in the log; that the 8,000-character update never arrives while a later change does, and that it arrives withlob.enabled=true; the placeholder in both modes; and the exact JSON of each number in both decimal modes. If any of that doesn’t reproduce, the run fails. Proven on every CI push.
What the lab doesn’t cover: the XStream and OpenLogReplicator adapters, Avro or a schema registry, and any particular sink connector. It tests what Debezium writes to the topic.
What teams get wrong
- Treating
RUNNINGas “correct”. The status says the task is alive. Compare a sample of target rows with the source after the first day of streaming, not only row counts. - Enabling supplemental logging for the database only.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATAlets the connector start. Each captured table still needs(ALL) COLUMNS, or the database-wide equivalent. - Testing with short values. A 5-character
CLOBarrives with the default settings. The ones that don’t are the long ones production has. - Writing the
afterimage as-is. Unchanged LOBs arrive as a placeholder string, by design. The consumer has to know that. - Turning off schemas without choosing a decimal mode. With
schemas.enable=false, the default decimal encoding can’t be decoded. Usestring, or keep the schema.
Frequently asked questions
Why does a Debezium Oracle update event have ID 0 or empty columns?
Because the table doesn't have (ALL) COLUMNS supplemental logging. With only the database-level minimal supplemental logging, the redo for an update holds the changed column, not the rest of the row, so LogMiner can't rebuild the key or the other columns. In the companion lab, the update event had key 0 and CUSTOMER as an empty string while the connector stayed RUNNING; Debezium logged one warning per table when streaming started. Run ALTER TABLE ... ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS for every captured table.
Why are CLOB values missing from Debezium Oracle change events?
lob.enabled defaults to false. In the companion lab with Debezium 3.7, an update that set a CLOB to 8,000 characters produced no event at all, and inserts arrived with the CLOB null, while a 5-character value did arrive. Set lob.enabled to true for tables whose LOBs you need; the documentation says it can't be combined with the hybrid mining strategy.
What does __debezium_unavailable_value mean?
It's the placeholder Debezium sends for a value it doesn't have. On Oracle, an update that doesn't set a CLOB, NCLOB or BLOB column doesn't log that column's value, so the event carries the placeholder in its place, with lob.enabled true or false. The consumer has to treat it as 'unchanged' and keep the current value, for example with a CASE in its MERGE.
Why does Debezium send Oracle NUMBER values as base64 strings like J0I=?
With the default decimal.handling.mode=precise, a NUMBER(p,s) is a Kafka Connect Decimal: the unscaled value as bytes, which JSON shows as base64. 'J0I=' is 10050, which is 100.50 at scale 2, and the scale is only in the message schema. With schemas.enable=false the consumer never sees it. Set decimal.handling.mode to string, or keep the schema with the message.
Debezium on Oracle is only as complete as the redo it reads. Log the whole row for every captured table, turn on
LOB capture where the LOBs matter, teach the consumer what the placeholder means, and pick a decimal mode the
consumer can read. Then check the target against the source, not the connector’s status. Prove each one with the
debezium-cdc lab, where an UPDATE of one
column turns into an event for row 0 and a status that still says RUNNING.
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