I’m fairly certain that at some point most Oracle connector users have seen this error in the logs, whether during PoC design or testing, but hopefully not production. The error message itself is relatively clear, but what is often not clear is "why did this happen".

For other databases, their transaction logs only contain committed changes that are then consumed by Debezium. Unfortunately, Oracle does not do this and instead writes every single change to the transaction logs, even if the change is later rolled back due to a constraint violation or an explicit user or system rollback. This means that reading the changes from the Oracle redo and archive logs isn’t as simple as reading from position X until the end of the file and then repeat with the next log in sequence. Instead, the connector must maintain what we call a low and high watermark SCN, or if you’re familiar with the connector’s offsets these are represented as scn and commit_scn.

The low watermark or scn represents the safe resume point in the redo logs. Generally this points to the position in the logs where the oldest in-progress transaction started. The high watermark or commit_scn represents the position in the logs where we last emitted a transaction batch for a given redo thread. This means that the changes in between these two values are a mixture of uncommitted changes, committed changes, or rolled back changes.

When the connector starts, the low watermark or scn read from the offsets is compared to the oldest available archive log in Oracle. If the archive log begins with a system change number that comes after this scn value, this error will occur.

Long-running transactions directly impact the low watermark or scn position. If a transaction remains active for longer than your archive log retention policy and the connector is restarted due to a re-balance or failure, this error can occur. If you suspect long-running transactions, you can configure the log.mining.transaction.retention.ms property in order to discard a transaction that lives longer than the specified value. While this does cause data loss as that transaction’s changes are discarded, it does allow the low watermark to safely advance forward at a reasonable pace even when long-running transactions occur. You should set the transaction retention period to a value less than your archive log retention period.

Another use case that can raise this error is if you are capturing changes from an Oracle database with a low volume of changes. In particular, if you are using an older version of Debezium where the LogMiner query applied database-level filters or you’ve configured the new query filter mode to apply database-level filters, it’s possible that the connector may go extended periods of time without observing a single change event. Since offset data only synchronizes when the connector sends an event to Kafka, low volumes of changes in a large window of time can mean those Kafka offsets become stale and if a restart occurs, this error could happen. In this use case, configuring both the heartbeat.internval.ms and heartbeat.action.query connector properties is a great way to guarantee that there is some activity flowing to Kafka to keep those offsets from becoming stale.

This specific error is most commonly observed during the connector’s initial snapshot. The Oracle connector relies on what is called flashback queries during the initial snapshot phase.

A flashback query is a standard SELECT statement that uses a system change number in order to generate a result-set based on the state of the data at that given point in the database’s lifetime. This can be useful for a variety of reasons, including being able to restore objects without the need for media recovery because Oracle is capable of retaining that previous state for a certain period of time. The data returned by these queries use the Automatic Undo Management (AUM) subsystem and rely on the undo data area where transactions are recorded and retained for a given period of time, configurable based on the database parameter UNDO_RETENTION.

If the SCN used for the flashback query becomes too old and the undo retention no longer maintains historical data for that system change number, Oracle will report an ORA-01555 error that the snapshot is now too old. When this happens during your initial snapshot, the snapshot will need to be retaken from the beginning and unless you reconfigure Oracle’s undo retention period to allow for a longer retention time, rerunning the snapshot on the same data set will result in the same outcome.

So either a) have your DBA increase the UNDO_RETENTION database parameter temporarily or b) use a schema-only snapshot and then rely on incremental snapshots to generate the initial data set from your existing table data.

Occasionally users will notice there is a latency when the connector first starts or at specific periods during the connector’s lifetime. One of the best ways to identify what is going on is to coordinate with your DBA and to take a close look at your database’s alert log, which records all the LogMiner and XStream interactions that Debezium performs with your database. But most often this latency is quite common for users who use the default log mining strategy.

The default mining strategy that we covered earlier performs a what is called a data dictionary build step and depending on your database, this can take some time to be written to your redo logs and then parsed by the LogMiner process. It’s not uncommon for this to take 30 seconds up to several minutes to complete, and when using the default mining strategy, this process occurs on each log switch.

So we normally suggest that if you experience this latency frequently that you check the frequency of your log switches. If your database is performing excessive log switches within a small window that does not adhere to Oracle’s guidelines, your DBA may need to tune the database accordingly. Reducing the frequency of log switches increases the time that Debzium can reuse the same log file for mining and therefore reduces the need to build and parse the data dictionary.

If your table schema does not change often or won’t change at all, you can reconfigure the connector to use the online_catalog mining strategy as an alternative. This avoids the writing the data dictionary to the redo logs and the parse phase performed by LogMiner, greatly increasing the speed at which a mining session begins at both connector start-up and at each log switch interval.

Database administrators typically keep archive logs on the database server for a short period of time before they’re removed. This interval varies and depends on a number of factors including the frequency the logs are created, their respective sizes, and the physical space available on the server. What is most important is that if Debezium requires a specific archive log that it remains available until it’s no longer needed for CDC.

The easiest way to determine what logs are needed is via JMX metrics, looking at the field OffsetScn. This field references the system change number where the connector will resume from in case of a restart, and so any archive or redo log that contains this system change number or comes after this change number must remain available.

Debezium 2.4 will add another JMX metric that will provide the cut-off timestamp for archive logs based on this OffsetScn. This means that you will be able to use this timestamp directly in shell scripts to compare the filesystem’s timestamp with the one from JMX metrics, safely knowing which logs can must be retained and which can safely be removed via RMAN.

Due to the nature of how transaction data is written to the Oracle archive and redo logs, a buffer of the transaction state must be managed by the connector. Under ideal circumstances, this buffer maintains short-lived data, the transaction starts, we buffer its relevant changes, and we observe the rollback or commit, and the data managed in the buffer is handled and the buffer cleared.

Because the connector buffers transactions, it’s extremely important that you have some prior knowledge of your environment’s transaction patterns. If this information can vary and cannot be predicted, you may want to consider using an alternative buffer type than the default Heap (memory) based buffer as this can easily lead to OutOfMemory exceptions under these circumstances when memory is configured too low.

Please refer to the documentation about Event Buffering. The Oracle connector offers two Infinispan based solutions that allow the connector store the buffer off-heap, reducing the connector’s memory footprint and being capable of dealing with very large transactions seamlessly.