Overview
A retail bank streamed changes from an Oracle core system into Kafka with the Debezium Oracle connector on Kafka Connect. Every night a settlement batch wrote large transactions, and the connector fell hours behind. Downstream reconciliation started late, and twice the archived redo logs aged out before the connector reached them.
Challenge
The Oracle connector reads changes through LogMiner, and its cost depends on the mining window, the size of open transactions and how often it starts a new mining session. The batch opened transactions that stayed open for a long time, so the connector buffered them and re-read the same redo. Lag recovered slowly in the morning and nobody could say which setting to change without risking a gap.
Environment
Oracle 19c on-premises in ARCHIVELOG mode with supplemental logging; Kafka Connect on Kubernetes in the bank's private cloud; Debezium 2.x Oracle connector; Prometheus alerts on connector JMX metrics.
Approach
AceMQ measured the lag by phase against the batch schedule, using the connector's lag, active transaction and oldest SCN metrics, and matched it to archive log generation on the database. Changes were made in the order that carried least risk: batch commit behaviour with the DBAs first, mining settings second, and alerting on retention last. Each change was rehearsed on a restored copy before production.
Solution
- Correlated MilliSecondsBehindSource and active transaction metrics with the nightly settlement batch to find where the lag began
- Worked with the DBAs to make the batch commit in smaller units so long-running transactions stopped holding the mining window open
- Tuned log.mining.batch.size.max and the sleep-time settings so the connector kept pace during peak redo generation
- Reviewed the log.mining.strategy choice against the schema change rate on the captured tables
- Extended archive log retention to cover the longest outage the bank had to survive and added an alert before the oldest unread log was due to be purged
- Wrote a runbook for a lagging or restarted Oracle connector, including when a new snapshot is the only safe answer
Outcome
Peak lag during the nightly batch fell from about three hours to under twenty minutes, and the pipeline drained before reconciliation started. Archive log retention now has headroom and an alert, so the earlier gaps have not recurred.
Technologies
Kafka
Kubernetes
RabbitMQ
Prometheus
Helm
VMware vSphere
Terraform
