Sync data from Informix to Oracle using Debezium/Kafka Connect/Kafka/JDBC Sink

 Following commands and details will be helpful when configuring Debezium to sync data from Informix to Oracle database.

1. Docker compose to create Kafka connect, Kafka, Zookeeper

Copy Informix connector and streaming jar files into connectors directory in docker compose file exists

services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.5.0
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
    ports:
      - "2181:2181"
  kafka:
    image: confluentinc/cp-kafka:7.5.0
    ports:
      - "9092:9092"
    environment:
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
  connect:
    image: my-debezium-connect  # Custom image you’ll build below
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: connect-cluster
      CONFIG_STORAGE_TOPIC: connect-configs
      OFFSET_STORAGE_TOPIC: connect-offsets
      STATUS_STORAGE_TOPIC: connect-status
      KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      CONFIG_STORAGE_REPLICATION_FACTOR: 1
      OFFSET_STORAGE_REPLICATION_FACTOR: 1
      STATUS_STORAGE_REPLICATION_FACTOR: 1
      VALUE_CONVERTER_SCHEMAS_ENABLE: "false"
      KEY_CONVERTER_SCHEMAS_ENABLE: "false"
    volumes:
      - ./connectors:/kafka/connectors

2. Build docker compose

sudo docker compose build --no-cache

3. Stop docker compose docker containers

sudo docker compose down

4. Start the docker compose containers

sudo docker compose up -d

5. Check the docker connect container logs

sudo docker compose logs -f connect

6. Informix Kafka connect json

{

  "name": "informix-source-connector",

  "config": {

    "connector.class": "io.debezium.connector.informix.InformixConnector",

    "tasks.max": "1",

    "database.hostname": "172.27.xx.xx",

    "database.port": "1528",

    "database.user": "xxxx",

    "database.password": "xxxx@23",

    "database.dbname": "xxxx",

    "database.server.name": "informix",

    "database.informixserver":"debezium_test",

    "topic.prefix": "informixcdc",

    "table.include.list": "table1",

    "snapshot.mode": "schema_only",

    "name": "informix-source-connector",

    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",

    "schema.history.internal.kafka.topic": "schema-changes.informix"

  }

}

7. Deploy Informix source connector to Kafka connect

curl -X POST http://localhost:8083/connectors -H "Content-Type: application/json" -d @informix-source.json|jq

8. Check the status of the connector

sudo curl http://localhost:8083/connectors/informix-source-connector/status | jq

9. Check the Kafka connect deployed connectors

curl -s http://localhost:8083/connector-plugins | jq

10. Delete Informix source connector

sudo curl -X DELETE http://localhost:8083/connectors/informix-source-connector

11. Login to Kafka connect container 

sudo docker exec -it cdc1-connect-1 sh

12. Oracle JDBC sink connector

{

  "name": "oracle-sink-connector",

  "config": {

    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",

    "tasks.max": "1",

    "topics": "informixcdc.informix.table1",

    "connection.url": "jdbc:oracle:thin:@//172.27.xx.xxx:1521/PDB_TST",

    "connection.username": "TEST",

    "connection.password": "test123",

    "connection.driver": "oracle.jdbc.OracleDriver",

    "insert.mode": "upsert",

    "delete.mode": "none",

    "primary.key.fields": "mobile_no",

    "primary.key.mode": "record_value",

    "table.name.format": "table1",

    "auto.evolve": "false",

    "auto.create": "false",

    "dialect.name": "OracleDatabase",

    "schema.evolution":"basic"

  }

}

13. Deploy Oracle sink connector

curl -X POST -H "Content-Type: application/json" --data @oracle-sink.json http://localhost:8083/connectors

14. Check Oracle sink connector status

curl http://localhost:8083/connectors/oracle-sink-connector/status|jq

15. Restart Oracle sink connector

curl -X POST http://localhost:8083/connectors/oracle-sink-connector/tasks/0/restart

16. Check configuration of Oracle connector

curl http://localhost:8083/connectors/oracle-sink-connector/config | jq

17. Login to Kafka and check first 5 messages in the Topic

docker exec -it cdc1-kafka_1 sh -c "kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic informixcdc.informix.table1 --from-beginning"

/bin/kafka-console-consumer   --bootstrap-server kafka:9092   --topic informixcdc.informix.smgt004   --from-beginning   --max-messages 5

18. Retrieve details of the Topic

/bin/kafka-topics --bootstrap-server kafka:9092 --describe --topic informixcdc.informix.smgt004

Check certificate expiry using Python

 Following python script can use to check HTTPS certifcate expiry.

import ssl
import socket
import requests
from datetime import datetime

# Configuration
HOST = "mobitel.lk"  # Change to your domain
PORT = 443
THRESHOLD_DAYS = 30  # Alert before expiry (days)
API_URL = "https://your-api.com/alert"  # REST API to notify

def get_ssl_expiry(host, port):
    context = ssl.create_default_context()
    with socket.create_connection((host, port)) as sock:
        with context.wrap_socket(sock, server_hostname=host) as ssock:
            cert = ssock.getpeercert()
            expiry_date = datetime.strptime(cert['notAfter'], "%b %d %H:%M:%S %Y %Z")
            return expiry_date

def send_alert():
    data = {"message": f"SSL certificate for {HOST} is expiring soon!"}
    response = requests.post(API_URL, json=data)
    print(f"Alert sent: {response.status_code}")
# Check expiry

expiry_date = get_ssl_expiry(HOST, PORT)
days_left = (expiry_date - datetime.now()).days
if days_left <= THRESHOLD_DAYS:
    send_alert()
    print(f"Certificate expiring in {days_left} days. Alert sent!")
else:
    print(f"Certificate is valid for {days_left} more days.")