Features

The Connect Oracle CDC Source connector includes the following features:

Redo log topic

The connector reads the Oracle database redo log and writes each raw redo log event as a separate Kafka record. The connector queries the V$LOGMNR_CONTENTS view. Each row in the result set that applies to one of the matched tables is converted to records with a field for each column in the result set. The connector will write to this topic using an at-least-once guarantee. The connector may rewrite a portion of the redo log event records upon restart.

To access redo log topics, you must grant the connector a corresponding operation–that is, CREATE, READ, or WRITE in an ACL. For output topics, you must grant the connector either CREATE or WRITE in an ACL. When granted READ, WRITE, or DELETE, the connector implicitly derives the DESCRIBE operation. See Oracle CDC Source connector ACLs for additional information and ACL examples.

Caution

  • Redo log topics should not be shared among connectors–this may cause unexpected behavior.
  • Currently, the connector only supports writing to the redo log topic with one partition.
  • Confluent recommends you increase your log retention policies to 24 hours. If you have a shorter retention policy and your table doesn’t have many activities, the connector might not be able to find a record with the last committed SCN.
  • When a connector is in the running state, you may not see a redo log topic being created (redo.log.topic.name). The connector will create the topic when a new INSERT, UPDATE, or DELETE is committed to the tables the connector is configured for. You may also create the topic with one partition before you run the connector.

Redo log corruption topic

It is possible that the Oracle redo logs themselves may be corrupted. The connector will not terminate if LogMiner reports corrupted blocks or segments. It is possible to configure the connector to write the corrupted block details to a separate Kafka topic, allowing downstream consumers to use this information to track and react to Oracle redo log file corruptions.

Table change event topics

The connector can turn raw logs into change events for each table and write these to Kafka topics using the configured table-to-topic mapping.

Pattern match tables to be captured

The connector configuration uses two regular expressions to identify the tables in the database that it should capture. The connector captures events from all tables in the database whose fully-qualified names (for example, <database-name>.<schema-name>.<table-name>) are matched by the include expression, unless explicitly excluded by matching the exclude expression.

Flexible mapping of tables to Kafka topics

The connector configuration specifies a template that identifies the names of the Kafka topic to which the events are written. This template is resolved into a name for every change event and can use literals or template variables including the schema name, table name, database name, various timestamps, and transaction IDs. This gives users a flexible way of identifying the names of the Kafka topics where the change events are written.

Record keys

The records that the connector writes to Kafka topics have (by default) a key corresponding to the primary key column values for the corresponding row in the database. If the primary key consists of a single column, the Kafka record’s key will contain the value of the column for that row. If the primary key consists of multiple columns, the Kafka record’s key will be a STRUCT containing a field for each of the primary key’s columns. You can change this behavior by setting the key.template configuration property. As with other Connect source connectors, each record’s key determines the topic partition where the connector writes the record.

Snapshots

When you first start a connector, it attempts to obtain a snapshot of all existing rows in each table, writing these (as records) to the Kafka topic for the table, before starting to capture changes made to those rows. This results in the Kafka topic containing records for every row in the database table. However, if the Kafka topic should only contain records from a specific point in time, you can use the start.from configuration property to specify an SCN or timestamp. This will set the point where the connector will start capturing events for all tables.

Note

If the connector is interrupted or stopped, or fails while performing a snapshot of any tables upon recovery or restart, the connector restarts all incomplete snapshots from the beginning. Unfortunately, it is currently not possible to resume a snapshot of a table that is changing while ensuring that all changes to that table have been captured.

Table partition snapshots

The connector has an advanced feature of table partition snapshots. With this feature, the connector performs snapshots, in parallel, of large tables that are partitioned in Oracle, and distributes these table-partition snapshots across all tasks. This helps to scale the number of tasks linearly, so more snapshots are performed in parallel across a larger number of tasks. For example, a connector can capture and snapshot a single large table (N=1) with many table partitions (for example, P=20) using up to P+1 tasks. This reduces the overall time required to perform the snapshot by scaling out the number of tasks.

Note

When running a connector with snapshot.by.table.partitions=true, create table-specific topics ahead of time. If table-specific topics are not created ahead of time, some tasks assigned to partitioned tables will fail. In addition, ensure target tables are all partitioned. If you have a mix of non-partitioned tables and partitioned tables, use snapshot.by.table.partitions=false To view the property description, go to snapshot.by.table.partitions.

Large object types

You can configure the connector to capture changes in tables that contain columns with binary large object (BLOB), character large object (CLOB), and national character large object (NCLOB) types. These large object (LOB) types are written to separate LOB topics that can be consumed by downstream applications. To enable this feature, specify a template variable to use in the lob.topic.name.template configuration property (see Template variables for supported variables). When enabled, the connector writes LOB objects to a separate topic with the key of the topic, consisting of the table full name, column name, and primary key of the change record of the LOB object row.

Note

  • A table that contains large object (LOB) type columns must include primary keys.
  • LOB objects of more than 1 KB in size are supported. Ensure you set enable.large.lob.object.support to true.
  • Be careful when updating the value of primary keys when used in association with LOB topics. When an update to the primary key is processed, the connector will emit the updated record to the change event, but will not retroactively update the LOB record key.

Auto-table set sync and task reconfiguration

Tables can be deleted and created in the Oracle database while the connector is running. The connector periodically checks for newly added or recently dropped tables that match the tables to be captured. When the connector identifies new or deleted tables, the connector automatically reconfigures its tasks to stop watching the deleted tables and begin capturing changes.

Scalable database workloads

The connector is designed to scale from small to large database workloads using connector tasks. The connector can be configured to use as few as one task (tasks.max=1) or scale to as many tasks as required to capture all table changes.

Micro-rebalancing of task loads

Upon startup, the connector evenly distributes tables across its tasks. The connector monitors throughput variations for each table and the position of each task in the redo log. The connector automatically attempts to distribute the load across all of the connector’s tasks by assigning frequently-changing tables to different tasks.

Automatic creation of Kafka topics

You can include rules in your connector configuration that define the topic settings for any topic that the source connector writes to.

Note

When running a connector with snapshot.by.table.partitions=true, create table-specific topics ahead of time. If table-specific topics are not created ahead of time, some tasks assigned to partitioned tables will fail. In addition, ensure target tables are all partitioned. If you have a mix of non-partitioned tables and partitioned tables, use snapshot.by.table.partitions=false To view the property description, go to snapshot.by.table.partitions.

Automated reconnection

The connector is able to automatically reconnect when the connection to the database is disrupted or interrupted. When a connection is lost, the connector stops, logs a disconnection warning or error messages, and attempts to reconnect using exponential backoff. Once the connection is re-established, the connector automatically resumes normal operation. A few connection properties control this behavior, including query.timeout.ms (defaults to 5 mins).

Oracle multi-tenant CDB/PDB architecture support

Oracle provides multitenant architecture support in Oracle Database 12c and above. System tables are stored in a single container database (CDB). User tables are stored in pluggable databases (PDBs) plugged into the CDB. Each instance of our connector can read user tables that reside in one PDB. The PDB name where user tables reside can be configured using the property oracle.pdb.name. To read from system tables in the CDB, or to read from legacy 11g database, leave the oracle.pdb.name configuration property blank. The oracle.sid property must be set to the Oracle system identifier (SID) to access either CDB, PDB, or legacy non-multitenant database.

Manage long-running transactions

This section is applicable only when the configuration use.transaction.begin.for.mining.session is set to true.

A long running transaction can have a negative impact on the connector performance. It can cause each subsequent mining session to do more work and hence take more time to complete, leading to increased lag in processing change events. It can also impact the memory usage of the connector. The connector stores events corresponding to a transaction in an in-memory buffer until it receives the commit or rollback event for the transaction. A long-running transaction having a large number of changes leads to increased memory usage that could eventually result in an out-of-memory error if there is an insufficient amount of available memory.

As a general guidance, Confluent recommends you avoid long-running transactions, since it can impact the scalability of the system and could lead to deadlocks. It is best to change applications to not have these long-running transactions.

In the case where changes to an application is not feasible, consider using the log.mining.transaction.age.threshold.ms and log.mining.transaction.threshold.breached.action configurations to drop long-running transactions. Use these properties with caution since dropping transactions could result in potential data loss.

Use the log.mining.transaction.age.threshold.ms configuration to define a threshold (in milliseconds) for transaction age. Transaction age is defined as the duration the transaction has been open on the database. If the transaction age exceeds this threshold then an action is taken depending on the value set for the log.mining.transaction.threshold.breached.action configuration. The default value is -1, which means that a transaction is retained in the buffer until the connector receives the commit or rollback event for the transaction. This should not be set to a value that is more than the retention period for archived redo log files.

Use the log.mining.transaction.threshold.breached.action configuration to control the action to take when an active transaction exceeds the threshold defined using the log.mining.transaction.age.threshold.ms configuration. Possible options are: discard and warn (default). Use discard to drop long running transactions that exceed the threshold age from the buffer and skip emitting any records associated with these transactions. Use warn to log a warning, mentioning the oldest transaction that exceed the threshold.

Archived redo log destination

The connector provides a configuration to specify the archived redo log destination used to read the redo log files. Use the log.mining.archive.destination.name configuration to specify the name of the log archive destination to use when mining archived redo logs. You can configure the connector to use a specific destination using the destination name, for example, LOG_ARCHIVE_DEST_1.