Debezium Connector for Oracle

Typically, the redo logs on an Oracle server are configured to not retain the complete history of the database. As a result, the Debezium Oracle connector cannot retrieve the entire history of the database from the logs. To enable the connector to establish a baseline for the current state of the database, the first time that the connector starts, it performs an initial consistent snapshot of the database.

By default, a connector runs an initial snapshot operation only after it starts for the first time. Following this initial snapshot, under normal circumstances, the connector does not repeat the snapshot process. Any future change event data that the connector captures comes in through the streaming process only.

However, in some situations the data that the connector obtained during the initial snapshot might become stale, lost, or incomplete. To provide a mechanism for recapturing table data, Debezium includes an option to perform ad hoc snapshots. You might want to perform an ad hoc snapshot after any of the following changes occur in your Debezium environment:

You can re-run a snapshot for a table for which you previously captured a snapshot by initiating a so-called ad-hoc snapshot. Ad hoc snapshots require the use of signaling tables. You initiate an ad hoc snapshot by sending a signal request to the Debezium signaling table.

When you initiate an ad hoc snapshot of an existing table, the connector appends content to the topic that already exists for the table. If a previously existing topic was removed, Debezium can create a topic automatically if automatic topic creation is enabled.

Ad hoc snapshot signals specify the tables to include in the snapshot. The snapshot can capture the entire contents of the database, or capture only a subset of the tables in the database. Also, the snapshot can capture a subset of the contents of the table(s) in the database.

You specify the tables to capture by sending an execute-snapshot message to the signaling table. Set the type of the execute-snapshot signal to incremental or blocking, and provide the names of the tables to include in the snapshot, as described in the following table:

You initiate an ad hoc incremental snapshot by adding an entry with the execute-snapshot signal type to the signaling table, or by sending a signal message to a Kafka signaling topic. After the connector processes the message, it begins the snapshot operation. The snapshot process reads the first and last primary key values and uses those values as the start and end point for each table. Based on the number of entries in the table, and the configured chunk size, Debezium divides the table into chunks, and proceeds to snapshot each chunk, in succession, one at a time.

For more information, see Incremental snapshots.

You initiate an ad hoc blocking snapshot by adding an entry with the execute-snapshot signal type to the signaling table or signaling topic. After the connector processes the message, it begins the snapshot operation. The connector temporarily stops streaming, and then initiates a snapshot of the specified table, following the same process that it uses during an initial snapshot. After the snapshot completes, the connector resumes streaming.

For more information, see Blocking snapshots.

To provide flexibility in managing snapshots, Debezium includes a supplementary snapshot mechanism, known as incremental snapshotting. Incremental snapshots rely on the Debezium mechanism for sending signals to a Debezium connector. Incremental snapshots are based on the DDD-3 design document.

In an incremental snapshot, instead of capturing the full state of a database all at once, as in an initial snapshot, Debezium captures each table in phases, in a series of configurable chunks. You can specify the tables that you want the snapshot to capture and the size of each chunk. The chunk size determines the number of rows that the snapshot collects during each fetch operation on the database. The default chunk size for incremental snapshots is 1024 rows.

As an incremental snapshot proceeds, Debezium uses watermarks to track its progress, maintaining a record of each table row that it captures. This phased approach to capturing data provides the following advantages over the standard initial snapshot process:

When you run an incremental snapshot, Debezium sorts each table by primary key and then splits the table into chunks based on the configured chunk size. Working chunk by chunk, it then captures each table row in a chunk. For each row that it captures, the snapshot emits a READ event. That event represents the value of the row when the snapshot for the chunk began.

As a snapshot proceeds, it’s likely that other processes continue to access the database, potentially modifying table records. To reflect such changes, INSERT, UPDATE, or DELETE operations are committed to the transaction log as per usual. Similarly, the ongoing Debezium streaming process continues to detect these change events and emits corresponding change event records to Kafka.

In some cases, the UPDATE or DELETE events that the streaming process emits are received out of sequence. That is, the streaming process might emit an event that modifies a table row before the snapshot captures the chunk that contains the READ event for that row. When the snapshot eventually emits the corresponding READ event for the row, its value is already superseded. To ensure that incremental snapshot events that arrive out of sequence are processed in the correct logical order, Debezium employs a buffering scheme for resolving collisions. Only after collisions between the snapshot events and the streamed events are resolved does Debezium emit an event record to Kafka.

To assist in resolving collisions between late-arriving READ events and streamed events that modify the same table row, Debezium employs a so-called snapshot window. The snapshot window demarcates the interval during which an incremental snapshot captures data for a specified table chunk. Before the snapshot window for a chunk opens, Debezium follows its usual behavior and emits events from the transaction log directly downstream to the target Kafka topic. But from the moment that the snapshot for a particular chunk opens, until it closes, Debezium performs a de-duplication step to resolve collisions between events that have the same primary key..

For each data collection, the Debezium emits two types of events, and stores the records for them both in a single destination Kafka topic. The snapshot records that it captures directly from a table are emitted as READ operations. Meanwhile, as users continue to update records in the data collection, and the transaction log is updated to reflect each commit, Debezium emits UPDATE or DELETE operations for each change.

As the snapshot window opens, and Debezium begins processing a snapshot chunk, it delivers snapshot records to a memory buffer. During the snapshot windows, the primary keys of the READ events in the buffer are compared to the primary keys of the incoming streamed events. If no match is found, the streamed event record is sent directly to Kafka. If Debezium detects a match, it discards the buffered READ event, and writes the streamed record to the destination topic, because the streamed event logically supersede the static snapshot event. After the snapshot window for the chunk closes, the buffer contains only READ events for which no related transaction log events exist. Debezium emits these remaining READ events to the table’s Kafka topic.

The connector repeats the process for each snapshot chunk.

Currently, you can use either of the following methods to initiate an incremental snapshot:

For more advanced uses, you can fine-tune control of the snapshot by implementing one of the following interfaces:

To provide more flexibility in managing snapshots, Debezium includes a supplementary ad hoc snapshot mechanism, known as a blocking snapshot. Blocking snapshots rely on the Debezium mechanism for sending signals to a Debezium connector.

A blocking snapshot behaves just like an initial snapshot, except that you can trigger it at run time.

You might want to run a blocking snapshot rather than use the standard initial snapshot process in the following situations:

When you run a blocking snapshot, Debezium stops streaming, and then initiates a snapshot of the specified table, following the same process that it uses during an initial snapshot. After the snapshot completes, the streaming is resumed.

You can set the following properties in the data component of a signal:

For example:

A delay might exist between the time that you send the signal to trigger the snapshot, and the time when streaming stops and the snapshot starts. As a result of this delay, after the snapshot completes, the connector might emit some event records that duplicate records captured by the snapshot.

By default, the Oracle connector writes change events for all INSERT, UPDATE, and DELETE operations that occur in a table to a single Apache Kafka topic that is specific to that table. The connector uses the following convention to name change event topics:

topicPrefix.schemaName.tableName

The following list provides definitions for the components of the default name:

For example, if fulfillment is the server name, inventory is the schema name, and the database contains tables with the names orders, customers, and products, the Debezium Oracle connector emits events to the following Kafka topics, one for each table in the database:

The connector applies similar naming conventions to label its internal database schema history topics, schema change topics, and transaction metadata topics.

If the default topic name do not meet your requirements, you can configure custom topic names. To configure custom topic names, you specify regular expressions in the logical topic routing SMT. For more information about using the logical topic routing SMT to customize topic naming, see Topic routing.

When a database client queries a database, the client uses the database’s current schema. However, the database schema can be changed at any time, which means that the connector must be able to identify what the schema was at the time each insert, update, or delete operation was recorded. Also, a connector cannot necessarily apply the current schema to every event. If an event is relatively old, it’s possible that it was recorded before the current schema was applied.

To ensure correct processing of events that occur after a schema change, Oracle includes in the redo log not only the row-level changes that affect the data, but also the DDL statements that are applied to the database. As the connector encounters these DDL statements in the redo log, it parses them and updates an in-memory representation of each table’s schema. The connector uses this schema representation to identify the structure of the tables at the time of each insert, update, or delete operation and to produce the appropriate change event. In a separate database schema history Kafka topic, the connector records all DDL statements along with the position in the redo log where each DDL statement appeared.

When the connector restarts after either a crash or a graceful stop, it starts reading the redo log from a specific position, that is, from a specific point in time. The connector rebuilds the table structures that existed at this point in time by reading the database schema history Kafka topic and parsing all DDL statements up to the point in the redo log where the connector is starting.

This database schema history topic is internal for internal connector use only. Optionally, the connector can also emit schema change events to a different topic that is intended for consumer applications.

You can configure a Debezium Oracle connector to produce schema change events that describe structural changes that are applied to tables in the database. The connector writes schema change events to a Kafka topic named <serverName>, where serverName is the namespace that is specified in the topic.prefix configuration property.

Debezium emits a new message to the schema change topic whenever it streams data from a new table, or when the structure of the table is altered.

Messages that the connector sends to the schema change topic contain a payload, and, optionally, also contain the schema of the change event message.

The schema for the schema change event has the following elements:

The following example shows a typical schema in JSON format.

The payload of a schema change event message includes the following elements:

The following example shows a typical schema change message in JSON format. The message contains a logical representation of the table schema.

In messages that the connector sends to the schema change topic, the message key is the name of the database that contains the schema change. In the following example, the payload field contains the databaseName key:

Debezium can generate events that represent transaction metadata boundaries and that enrich data change event messages.

Database transactions are represented by a statement block that is enclosed between the BEGIN and END keywords. Debezium generates transaction boundary events for the BEGIN and END delimiters in every transaction. Transaction boundary events contain the following fields:

The following example shows a typical transaction boundary message:

Unless overridden via the topic.transaction option, the connector emits transaction events to the <topic.prefix>.transaction topic.

Entries in the Oracle redo logs do not store the original SQL statements that users submit to make DML changes. Instead, a redo entry holds a set of change vectors and a set of object identifiers that represent the tablespace, table, and columns related to these vectors. In other words, redo log entries don’t include the names of the schemas, tables, or columns affected by DML changes.

The Debezium Oracle connector uses the log.mining.strategy configuration property to control how Oracle LogMiner handles the lookup of the object identifiers in the change vectors. In certain situations, one log mining strategy might prove more reliable than another with regard to schema changes. However, before you choose a log mining strategy, it’s important to consider the implications it might have on performance and overhead.

The Debezium Oracle connector integrates with Oracle LogMiner by default. This integration requires a specialized set of steps which includes generating a complex JDBC SQL query to ingest the changes recorded in the transaction logs as change events. The V$LOGMNR_CONTENTS view used by the JDBC SQL query does not have any indices to improve the query’s performance, and so there are different query modes that can be used that control how the SQL query is generated as a way to improve the query’s execution.

The log.mining.query.filter.mode connector property can be configured with one of the following to influence how the JDBC SQL query is generated:

Oracle writes all changes to the redo logs in the order in which they occur, including changes that are later discarded by a rollback. As a result, concurrent changes from separate transactions are intertwined. When the connector first reads the stream of changes, because it cannot immediately determine which changes are committed or rolled back, it temporarily stores the change events in an internal buffer. After a change is committed, the connector writes the change event from the buffer to Kafka. The connector drops change events that are discarded by a rollback.

You can configure the buffering mechanism that the connector uses by setting the property log.mining.buffer.type.

When the Debezium Oracle connector is configured to use LogMiner, it collects change events from Oracle by using a start and end range that is based on system change numbers (SCNs). The connector manages this range automatically, increasing or decreasing the range depending on whether the connector is able to stream changes in near real-time, or must process a backlog of changes due to the volume of large or bulk transactions in the database.

Under certain circumstances, the Oracle database advances the SCN by an unusually high amount, rather than increasing the SCN value at a constant rate. Such a jump in the SCN value can occur because of the way that a particular integration interacts with the database, or as a result of events such as hot backups.

The Debezium Oracle connector relies on the following configuration properties to detect the SCN gap and adjust the mining range.

The connector first compares the difference in the number of changes between the current SCN and the highest SCN in the current mining range. If the difference between the current SCN value and the highest SCN value is greater than the minimum gap size, then the connector has potentially detected a SCN gap. To confirm whether a gap exists, the connector next compares the timestamps of the current SCN and the SCN at the end of the previous mining range. If the difference between the timestamps is less than the maximum time interval, then the existence of an SCN gap is confirmed.

When an SCN gap occurs, the Debezium connector automatically uses the current SCN as the end point for the range of the current mining session. This allows the connector to quickly catch up to the real-time events without mining smaller ranges in between that return no changes because the SCN value was increased by an unexpectedly large number. When the connector performs the preceding steps in response to an SCN gap, it ignores the value that is specified by the log.mining.batch.size.max property. After the connector finishes the mining session and catches back up to real-time events, it resumes enforcement of the maximum log mining batch size.

The Debezium Oracle connector tracks system change numbers in the connector offsets so that when the connector is restarted, it can begin where it left off. These offsets are part of each emitted change event; however, when the frequency of database changes are low (every few hours or days), the offsets can become stale and prevent the connector from successfully restarting if the system change number is no longer available in the transaction logs.

For connectors that use non-CDB mode to connect to Oracle, you can enable heartbeat.interval.ms to force the connector to emit a heartbeat event at regular intervals so that offsets remain synchronized.

For connectors that use CDB mode to connect to Oracle, maintaining synchronization is more complicated. Not only must you set heartbeat.interval.ms, but it’s also necessary to set heartbeat.action.query. Specifying both properties is required, because in CDB mode, the connector specifically tracks changes inside the PDB only. A supplementary mechanism is needed to trigger change events from within the pluggable database. At regular intervals, the heartbeat action query causes the connector to insert a new table row, or update an existing row in the pluggable database. Debezium detects the table changes and emits change events for them, ensuring that offsets remain synchronized, even in pluggable databases that process changes infrequently.

For each changed table, the change event key is structured such that a field exists for each column in the primary key (or unique key constraint) of the table at the time when the event is created.

For example, a customers table that is defined in the inventory database schema, might have the following change event key:

If the value of the <topic.prefix>.transaction configuration property is set to server1, the JSON representation for every change event that occurs in the customers table in the database features the following key structure:

The schema portion of the key contains a Kafka Connect schema that describes the content of the key portion. In the preceding example, the payload value is not optional, the structure is defined by a schema named server1.DEBEZIUM.CUSTOMERS.Key, and there is one required field named id of type int32. The value of the key’s payload field indicates that it is indeed a structure (which in JSON is just an object) with a single id field, whose value is 1004.

Therefore, you can interpret this key as describing the row in the inventory.customers table (output from the connector named server1) whose id primary key column had a value of 1004.

The structure of a value in a change event message mirrors the structure of the message key in the change event in the message, and contains both a schema section and a payload section.

An envelope structure in the payload sections of a change event value contains the following fields:

The schema portion of the event message’s value contains a schema that describes the envelope structure of the payload and the nested fields within it.

The following example shows the value of a create event value from the customers table that is described in the change event keys example:

In the preceding example, notice how the event defines the following schema:

The payload portion of this event’s value, provides information about the event. It describes that a row was created (op=c), and shows that the after field value contains the values that were inserted into the ID, FIRST_NAME, LAST_NAME, and EMAIL columns of the row.

The following example shows an update change event that the connector captures from the same table as the preceding create event.

The payload has the same structure as the payload of a create (insert) event, but the following values are different:

The payload section reveals several other useful pieces of information. For example, by comparing the before and after structures, we can determine how a row changed as the result of a commit. The source structure provides information about Oracle’s record of this change, providing traceability. It also gives us insight into when this event occurred in relation to other events in this topic and in other topics. Did it occur before, after, or as part of the same commit as another event?

The following example shows a delete event for the table that is shown in the preceding create and update event examples. The schema portion of the delete event is identical to the schema portion for those events.

The payload portion of the event reveals several differences when compared to the payload of a create or update event:

The delete event provides consumers with the information that they require to process the removal of this row.

The Oracle connector’s events are designed to work with Kafka log compaction, which allows for the removal of some older messages as long as at least the most recent message for every key is kept. This allows Kafka to reclaim storage space while ensuring the topic contains a complete dataset and can be used for reloading key-based state.

When a row is deleted, the delete event value shown in the preceding example still works with log compaction, because Kafka is able to remove all earlier messages that use the same key. The message value must be set to null to instruct Kafka to remove all messages that share the same key. To make this possible, by default, Debezium’s Oracle connector always follows a delete event with a special tombstone event that has the same key but null value. You can change the default behavior by setting the connector property tombstones.on.delete.

A truncate change event signals that a table has been truncated. The message key is null in this case, the message value looks like this:

If a single TRUNCATE operation affects multiple tables, the connector emits one truncate change event record for each truncated table.

If you do not want the connector to capture truncate events, use the skipped.operations option to filter them out.

The following table describes how the connector maps basic character types.

The following table describes how the connector maps binary and character large object (LOB) data types.

The following table describes how the Debezium Oracle connector maps numeric types.

As mention above, Oracle allows negative scales in NUMBER type. This can cause an issue during conversion to the Avro format when the number is represented as the Decimal. Decimal type includes scale information, but Avro specification allows only positive values for the scale. Depending on the schema registry used, it may result into Avro serialization failure. To avoid this issue, you can use NumberToZeroScaleConverter, which converts sufficiently high numbers (P - S >= 19) with negative scale into Decimal type with zero scale. It can be configured as follows:

By default, the number is converted to Decimal type (zero_scale.decimal.mode=precise), but for completeness remaining two supported types (double and string) are supported as well.

Oracle does not provide native support for a BOOLEAN data type. However, it is common practice to use other data types with certain semantics to simulate the concept of a logical BOOLEAN data type.

To enable you to convert source columns to Boolean data types, Debezium provides a NumberOneToBooleanConverter custom converter that you can use in one of the following ways:

Other than the Oracle INTERVAL, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types, the way that the connector converts temporal types depends on the value of the time.precision.mode configuration property.

When the time.precision.mode configuration property is set to adaptive (the default), then the connector determines the literal and semantic type for the temporal types based on the column’s data type definition so that events exactly represent the values in the database:

When the time.precision.mode configuration property is set to connect, then the connector uses the predefined Kafka Connect logical types. This can be useful when consumers only know about the built-in Kafka Connect logical types and are unable to handle variable-precision time values. Because the level of precision that Oracle supports exceeds the level that the logical types in Kafka Connect support, if you set time.precision.mode to connect, a loss of precision results when the fractional second precision value of a database column is greater than 3:

The following table describes how the connector maps ROWID (row address) data types.

The following table describes how the connector maps XMLTYPE data types.

Oracle enables you to define custom data types to provide flexibility when the built-in data types do not satisfy your requirements. There are a several user-defined types such as Object types, REF data types, Varrays, and Nested Tables. At this time, you cannot use the Debezium Oracle connector with any of these user-defined types.

Oracle provides SQL-based interfaces that you can use to define new types when the built-in or ANSI-supported types are insufficient. Oracle offers several commonly used data types to serve a broad array of purposes such as Any or Spatial types. At this time, you cannot use the Debezium Oracle connector with any of these data types.

If a default value is specified for a column in the database schema, the Oracle connector will attempt to propagate this value to the schema of the corresponding Kafka record field. Most common data types are supported, including:

If a temporal type uses a function call such as TO_TIMESTAMP or TO_DATE to represent the default value, the connector will resolve the default value by making an additional database call to evaluate the function. For example, if a DATE column is defined with the default value of TO_DATE('2021-01-02', 'YYYY-MM-DD'), the column’s default value will be the number of days since the UNIX epoch for that date or 18629 in this case.

If a temporal type uses the SYSDATE constant to represent the default value, the connector will resolve this based on whether the column is defined as NOT NULL or NULL. If the column is nullable, no default value will be set; however, if the column isn’t nullable then the default value will be resolved as either 0 (for DATE or TIMESTAMP(n) data types) or 1970-01-01T00:00:00Z (for TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data types). The default value type will be numeric except if the column is a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE in which case its emitted as a string.

Beginning with version 23, Oracle database provides a BOOLEAN logical data type. In earlier versions, the database simulates a BOOLEAN type by using a NUMBER(1) data type, constrained with a value of 0 for false, or a value of 1 for true.

By default, when Debezium emits change events for source columns that use the NUMBER(1) data type, it converts the data to the INT8 literal type. If the default mapping for NUMBER(1) data types does not meet your needs, you can configure the connector to use the logical BOOL type when it emits these columns by configuring the NumberOneToBooleanConverter, as shown in the following example:

In the preceding example, the selector property is optional. The selector property specifies a regular expression that designates which tables or columns the converter applies to. If you omit the selector property, when Debezium emits an event, every column with the NUMBER(1) data type is converted to a field that uses the logical BOOL type.

Oracle supports creating NUMBER based columns with negative scale, that is, NUMBER(-2). Not all systems can process negative scale values, so these values can result in processing problems in your pipeline. For example, because Apache Avro does not support these values, problems can occur if Debezium converts events to Avro format. Similarly, downstream consumers that do not support these values can also encounter errors.

In the preceding example, the decimal.mode property specifies how the connector emits decimal values. This property is optional. If you omit the decimal.mode property, the converter defaults to using the PRECISE decimal handling mode.

Although Oracle recommends against the use of certain data types, such as RAW, legacy systems might continue to use such types. By default, Debezium emits RAW column types as logical BYTES, a type that enables the storage of binary or text-based data.

In some cases, RAW columns might store character data as a series of bytes. To facilitate consumption by consumers, you can configure Debezium to use the RawToStringConverter. The RawToStringConverter provides a way to easily target such RAW columns and emit values as strings, rather than bytes. The following example shows how to add the RawToStringConverter to the connector configuration:

In the preceding example, the selector property enables you to define a regular expression that specifies the tables or columns that the converter processes. If you omit the selector property, the converter maps all RAW column types to logical STRING field types.

An Oracle database can be installed either as a standalone instance or using Oracle Real Application Cluster (RAC). The Debezium Oracle connector is compatible with both types of installation.

When the Debezium Oracle connector captures tables, it automatically excludes tables from the following schemas:

To enable the connector to capture changes from a table, the table must use a schema that is not named in the preceding list.

When the Debezium Oracle connector captures tables, it automatically excludes tables that match the following rules:

To enable the connector to capture a table with a name that matches any of the preceding rules, you must rename the table.

Oracle AWS RDS does not allow you to execute the commands above nor does it allow you to log in as sysdba. AWS provides these alternative commands to configure LogMiner. Before executing these commands, ensure that your Oracle AWS RDS instance is enabled for backups.

To confirm that Oracle has backups enabled, execute the command below first. The LOG_MODE should say ARCHIVELOG. If it does not, you may need to reboot your Oracle AWS RDS instance.

Once LOG_MODE is set to ARCHIVELOG, execute the commands to complete LogMiner configuration. The first command set the database to archivelogs and the second adds supplemental logging.

To enable Debezium to capture the before state of changed database rows, you must also enable supplemental logging for captured tables or for the entire database. The following example illustrates how to configure supplemental logging for all columns in a single inventory.customers table.

Enabling supplemental logging for all table columns increases the volume of the Oracle redo logs. To prevent excessive growth in the size of the logs, apply the preceding configuration selectively.

Minimal supplemental logging must be enabled at the database level and can be configured as follows.

Depending on the database configuration, the size and number of redo logs might not be sufficient to achieve acceptable performance. Before you set up the Debezium Oracle connector, ensure that the capacity of the redo logs is sufficient to support the database.

The capacity of the redo logs for a database must be sufficient to store its data dictionary. In general, the size of the data dictionary increases with the number of tables and columns in the database. If the redo log lacks sufficient capacity, both the database and the Debezium connector might experience performance problems.

Consult with your database administrator to evaluate whether the database might require increased log capacity.

Oracle database administrators can configure up to 31 different destinations for archive logs. Administrators can set parameters for each destination to designate it for a specific use, for example, log shipping for physical standbys, or external storage to allow for extended log retention. Oracle reports details about archive log destinations in the V$ARCHIVE_DEST_STATUS view.

The Debezium Oracle connector only uses destinations that have a status of VALID and a type of LOCAL. If your Oracle environment includes multiple destinations that satisfy that criteria, consult with your Oracle administrator to determine which archive log destination Debezium should use.

For the Debezium Oracle connector to capture change events, it must run as an Oracle LogMiner user that has specific permissions. The following example shows the SQL for creating an Oracle user account for the connector in a multi-tenant database model.

A standby database provides a synchronized copy of the primary instance. In the event of a primary database failure, standby databases provide for continuous availability, and disaster recovery. Oracle makes use of both physical and logical standby databases.

A physical standby is an exact, block-for-block copy of the primary production database, and its system change number (SCN) values are identical to those of the primary. The Debezium Oracle connector cannot capture change events directly from a physical standby database, because physical standbys do not accept external connections. The connector can capture events from a physical standby only after the standby is converted to the primary database. The connector then connects to the former standby as if it were any primary database.

A logical standby contains the same logical data as the primary, but data might be stored in a different physical manner. SCN offsets in a logical standby differ from the offsets in the primary database. You can configure the Debezium Oracle connector to capture changes from a logical standby database.

The database parameter max_string_size controls how the Oracle database, and by extension, Debezium, interprets values for VARCHAR2, NVARCHAR2, and RAW fields. The default, STANDARD, means the lengths for these data types align with the same limits with releases prior to Oracle 12c (4000 bytes for VARCHAR2 and NVARCHAR2 and 2000 bytes for RAW). When configured as EXTENDED, these columns now allow up to 32767 bytes of data to be stored.

For the Debezium Oracle connector, when the database parameter max_string_size is EXTENDED, the lob.enabled connector configuration option should be set to true to capture changes made to VARCHAR2 and NVARCHAR2 fields that have string lengths that exceed 4000 bytes or RAW fields with more than 2000 bytes.

When set to EXTENDED, Oracle performs an implicit conversion of the character data in-flight when the byte length of the string data exceeds the legacy maximums. This implicit conversion means that Oracle internally treats the string data as though it is a CLOB, so you get the benefit of treating the field as a regular string to the outside world, but with all the pitfalls and concerns about storage at the database tier level.

Since Oracle treats these strings as CLOB internally, the redo logs also reflect several unique operation types that the Debezium Oracle connector needs to be aware that it should mine. And because these operation types very closely resemble CLOB operations, the redo log entries must be mined in the same way as any other LOB type, which is why lob.enabled must be set to true to capture changes from the redo logs regardless of the string data’s byte length.

Typically, you register a Debezium Oracle connector by submitting a JSON request that specifies the configuration properties for the connector. The following example shows a JSON request for registering an instance of the Debezium Oracle connector with logical name server1 at port 1521:

You can choose to produce events for a subset of the schemas and tables in a database. Optionally, you can ignore, mask, or truncate columns that contain sensitive data, that are larger than a specified size, or that you do not need.

In the previous example, the database.hostname and database.port properties are used to define the connection to the database host. However, in more complex Oracle deployments, or in deployments that use Transparent Network Substrate (TNS) names, you can use an alternative method in which you specify a JDBC URL.

The following JSON example shows the same configuration as in the preceding example, except that it uses a JDBC URL to connect to the database.

For the complete list of the configuration properties that you can set for the Debezium Oracle connector, see Oracle connector properties.

You can send this configuration with a POST command to a running Kafka Connect service. The service records the configuration and starts a connector task that performs the following operations:

To start running a Debezium Oracle connector, create a connector configuration, and add the configuration to your Kafka Connect cluster.

After the connector starts, it performs a consistent snapshot of the Oracle databases that the connector is configured for. The connector then starts generating data change events for row-level operations and streaming the change event records to Kafka topics.

Oracle Database supports the following deployment types:

When connecting to Oracle using mutual TLS authentication (mTLS), this involves both the connector and the database service proving their identities. The Debezium for Oracle connector relies on the Oracle JDBC driver’s built-in functionality to support mTLS authentication.

You can use either of the following methods to establish mTLS connections between Debezium and Oracle:

The following configuration properties are required unless a default value is available.