Install Debezium Oracle Connector using AMQ Streams (Kafka)

There are two sections to set up the Debezium Oracle connector with AMQ Streams on OpenShift 4.7. In the first section we will look at the steps to set up Oracle Database and in the second section, we will look at configuring the OpenShift environment to configure the Kafka Connect. You can skip the first section, if you have an existing Oracle DB.

Steps to set up Oracle Database :

Note : Please review the steps here with your Oracle DBA. These steps are here to help you set up quickly for development / POC purposes ONLY. Database needs to be accessible by OpenShift AMQ Kafka cluster components.

Oracle 19 Database from Oracle container registry (https://container-registry.oracle.com/ords/f?p=113:10::::::) is used.

1. Create ~/tmp/oracle19/ou2 and ~/tmp/oracle19/oradata folders on your local machine where you are going to run docker container.
2. The following shows how to start the Oracle database using docker run. Please wait until ‘docker ps’ status for oracle container shows as ‘healthy’ ONLY.

docker run -it — name oracledb19 -p 1521:1521 -v ~/tmp/oracle19/u02:/ORCL/u02 -v ~/tmp/oracle19/oradata:/opt/oracle/oradata -e ORACLE_PWD=top_secret container-registry.oracle.com/database/enterprise:19.3.0.0

Docker logs should have this.

DONE: Executing user defined scripts
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
Executing user defined scripts
/opt/oracle/runUserScripts.sh: running /opt/oracle/scripts/startup/runDatapatch.sh
Datafiles are already patched. Skipping datapatch run.
DONE: Executing user defined scripts
The following output is now a tail of the alert.log:
ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE “USERS”
ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE “USERS”
2021–06–29T14:27:42.363950+00:00
ALTER SYSTEM SET control_files=’/opt/oracle/oradata/ORCLCDB/control01.ctl’ SCOPE=SPFILE;
2021–06–29T14:27:42.368475+00:00
ALTER SYSTEM SET local_listener=’’ SCOPE=BOTH;
ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed: ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
XDB initialized.

3. ssh into the container and create from there the folder using the oracle user (to have the correct permissions) :

docker exec -it oracledb19 bash
cd /opt/oracle/oradata/
mkdir recovery_area

4. An automated script has been provided in order to configure the database with LogMiner support. Download the following script file and copy it into the container at “/home/oracle” location and run it. When the script execution has completed, the database is fully configured and ready to send change events to Debezium.

https://raw.githubusercontent.com/debezium/oracle-vagrant-box/master/setup-logminer.sh

docker cp ~/Downloads/setup-logimner.sh oracledb19:/home/oracle
docker exec -it — user root oracledb19 bash
cd /home/oracle — in container
Run ./setup-logimner.sh
(or)
cat /setup-logminer.sh | docker exec -i oracledb19 bash

5. Insert test data into the database. Here is a sample from Debezium repo. You can run locally on your machine where the Oracle DB docker container is running.

https://raw.githubusercontent.com/debezium/debezium-examples/master/tutorial/debezium-with-oracle-jdbc/init/inventory.sql

cat inventory.sql | docker exec -i oracledb19 sqlplus debezium/dbz@//localhost:1521/ORCLPDB1

Steps to set up Kafka Connect:

1. Download the Oracle Debezium Connector from AMQ developer’s site. https://developers.redhat.com/content-gateway/file/debezium-connector-oracle-1.4.2.Final-redhat-00002-plugin.zip
2. Download the Oracle client drivers from here (https://www.oracle.com/database/technologies/oracle-database-software-downloads.html).
3. Create a folder and copy the zip from (1) inside it and from (2) extract the ojdbc8.jar and xstreams.jar and copy it in the folder.
4. Create a docker image using the below content and push it into a repo accessible to OpenShift AMQ Streams / Kafka cluster.

FROM registry.redhat.io/amq7/amq-streams-kafka-27-rhel7:1.7.0
USER root:root
RUN mkdir -p /opt/kafka/plugins/oracle
COPY debezium-connector-oracle-1.4.2.Final-redhat-00002-plugin.zip /opt/kafka/plugins/oracle
RUN unzip /opt/kafka/plugins/oracle/debezium-connector-oracle-1.4.2.Final-redhat-00002-plugin.zip -d /opt/kafka/plugins/oracle
COPY *.jar /opt/kafka/plugins/oracle
RUN rm /opt/kafka/plugins/oracle/debezium-connector-oracle-1.4.2.Final-redhat-00002-plugin.zip
USER 1001 

Example :

docker build . -t quay.io/svalluru1/rh-kafkaconnect-oracle
docker push quay.io/svalluru1/rh-kafkaconnect-oracle

5. Create AMQ Kafka Cluster.

apiVersion: kafka.strimzi.io/v1beta2
kind: Kafka
metadata:
name: my-cluster
spec:
kafka:
config:
offsets.topic.replication.factor: 3
transaction.state.log.replication.factor: 3
transaction.state.log.min.isr: 2
log.message.format.version: ‘2.7’
inter.broker.protocol.version: ‘2.7’
version: 2.7.0
storage:
type: ephemeral
replicas: 3
listeners:
- name: plain
port: 9092
type: internal
tls: false
- name: tls
port: 9093
type: route
tls: true
entityOperator:
topicOperator: {}
userOperator: {}
zookeeper:
storage:
type: ephemeral
replicas: 3

6. Create Kafka Connect which will pull the docker image containing Oracle Debezium connector with Oracle Drivers.

apiVersion: kafka.strimzi.io/v1beta1
kind: KafkaConnect
metadata:
name: my-connect-cluster-oracle
annotations:
strimzi.io/use-connector-resources: “true”
spec:
image: quay.io/svalluru1/rh-kafkaconnect-oracle:latest
replicas: 1
bootstrapServers: my-cluster-kafka-bootstrap:9092

7. Create a KafkaConnector instance with Oracle database configuration. Make sure your Database is accessible to the OpenShift cluster.

apiVersion: “kafka.strimzi.io/v1alpha1”
kind: “KafkaConnector”
metadata:
name: “oracle-connector”
labels:
strimzi.io/cluster: my-connect-cluster-oracle
spec:
class: io.debezium.connector.oracle.OracleConnector
tasksMax: 1
config:
database.url: jdbc:oracle:thin:@//136.56.80.163:1521/ORCLCDB
database.user: c##dbzuser
database.password: dbz
database.server.name: dbserver1
database.dbname: ORCLCDB
database.connection.adapter: logminer
database.pdb.name: ORCLPDB1
database.history.kafka.bootstrap.servers: my-cluster-kafka-bootstrap:9092
database.history.kafka.topic: schema-changes.inventory
schema.include.list: DEBEZIUM

8. Connect to one of the Kafka Brokers and read the kafka topics for messages about the DB tables.

You should see similar Kafka Topics related to your Database tables which confirms that Debezium Connector is able to make the connectivity between Tables and Kafka cluster.

Troubleshooting :

• Any errors related to Kafka Connector, please check the Logs of Kafka Connect Pod.

References : Some of the references are just for broader understanding.

https://debezium.io/documentation/reference/connectors/oracle.html

https://developers.redhat.com/blog/2021/03/25/db2-and-oracle-connectors-coming-to-debezium-1-4-ga#

https://developers.redhat.com/blog/2021/04/19/capture-oracle-database-events-in-apache-kafka-with-debezium#

https://github.com/debezium/oracle-vagrant-box