Setup Debezium Oracle Conenctor

README.MD

Tutorial

https://www.youtube.com/watch?v=mzho5QS6CSk

Install Oracle

Clone following repo, download oracle binaries at put in the correct directory, then build docker image using shell script https://github.com/oracle/docker-images/blob/main/OracleDatabase/SingleInstance/README.md

Build Oracle Docker Image

Start Oracle

Create recovery area folder

/opt/oracle/oradata/recovery_area otherwise following error might be observerd

SQL> ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

SQL> Disconnected

The Oracle base remains unchanged with value /opt/oracle

#####################################

########### E R R O R ###############

DATABASE SETUP WAS NOT SUCCESSFUL!

Please check output for further info!

########### E R R O R ###############

#####################################

Setup Logminer

Run following script from Oracle docker container https://github.com/debezium/oracle-vagrant-box/blob/main/setup-logminer.sh

curl https://raw.githubusercontent.com/debezium/oracle-vagrant-box/main/setup-logminer.sh | sh

Create Sample database

https://github.com/debezium/debezium-examples/blob/main/tutorial/debezium-with-oracle-jdbc/init/inventory.sql

curl https://raw.githubusercontent.com/debezium/debezium-examples/main/tutorial/debezium-with-oracle-jdbc/init/inventory.sql | sqlplus debezium/dbz@//localhost:1521/orclpdb1

note check the name of the tns service to give correct service name in sqlplsus command above

cd $ORACLE_HOME/network/admin
cat tnsnames.ora
lsnrctl status

buildOracle19.3Image.sh

./buildContainerImage.sh -v 19.3.0 -t oracle19.3 -e

gistfile1.txt

connect:
    image: connect-with-more-connectors
    hostname: connect
    extra_hosts:
    #Use database as host which points to the IP of the host machine. This is used in connector to connect with Oracle DB
       -  "database:192.168.29.202"
    container_name: connect
    depends_on:
      - broker
      - schema-registry
    ports:
      - "8083:8083"
    environment:
      CONNECT_BOOTSTRAP_SERVERS: 'broker:29092'
      CONNECT_REST_ADVERTISED_HOST_NAME: connect
      CONNECT_REST_PORT: 8083
      CONNECT_GROUP_ID: compose-connect-group
      CONNECT_CONFIG_STORAGE_TOPIC: docker-connect-configs
      CONNECT_CONFIG_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_OFFSET_FLUSH_INTERVAL_MS: 10000
      CONNECT_OFFSET_STORAGE_TOPIC: docker-connect-offsets
      CONNECT_OFFSET_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_STATUS_STORAGE_TOPIC: docker-connect-status
      CONNECT_STATUS_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_KEY_CONVERTER: org.apache.kafka.connect.storage.StringConverter
      CONNECT_VALUE_CONVERTER: io.confluent.connect.avro.AvroConverter
      CONNECT_VALUE_CONVERTER_SCHEMA_REGISTRY_URL: http://schema-registry:8081
      # CLASSPATH required due to CC-2422
      CLASSPATH: /usr/share/java/monitoring-interceptors/monitoring-interceptors-6.1.1.jar
      CONNECT_PRODUCER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringProducerInterceptor"
      CONNECT_CONSUMER_INTERCEPTOR_CLASSES: "io.confluent.monitoring.clients.interceptor.MonitoringConsumerInterceptor"
      #CONNECT_PLUGIN_PATH: "/usr/share/java,/usr/share/confluent-hub-components,/data/connectors"
      CONNECT_PLUGIN_PATH: "/usr/share/java,/usr/share/confluent-hub-components"
      CONNECT_LOG4J_LOGGERS: org.apache.zookeeper=ERROR,org.I0Itec.zkclient=ERROR,org.reflections=ERROR

KafkaConnectDockerFileWithAdditionalConnectors

FROM confluentinc/cp-kafka-connect-base:6.1.1

RUN   confluent-hub install --no-prompt hpgrahsl/kafka-connect-mongodb:1.1.0 \
   && confluent-hub install --no-prompt microsoft/kafka-connect-iothub:0.6 \
   && confluent-hub install --no-prompt wepay/kafka-connect-bigquery:1.1.0 \
   && confluent-hub install --no-prompt confluentinc/kafka-connect-jdbc:latest \
   && confluent-hub install --no-prompt confluentinc/kafka-connect-datagen:0.1.0 \
   && confluent-hub install --no-prompt debezium/debezium-connector-postgresql:latest

# -----------
# Install Debezium Oracle connector and required libraries
# -- Download
RUN curl "https://repo1.maven.org/maven2/io/debezium/debezium-connector-oracle/1.8.0.Final/debezium-connector-oracle-1.8.0.Final-plugin.tar.gz" | \
   tar xvfz - --directory /usr/share/java/

# -- Install the required library files
USER root
RUN yum clean all && yum update -y
RUN yum install -y unzip
RUN wget "https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-basiclite-linux.x64-19.6.0.0.0dbru.zip" -O /tmp/ic.zip
RUN unzip /tmp/ic.zip -d /usr/share/java/debezium-connector-oracle/
RUN cp  /usr/share/java/debezium-connector-oracle/instantclient_19_6/ojdbc8.jar /usr/share/java/debezium-connector-oracle/ojdbc8.jar
RUN cp  /usr/share/java/debezium-connector-oracle/instantclient_19_6/ojdbc8.jar /usr/share/java/kafka/ojdbc8.jar

startOracle19.3.sh

docker run --name oracle19 -p 1521:1521 -e ORACLE_PWD=top_secret -v /Users/sujit/oracle/:/opt/oracle/oradata oracle19.3

TestConnector.json

{
  "name": "Debezium-Test-Timestamp1",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "key.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "transforms": "TimestampConverter",
    "transforms.TimestampConverter.type": "org.apache.kafka.connect.transforms.TimestampConverter$Value",
    "transforms.TimestampConverter.target.type": "string",
    "transforms.TimestampConverter.field": "timestamp",
    "transforms.TimestampConverter.format": "dd-MM-yyyy hh:mm:ss",
    "database.hostname": "database",
    "database.port": "1521",
    "database.user": "c##dbzuser",
    "database.password": "dbz",
    "database.server.name": "test",
    "database.dbname": "ORCLCDB",
    "database.pdb.name": "ORCLPDB1",
    "database.connection.adapter": "LogMiner",
    "time.precision.mode": "connect",
    "database.history.kafka.bootstrap.servers": "broker:29092",
    "database.history.kafka.topic": "history",
    "table.include.list": "DEBEZIUM.CUSTOMERS",
    "database.schema": "DEBEZIUM"
  }
}

How to connect to DB with Debezium user in Dbeaver