Oracle CDC Source Connector for Confluent Cloud Overview and 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.

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.

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.

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.

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.

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.

Manage custom offsets¶

You can manage the offsets for this connector. Offsets provide information on the point in the system from which the connector is accessing data. For more information, see Manage Offsets for Fully-Managed Connectors in Confluent Cloud.

To manage offsets:

• Manage offsets using Confluent Cloud APIs. For more information, see Cluster API reference.

GET /connect/v1/environments/{environment_id}/clusters/{kafka_cluster_id}/connectors/{connector_name}/offsets
Host: https://api.confluent.cloud

{
    "id": "lcc-example123",
    "name": "{connector_name}",
    "offsets": [
      {
        "partition": {
          "sidPdb": "ORCLCDB.ORCLPDB1"
        },
        "offset": {
          "scn": "2169287",
          "tablePlacement": ""
        }
      }
    ],
    "metadata": {
        "observed_at": "2024-03-28T17:57:48.139635200Z"
    }
}

POST /connect/v1/environments/{environment_id}/clusters/{kafka_cluster_id}/connectors/{connector_name}/offsets/request
Host: https://api.confluent.cloud

 {
     "type": "PATCH",
     "offsets": [
       {
         "partition": {
           "sidPdb": "ORCLCDB.ORCLPDB1"
         },
         "offset": {
           "scn": "2169287",
           "tablePlacement": ""
         }
       }
     ]
 }

{
    "id": "lcc-example123",
    "name": "{connector_name}",
    "offsets": [
      {
        "partition": {
          "sidPdb": "ORCLCDB.ORCLPDB1"
        },
        "offset": {
          "scn": "2169287",
          "tablePlacement": ""
        }
      }
    ],
    "requested_at": "2024-03-28T17:58:45.606796307Z",
    "type": "PATCH"
}

 POST /connect/v1/environments/{environment_id}/clusters/{kafka_cluster_id}/connectors/{connector_name}/offsets/request
 Host: https://api.confluent.cloud

{
  "type": "DELETE"
}

{
  "id": "lcc-example123",
  "name": "{connector_name}",
  "offsets": [],
  "requested_at": "2024-03-28T17:59:45.606796307Z",
  "type": "DELETE"
}

GET /connect/v1/environments/{environment_id}/clusters/{kafka_cluster_id}/connectors/{connector_name}/offsets/request/status
Host: https://api.confluent.cloud

{
   "request": {
      "id": "lcc-example123",
      "name": "{connector_name}",
      "offsets": [
        {
          "partition": {
            "sidPdb": "ORCLCDB.ORCLPDB1"
          },
          "offset": {
            "scn": "2169287",
            "tablePlacement": ""
          }
        }
      ],
      "requested_at": "2024-03-28T17:58:45.606796307Z",
      "type": "PATCH"
   },
   "status": {
      "phase": "APPLIED",
      "message": "The Connect framework-managed offsets for this connector have been altered successfully. However, if this connector manages offsets externally, they will need to be manually altered in the system that the connector uses."
   },
   "previous_offsets": [],
   "applied_at": "2024-03-28T17:58:48.079141883Z"
}