Configuring Connection Details

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Connecting to Your Event Broker .

The name of the binder for Oracle is oracdc.

spring:
  cloud:
    stream:
      <binder-name>:
        default:
          <shared parameters>
        bindings:
          input-0:
            <workflow-specific parameters>

Oracle Database Prerequisites

Before you use the Micro-Integration for Oracle Bulk CDC, you must configure your Oracle database by completing the following steps:

Steps

1. Configure the CDC user by running the following commands in SQL*Plus as SYSDBA:

   CONNECT SYS/<sys_password> AS SYSDBA;
   
   CREATE USER <cdc_username> IDENTIFIED BY <cdc_password>
     DEFAULT TABLESPACE USERS
     TEMPORARY TABLESPACE TEMP
     QUOTA UNLIMITED ON USERS;
   
   GRANT CONNECT, RESOURCE, DBA TO <cdc_username>;
   GRANT SELECT ANY TRANSACTION TO <cdc_username>;
   GRANT SELECT ANY DICTIONARY TO <cdc_username>;
   GRANT EXECUTE_CATALOG_ROLE TO <cdc_username>;
   GRANT SELECT_CATALOG_ROLE TO <cdc_username>;
   GRANT LOGMINING TO <cdc_username>;
   
   ALTER USER <cdc_username> SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
   GRANT SET CONTAINER TO <cdc_username>;

2. Grant privileges to the CDC user inside the pluggable database (PDB).

   ALTER SESSION SET CONTAINER = <pdb_name>;
   
   GRANT CONNECT, RESOURCE, DBA TO <cdc_username>;
   GRANT SELECT ANY TRANSACTION TO <cdc_username>;
   GRANT SELECT ANY DICTIONARY TO <cdc_username>;
   GRANT EXECUTE_CATALOG_ROLE TO <cdc_username>;
   GRANT SELECT_CATALOG_ROLE TO <cdc_username>;
   GRANT LOGMINING TO <cdc_username>;

3. Enable ARCHIVELOG mode (required by LogMiner-based CDC). To enable ARCHIVELOG mode:

   1. Configure the archive destination:

      ALTER SESSION SET CONTAINER = CDB$ROOT;
      
      ALTER SYSTEM SET db_recovery_file_dest_size = 10G SCOPE=BOTH;
      ALTER SYSTEM SET db_recovery_file_dest = '<archive_dest_path>' SCOPE=BOTH;

   2. Restart the database and enable ARCHIVELOG:

      SHUTDOWN IMMEDIATE;
      
      CONNECT SYS/<sys_password> AS SYSDBA;
      STARTUP MOUNT;
      
      ALTER DATABASE ARCHIVELOG;
      ALTER DATABASE OPEN;
      
      ALTER PLUGGABLE DATABASE ALL OPEN;
      ALTER SYSTEM REGISTER;

4. Enable database-level supplemental logging:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

5. Switch to the PDB container where your application tables reside, so that the following steps target the correct database scope:

   ALTER SESSION SET CONTAINER = <pdb_name>;

6. Enable table-level supplemental logging:

   ALTER TABLE <schema>.<table_name_1> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   ALTER TABLE <schema>.<table_name_2> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

7. Verify that the tables you need have been created:

   SELECT owner, table_name
   FROM all_tables
   WHERE owner='<schema>'
     AND table_name IN ('<table_name_1>','<table_name_2>');
   
   EXIT;

Oracle Datatype Handling

Oracle's handling of booleans, large objects, and quoted identifiers affects what appears in CDC event payloads and how column names are represented. Depending on your schema, you may need to configure both the database and the Micro-Integration to get the expected output.

Oracle Boolean Support

converters=boolean
boolean.type=io.debezium.connector.oracle.converters.NumberOneToBooleanConverter
boolean.selector=.*MYTABLE.FLAG,.*.IS_ARCHIVED

LOB and XMLTYPE Handling

Oracle treats large objects (LOBs) such as BLOB, CLOB, and XMLTYPE in a special way. When these columns are modified, Oracle generates multiple internal redo operations (INSERT + UPDATE + UPDATE) to write the LOB data. These operations always occur at the database level and are captured by LogMiner.

To handle this, you can use the following options:

• lobEnabled

  Setting lobEnabled=true enables LOB processing in the Micro-Integration (using Debezium) so that supported LOB types (CLOB, BLOB, XMLTYPE) are properly serialized and included in the outgoing CDC event. We recommend setting lobEnabled=true by default, with the option for you to disable it if necessary.

• columnExcludeList

  The columnExcludeList parameter allows you to exclude specific columns from the outgoing CDC message. This is useful when a column is too large (for example, a profile picture BLOB), the column is not needed downstream, or you want to reduce message size.

  Example:

  columnExcludeList=SCHEMA_B.COMMON_TYPES.PROFILE_PICTURE

  When combined with lobEnabled=true, the Micro-Integration removes the excluded LOB column from the final CDC payload.

These settings control how LOB/XMLTYPE fields appear in the final outgoing CDC message, but they do not prevent Oracle or LogMiner from processing internal LOB writes.

Even when a LOB column is excluded from the final payload, Oracle still writes all internal LOB redo records (INSERT and multiple UPDATE operations), and LogMiner continues to read these internal changes as usual. Micro-Integration for Oracle CDC processes these LOB operations internally and collapses them into a single logical CDC event. The filtering is applied only at the final stage by the Micro-Integration for Oracle CDC, which removes the excluded LOB column from the outgoing message. As a result, the database performs the full LOB write internally, but the excluded column does not appear in the final CDC event published.

Oracle Quoted vs. Nonquoted Identifiers

In Oracle, the behavior of column names depends on whether the identifier is created as a quoted or nonquoted identifier. This behavior is defined by the Oracle SQL Language Reference and directly affects how column names appear in CDC events:

• Nonquoted Identifiers

  When an identifier is created without double quotes, Oracle treats it as a nonquoted identifier, which follows these rules:

  • A nonquoted identifier is not surrounded by any punctuation.

  • Nonquoted identifiers are automatically folded to uppercase.

  For example, when a column is created without quotes (column_2_decimal), Oracle stores it internally as COLUMN_2_DECIMAL. Oracle metadata exposes column names exactly as stored. As a result, the column is emitted in CDC events as COLUMN_2_DECIMAL (uppercase).

• Quoted Identifiers

  When an identifier is created with double quotes, it becomes a quoted identifier, which preserves the letter case. A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

  For example, when a column is created with quotes ("column_2_decimal"), Oracle stores it exactly as column_2_decimal (lowercase preserved). Oracle metadata exposes the column name exactly as stored. As a result, the column is emitted in CDC events as column_2_decimal (lowercase).

Oracle Connection Details

To manually configure the Oracle connection details, set the following properties in application.yml:

spring:
  cloud:
    stream:
      oracdc:
        bindings:
          input-0:
            consumer:
              endpoint:
                query-parameters:
                  databaseHostname: <hostname>
                  databasePort: <port>
                  databasePdbName: <PDB name>
                  databaseDbname: <database name>
                  ...

Where:

• databaseHostname is the hostname or IP address of the Oracle database server.

• databasePort is the connection port of the Oracle database server (default: 1521).

• databasePdbName is the name of the pluggable database (PDB) that the Micro-Integration should connect to and monitor for CDC events.

• databaseDbname is the name of the Oracle database (container database or pluggable database) used by the Micro-Integration as its logical server name for CDC metadata.

There are additional options required depending on the authentication type. For more information, see Basic Authentication or Client Certificate Authentication.

Basic Authentication

To use basic authentication, configure the following properties in application.yml:

oracdc:
  bindings:
    input-0:
      consumer:
        endpoint:
          query-parameters:
            databaseHostname: <hostname>
            databasePort: <port>
            databasePdbName: <PDB name>
            databaseDbname: <database name>
            databaseUser: <database user name>
            databasePassword: <password>

Where:

• databaseUser is the username to use for logging in to Oracle. The user must have CDC privileges.

• databasePassword is the password to use for authenticating with the Oracle database.

Client Certificate Authentication

This Micro-Integration supports TLS and mutual TLS (mTLS) connections to Oracle databases that use Oracle Wallet for TLS configuration. An Oracle Wallet is a password-protected container that stores the SSL/TLS certificates and keys that Oracle uses for secure connections. The connection uses the TCPS (TCP with SSL/TLS) protocol on port 2484 by default.

Choose TLS if your Oracle server is configured with a server-side certificate for connection encryption. Choose mTLS if your organization requires mutual authentication and you have a custom (non-auto-managed) Oracle Wallet.

Mutual TLS is not supported with auto-managed wallets, such as OCI-managed wallets. Auto-managed wallets do not allow private key export or client certificate import, which are required for mTLS.

• TLS (one-way): Encrypts the connection and verifies the Oracle server's identity. Provide a truststore (JKS or PKCS12 format) containing the Oracle server's CA certificate, exported from the server-side Oracle Wallet. To export the truststore, use the orapki wallet export command or Oracle Wallet Manager.

• Mutual TLS (mTLS): Provides two-way authentication. Requires a custom Oracle Wallet on the Oracle server. The Oracle server must have SSL_CLIENT_AUTHENTICATION=TRUE configured and the client certificate imported into the server-side Oracle Wallet. In addition to a truststore, provide a keystore (JKS or PKCS12 format) containing the client certificate and private key.

To use Client Certificate Authentication (TLS or mTLS), configure the following properties in application.yml. The databaseUrl parameter replaces databaseHostname and databasePort:

oracdc:
  bindings:
    input-0:
      consumer:
        endpoint:
          query-parameters:
            databasePdbName: <PDB name>
            databaseDbname: <database name>
            databaseUrl: <database URL>
            databaseUser: <database user name>
            databasePassword: <password>
            oracleSslTrustStore: <path/to/truststore.jks>
            oracleSslTrustStoreType: JKS
            oracleSslTrustStorePassword: <truststore password>
            # For mTLS, also set:
            oracleSslKeyStore: <path/to/keystore.jks>
            oracleSslKeyStoreType: JKS
            oracleSslKeyStorePassword: <keystore password>

Where:

• databaseUrl is the Oracle TNS connection string using the TCPS protocol. Format:

   "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<hostname>)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))"

• databaseUser is the username to use for logging in to Oracle. The user must have CDC privileges.

• databasePassword is the password to use for authenticating with the Oracle database.

• oracleSslTrustStore is the path to the truststore file (.jks or .p12) that contains the server's certificate, exported from the server-side Oracle Wallet.

• oracleSslTrustStoreType is the format of the truststore file. Valid values: JKS, PKCS12.

• oracleSslTrustStorePassword (optional) is the truststore password.

• oracleSslKeyStore (mTLS only) is the path to the keystore file (.jks or .p12) that contains the client certificate and private key.

• oracleSslKeyStoreType (mTLS only) is the format of the keystore file. Valid values: JKS, PKCS12.

• oracleSslKeyStorePassword (mTLS only, optional) is the keystore password.

Checkpoint Store Configuration

The Micro-Integration for Oracle Bulk CDC stores the information about the current progress of processing change events in a checkpoint store backed by a Last Value Queue (LVQ) on a Solace event broker. The checkpoint store contains information about the System Change Number (SCN) that has been processed.

For example, add the following settings to the application.yml file:

solace:
  oracdc:
    checkpoint:
      lvqName: lvq-checkpoint
      autoProvisionLvq: true

The following table lists the configuration options for the Checkpoint Store.

Oracle Bulk CDC Binder Configuration Options

The following properties are available at the Oracle Bulk CDC binder level.

Oracle Bulk CDC Consumer Configuration Options

The following configuration options are available for the Oracle Bulk CDC consumer.

All properties must be prefixed with spring.cloud.stream.oracdc.bindings.input-0.consumer.endpoint.query-parameters.

Example configuration:

spring:
  cloud:
    stream:
      oracdc:
        bindings:
           input-0:
             consumer:
               endpoint:
                 query-parameters:
                   <connection parameters>
                   snapshotMode: no_data
                   skipDeleteEvents: false
                   lobEnabled: true
                   databaseConnectionAdapter: logminer
                   logMiningStrategy: hybrid
                   maxBatchSize: 2048