# Oracle CDC Source Connector for Confluent Cloud Overview and Features The fully managed Oracle CDC Source connector for Confluent Cloud captures each change to rows in a database and then represents the changes as change event records in Apache Kafka® topics. ## 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](https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1154.htm) 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](../service-account.md#oracle-cdc-source-acls) for additional information and ACL examples. Note the following: - 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, `..`) 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](cc-oracle-cdc-source.md#connect-oracle-cdc-source-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](cc-oracle-cdc-source.md#cc-oracle-cdc-source-config-properties). 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](cc-oracle-cdc-source.md#cc-oracle-cdc-source-config-properties) to specify an SCN or timestamp. This will set the point where the connector will start capturing events for all tables. 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. When running a connector with `snapshot.by.table.partitions=true`, follow these requirements: - Create table-specific topics ahead of time. If these are not created, tasks assigned to partitioned tables will fail. - Ensure all target tables are partitioned. - Use `snapshot.by.table.partitions=false` if your source includes a mix of partitioned and non-partitioned tables. For more information, see the [snapshot.by.table.partitions](cc-oracle-cdc-source.md#oracle-cdc-source-snapshot-by-table-partitions) property description. ## 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](cc-oracle-cdc-source.md#cc-oracle-cdc-source-config-properties) (see [Template variables](cc-oracle-cdc-source.md#connect-oracle-cdc-source-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 the following: - Include primary keys for tables with large object (LOB) type columns. Primary keys are required for all tables containing LOB types. - Enable large LOB support for objects exceeding 1 KB. Set `enable.large.lob.object.support` to `true` to handle LOB objects larger than 1 KB. - Avoid updating primary key values associated with LOB topics. If a primary key is updated, the connector emits the updated record to the change event but does not retroactively update the corresponding 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. When running a connector with `snapshot.by.table.partitions=true`, follow these requirements: - Create table-specific topics ahead of time. If these are not created, tasks assigned to partitioned tables will fail. - Ensure all target tables are partitioned. - Use `snapshot.by.table.partitions=false` if your source includes a mix of partitioned and non-partitioned tables. For more information, see the [snapshot.by.table.partitions](cc-oracle-cdc-source.md#oracle-cdc-source-snapshot-by-table-partitions) property description. ## 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](https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234) 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. ## Before state for update operation For an update operation, the connector under default configuration populates the state of a row after a change occurs in the change event record. You can configure the connector to include the state of the row before the change by specifying a non-empty value for the configuration `output.before.state.field`. The specified value will be used as the field name under which the before state would be populated in the change event record. There are no changes to the change event records for snapshot, insert and delete operations when using this feature. Note the following limitations: - If you use the JSON_SR value converter, schema evolution is not backward compatible. - Columns with LOB types do not support this functionality.