Change events from Oracle database are not immediate

[Dwija D]

Hi

I am trying to capture change events from Oracle(12)  using the native LogMiner to Kafka topics. The connector runs fine. However, the change events are not pushed to the kafka topics immediately.  It takes random amount of time(10 mins, 25 mins and even an hour) when i made an update to a record in the configured table. The connector configuration is given below:

"name": "WF_USER_ROLE_ASSIGNMENTS_DEBEZIUM_TEST_3",

  "config": {

    "name": "WF_USER_ROLE_ASSIGNMENTS_DEBEZIUM_TEST_3", 

    "connector.class": "io.debezium.connector.oracle.OracleConnector",

    "database.hostname": "172.16.10.13",

    "database.port": "1521",

    "database.user": "CDCUSER",

    "database.password": "PassW0rd!23",

    "database.dbname": "VIS",

    "database.oracle.version": "12",

    "database.server.name": "VIS_R1227",

    "database.history.kafka.bootstrap.servers": "kfk-bro1:9093",

    "database.history.kafka.topic": "WF_USER_ROLE_ASSIGNMENTS_DEBEZIUM_TEST_3",

    "table.include.list": "APPLSYS.WF_USER_ROLE_ASSIGNMENTS",

    "message.key.columns": "APPLSYS.WF_USER_ROLE_ASSIGNMENTS:USER_NAME,ASSIGNING_ROLE,LAST_UPDATE_DATE,RELATIONSHIP_ID,PARTITION_ID",

     "key.converter": "org.apache.kafka.connect.json.JsonConverter",

    "key.converter.schemas.enable": "false",

    "value.converter": "org.apache.kafka.connect.json.JsonConverter",

    "value.converter.schemas.enable": "false",

    "key.converter.schema.registry.url": "http://sandbox-kfk-sr2:8082",

    "value.converter.schema.registry.url": "http://sandbox-kfk-sr2:8082"

  }

Also, the connector does not include  key columns which is configured in the connector to the message .

Is there a configuration option in debezium's Oracle-connector so that change events are available in real time which i think is the default behaviour of Oracle connector.

 Thanks

[Chris Cranford]

Hi Dwija -

If you're experiencing latency with event delivery, there are a few things to check:

My first suggestion is to look at your redo log configuration.  If your redo logs are configured poorly, this will significantly impact Oracle LogMiner and will cause abnormal delays in change events.  This is a result of the data dictionary being written to the logs and if they're sized too small, this causes a perpetual log switch situation that can be detrimental both to Oracle's performance & the connector.  Ideally what you want is to be able to write your data dictionary to a single redo log.  Your DBA should be able to look at the V$ARCHIVED_LOG table to determine whether or not the data dictionary is spread across multiple logs and if so, they can adjust the database log configuration appropriately. 

The next thing to check is whether or not the changes are being committed.  We've had some users who have thought their IDEs were in auto-commit mode and realized later that they're not in auto-commit mode and so the changes were not being emitted as fast as they expected since the commit didn't happen until they closed the IDE and the connection was committed.  It's important to remember that Oracle writes *ALL* changes to the redo logs, including those that are rollled back and those which are in-progress.  The connector gets this entire stream of events and therefore cannot react on an event until we detect the commit or rollback for the associated transaction.

Another option is to enable DEBUG logging as well as JMX metrics.  The connector tracks a number of JMX metrics for processing durations and performance.  These metrics might give some indication as to where most of the time is being spent.  Additionally using DEBUG log level, each mining session writes the number of rows processed, the number of DML operations detected, the number of current active transactions, the total time the batch took to be processed, and how far behind the connector is (aka lag).

As for "message.key.columns", this functionality should work, what version of the Debezium Oracle connector are you using?

CC

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/6b22600d-8d27-4779-a0bd-32a7e91f3aa3n%40googlegroups.com.

[Dwija D]

Hi Chris

Many thanks for all the useful information on Oracle LogMiner/Redo Log/Archive Log .  I am able to reduce the latency with this query (SQL> alter system switch logfile;) thereby able to test the change events faster than before.

I am using Oracle 12c along with Debezium 1.6.1.Final.

Regards