Troubleshooting

This page contains troubleshooting information for the Kafka Connect Oracle CDC Source connector.

Basic troubleshooting

The following sections describe a few potential issues and how to resolve them.

Numbers in my table-specific topics are not human readable

NUMBER or DECIMAL types in a table are converted to a decimal value that is represented in byte type in Kafka. For example purposes, suppose you have the following table:

CREATE TABLE MARIPOSA_ORDERDETAILS ( \
"ORDER_NUMBER" NUMBER (9) PRIMARY KEY, \
"PRODUCT_CODE" NUMBER (9), \
"DESCRIPTION" CLOB, \
"QUANTITY_ORDERED" NUMBER (25,13), \
"PRICE_EACH" VARCHAR2(10), \
"QUANTITY_INVENTORY" NUMBER (4,2) \
)\
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING \
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) \
TABLESPACE "USERS"

The following examples show a record and a schema from this table, using the Avro converter. The first example shows the original results (not human readable) and the second example shows how the results can be made human readable using the "numeric.mapping":"best_fit" connector property.

Result is not human readable

The record from the table:

{
  "ORDER_NUMBER": "\u0001\u001f",
  "PRODUCT_CODE": {
    "bytes": "\u0003ê"
  },
  "QUANTITY_ORDERED": {
    "bytes": "QÚ \u0007 \u0000"
  },
  "PRICE_EACH": {
    "string": "$7500"
  },
  "QUANTITY_INVENTORY": {
    "bytes": "\u0003è"
  },
  "table": {
    "string": "ORCL.ADMIN.MARIPOSA_ORDERDETAILS"
  },
  "scn": {
    "string": "33003097"
  },
  "op_type": {
    "string": "R"
  },
  "op_ts": null,
  "current_ts": {
    "string": "1610143106957"
  },
  "row_id": null,
  "username": null
}

The schema from the table:

{
  "fields": [
    {
      "name": "ORDER_NUMBER",
      "type": {
        "connect.name": "org.apache.kafka.connect.data.Decimal",
        "connect.parameters": {
          "scale": "0"
        },
        "connect.version": 1,
        "logicalType": "decimal",
        "precision": 64,
        "scale": 0,
        "type": "bytes"
      }
    },
    {
      "default": null,
      "name": "PRODUCT_CODE",
      "type": [
        "null",
        {
          "connect.name": "org.apache.kafka.connect.data.Decimal",
          "connect.parameters": {
            "scale": "0"
          },
          "connect.version": 1,
          "logicalType": "decimal",
          "precision": 64,
          "scale": 0,
          "type": "bytes"
        }
      ]
    },
    {
      "default": null,
      "name": "QUANTITY_ORDERED",
      "type": [
        "null",
        {
          "connect.name": "org.apache.kafka.connect.data.Decimal",
          "connect.parameters": {
            "scale": "13"
          },
          "connect.version": 1,
          "logicalType": "decimal",
          "precision": 64,
          "scale": 13,
          "type": "bytes"
        }
      ]
    },
    {
      "default": null,
      "name": "PRICE_EACH",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "QUANTITY_INVENTORY",
      "type": [
        "null",
        {
          "connect.name": "org.apache.kafka.connect.data.Decimal",
          "connect.parameters": {
            "scale": "2"
          },
          "connect.version": 1,
          "logicalType": "decimal",
          "precision": 64,
          "scale": 2,
          "type": "bytes"
        }
      ]
    },
    {
      "default": null,
      "name": "table",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "scn",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "op_type",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "op_ts",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "current_ts",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "row_id",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "username",
      "type": [
        "null",
        "string"
      ]
    }
  ],
  "name": "ConnectDefault",
  "namespace": "io.confluent.connect.avro",
  "type": "record"
}

Human readable result

If you want to see NUMBER as INT or DOUBLE, you can use the "numeric.mapping":"best_fit" connector property. The following examples show a record and a schema from this table, using this property setting.

The record from the table:

{
  "ORDER_NUMBER": 399,
  "PRODUCT_CODE": {
    "int": 1001
  },
  "QUANTITY_ORDERED": {
    "double": 10
  },
  "PRICE_EACH": {
    "string": "$2500"
  },
  "QUANTITY_INVENTORY": {
    "double": 10
  },
  "table": {
    "string": "ORCL.ADMIN.MARIPOSA_ORDERDETAILS"
  },
  "scn": {
    "string": "33014353"
  },
  "op_type": {
    "string": "R"
  },
  "op_ts": null,
  "current_ts": {
    "string": "1610143395344"
  },
  "row_id": null,
  "username": null
}

The schema from the table:

{
  "fields": [
    {
      "name": "ORDER_NUMBER",
      "type": "int"
    },
    {
      "default": null,
      "name": "PRODUCT_CODE",
      "type": [
        "null",
        "int"
      ]
    },
    {
      "default": null,
      "name": "QUANTITY_ORDERED",
      "type": [
        "null",
        "double"
      ]
    },
    {
      "default": null,
      "name": "PRICE_EACH",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "QUANTITY_INVENTORY",
      "type": [
        "null",
        "double"
      ]
    },
    {
      "default": null,
      "name": "table",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "scn",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "op_type",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "op_ts",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "current_ts",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "row_id",
      "type": [
        "null",
        "string"
      ]
    },
    {
      "default": null,
      "name": "username",
      "type": [
        "null",
        "string"
      ]
    }
  ],
  "name": "ConnectDefault",
  "namespace": "io.confluent.connect.avro",
  "type": "record"
}

No BLOBs, CLOBs, or NCLOBs in my table-specific topics

Since BLOBs, CLOBs, and NCLOBs are very large objects (LOBs), the connector stores these objects in separate Kafka topics. If "lob.topic.name.template" is empty, the connector ignores BLOBs, CLOBs, and NCLOBs. You can use the following example property to specify where the connector stores these large objects:

"lob.topic.name.template":"${databaseName}.${schemaName}.${tableName}.${columnName}"

Why do my table-specific topics show no new records?

When you check the log, you may see the following error:

[2020-12-08 23:32:20,714] ERROR Exception in RecordQueue thread (io.confluent.connect.oracle.cdc.util.RecordQueue)
org.apache.kafka.connect.errors.ConnectException: Failed to subscribe to the redo log topic 'redo-log-topic' even after waiting PT1M. Verify that this redo log topic exists in the brokers at broker:9092, and that the redo log reading task is able to produce to that topic.
   at io.confluent.connect.oracle.cdc.ChangeEventGenerator.subscribeToRedoLogTopic(ChangeEventGenerator.java:266)
   at io.confluent.connect.oracle.cdc.ChangeEventGenerator.execute(ChangeEventGenerator.java:221)
   at io.confluent.connect.oracle.cdc.util.RecordQueue.lambda$createLoggingSupplier$1(RecordQueue.java:468)
   at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
   at java.base/java.lang.Thread.run(Thread.java:834)
[2020-12-08 23:33:14,750] INFO WorkerSourceTask{id=cdc-oracle-source-pdb-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask)
[2020-12-08 23:33:14,751] INFO WorkerSourceTask{id=cdc-oracle-source-pdb-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask)

The redo.log.startup.polling.limit.ms property defaults to 300000 milliseconds (5 minutes). Connect tasks will wait for five minutes until a redo-log-topic is created. To resolve this issue, you can increase redo.log.startup.polling.limit.ms.

If there is no redo log topic, you can create it. Before you run the connector, use the following command to create the topic. Make sure the topic name matches the value you use for the "redo.log.topic.name" configuration property.

bin/kafka-topics --create --topic <redo-log-topic-name> \
--bootstrap-server broker:9092 --replication-factor 1 \
--partitions 1 --config cleanup.policy=delete \
--config retention.ms=120960000

Why are my topics empty?

Topics may be empty for a number of reasons. You must ensure the following:

• The database user has privilege to select from the tables.
• The table has at least one row.
• The table.inclusion.regex configuration property matches the fully qualified table name (for example, dbo.Users) and that the regular expression in the table.exclusion.regex configuration property does not match the fully qualified table name.
• If start.from is set to a particular SCN, ensure the rows in the database have changed since that SCN.
• If start.from=snapshot (the default), ensure the rows in the database have changed since the snapshot for the table was created.
• The redo.log.row.fetch.size property defaults to 10. If you’ve changed fewer than the number of rows specified, it’s possible that the connector is waiting for more changes to appear before sending the latest batch of records.

Why don’t I see change events in my topics?

If the topic has at least one record produced by the connector that is currently running, make sure that the database rows have changed on the corresponding table since the initial snapshot for the table was taken.

Ensure the table.inclusion.regex configuration property matches the fully qualified table name (for example, dbo.Users) and the regular expression in the table.exclusion.regex configuration property does not match the fully qualified table name.

Why is there no throughput when the connector is running with no exceptions?

One common reason is that automatic topic creation is off, and the redo log topic or change event topics were not manually created. Or, there are typos in the topic names.

Errors and exceptions

The following sections provide troubleshooting for several errors and exceptions.

ORA-01031: insufficient privileges

First, make sure you have set up the prerequisite privileges. For details, see Configure database user privileges.

Second, check whether supplemental logging is turned on for the databases or tables you want. You can enable supplemental logging for all tables or specific tables.

Supplemental logging for all tables

1. Enter the following command to turn on supplemental logging for all tables and columns.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

2. After enabling supplemental logging, enter the following:

   select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database
   

   You should see that SUPPLEMENTAL_LOG_DATA_ALL is YES as shown in the following example screen.

   

Supplemental logging for specific tables

1. Enter the following command to turn on supplemental logging for specific tables.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   ALTER TABLE <schema-name>.<table-name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   

2. After enabling supplemental logging, enter the following:

   select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database
   

   You should see that SUPPLEMENTAL_LOG_DATA_ALL is NO as shown in the following example screen.

   

ORA-06550: identifier ‘DBMS_LOGMNR’ must be declared

You may see this error when running an Oracle CDC source connector against an Amazon RDS for Oracle. Run the following command:

exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR', 'MYUSER', 'EXECUTE');

This record has failed the validation on broker and hence will be rejected

If you have tables without primary keys, you might see the following error message for table-specific topics. This is because the connector tried to write records without primary keys to a compaction topic (i.e., property: "topic.creation.default.cleanup.policy":"compact").

[2022-01-25 15:23:44,497] ERROR
WorkerSourceTask{id=OracleCDC_Mariposa_No_PK4-1} failed to send record to
ORCL.ADMIN.ORDERS: (org.apache.kafka.connect.runtime.WorkerSourceTask:370)
org.apache.kafka.common.InvalidRecordException: This record has failed the
validation on broker and hence will be rejected.

You can set "topic.creation.default.cleanup.policy" to “delete” or you can define another topic creation rule. For example:

 "topic.creation.groups":"redo",
 "topic.creation.redo.include":"oracle-redo-log-topic",
 "topic.creation.redo.replication.factor":3,
 "topic.creation.redo.partitions":1,
 "topic.creation.redo.cleanup.policy":"delete",
 "topic.creation.redo.retention.ms":1209600000,
 "topic.creation.default.replication.factor":3,
 "topic.creation.default.partitions":5,
 "topic.creation.default.cleanup.policy":"compact",

 “topic.creation.groups”:”nokey”,
 "topic.creation.nokey.include":"ORCL.ADMIN.ORDERS",
 "topic.creation.nokey.replication.factor":3,
 "topic.creation.nokey.cleanup.policy":"delete",

Inclusion pattern matches no tables…

When a connector can’t find tables that match the regular expression entries in the table.inclusion.regex property, the following message will display:

Inclusion pattern matches no tables...

This connector would not output any records…

If no table matches the inclusion and exclusion pattern specified in table.inclusion.regex and table.exclusion.regex respectively, the following message will display:

"This connector would not output any records, since no tables matched the
inclusion pattern '...' or were excluded by the pattern '...', or the redo
log topic name was not specified"?

Resolution: Check the inclusion pattern and the exclusion pattern specified in table.inclusion.regex and table.exclusion.regex configuration properties.

Note

You must use a fully-qualified table name to match tables. A fully-qualified table name consists of three parts:

• database name (either SID or PDB name)
• schema name
• table name

For example, if you want to match tables in a pluggable database (PDB) named MY_PDB1 and the schema is SYS, the inclusion is MY_PDB1[.]SYS[.].*.

Invalid value xxxx.YYYY.ERR$_TMP_PRS…

If the table.topic.name.template configuration property is set to a value that contains unescaped $ characters, the connector will display the following message:

"Invalid value xxxx.YYYY.ERR$_TMP_PRS for configuration
table.topic.name.template: must match pattern … Invalid value Must contain
only valid variable substitutions or characters escaped with `\\`: Invalid
value xxxx.YYYY.ERR$_TMP_PRS"?

Resolution: Check the value set on the configuration shown in the error message. Be sure that it does not contain unescaped $ characters. The $ character can be escaped with a backslash \ to make the following valid configuration value: xxxx.YYYY.ERR\$_TMP_PRS.

oracle.simplefan.impl.FanManager configure SEVERE:…

When trying to connect to an Oracle Cloud Autonomous Database, if the JDBC driver is trying to connect to the ONE service on the database when Oracle RAC Fast Application Notification (FAN) events are not enabled on the database, the connector will display the following message:

oracle.simplefan.impl.FanManager configure SEVERE: attempt
to configure ONS in FanManager failed with oracle.ons.NoServersAvailable:
Subscription time out

Resolution: Ensure the following configuration property is set to false:

oracle.fan.events.enable=false

Regex pattern issues

The following sections provide regex pattern guidance.

Amazon RDS instances for Oracle

Scenario: You have tables named CONFLUENT_CUSTOMERS, CONFLUENT_ORDERS, CONFLUENT_PRODUCTS, DEMO, and TESTING.

To capture changes to tables starting with CONFLUENT, use the following regex pattern:

"oracle.sid":"ORCL",
"oracle.username": "admin",
"table.inclusion.regex":"ORCL[.]ADMIN[.]CONFLUENT.*",

To capture changes to starting with DEMO and TESTING, use the following regex pattern:

"oracle.sid":"ORCL",
"oracle.username": "admin",
"table.inclusion.regex":"ORCL[.]ADMIN[.](DEMO|TESTING)",

To capture changes to tables starting with CONFLUENT, DEMO and TESTING, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLCDB[.]C##MYUSER[.](CONFLUENT.*|DEMO|TESTING)",

Oracle Standard Databases and Container Databases (CDBs)

Scenario: You have tables named CONFLUENT_CUSTOMERS, CONFLUENT_ORDERS, CONFLUENT_PRODUCTS, DEMO, and TESTING.

To capture changes to tables starting with CONFLUENT, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLCDB[.]C##MYUSER[.]CONFLUENT.*",

To capture changes to tables starting with DEMO and TESTING, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLCDB[.]C##MYUSER[.](DEMO|TESTING)",

To capture changes to tables starting with CONFLUENT, and DEMO and TESTING tables, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLCDB[.]C##MYUSER[.](CONFLUENT.*|DEMO|TESTING)",

Oracle Pluggable Databases (PDBs)

Scenario: You have tables named CONFLUENT_CUSTOMERS, CONFLUENT_ORDERS, CONFLUENT_PRODUCTS, DEMO, and TESTING.

To capture changes to tables starting with CONFLUENT, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.pdb.name": "ORCLPDB1",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLPDB1[.]C##MYUSER[.]CONFLUENT.*",

To capture changes to tables starting with DEMO and TESTING, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.pdb.name": "ORCLPDB1",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLPDB1[.]C##MYUSER[.](DEMO|TESTING)",

To capture changes to tables starting with CONFLUENT, and DEMO and TESTING, use the following regex pattern:

"oracle.sid":"ORCLCDB",
"oracle.pdb.name": "ORCLPDB1",
"oracle.username": "C##MYUSER",
"table.inclusion.regex":"ORCLPDB1[.]C##MYUSER[.](CONFLUENT.*|DEMO|TESTING)",