Oracle database CDC to Kafka on docker

Capture all row-based DML changes from the Oracle database and stream these changes to Kafka. The change data capture logic is based on the Oracle LogMiner solution.

1. Test environment preparation

(1)A linux cloud server is configured as a 2 core 4G 2M bandwidth 50G system disk

(2)install docker on cloud server

2. Set up the environment

(1)Pull the open source version image of Lenses

docker pull lensesio/fast-data-dev

Start the container

docker run -d --name kafka-lenses -p 2181:2181 -p 3030:3030 -p 8081:8081 -p 8082:8082 -p 8083:8083 -p 9092:9092 -v /home/kafka-lenses:/data -e ADV_HOST=192.168.60.165 lensesio/fast-data-dev

Note:

Change 192.168.60.165 to the IP address of the actual host machine. If you don’t set it in this way, you may not be able to access Kafka on other machines.
-v /home/kafka-lenses:/data means to mount the /home/kafka-lenses directory of the host into the container /data At present, it is guaranteed that the data can be persisted after restarting the container and will not be lost.

After the container is started, use a browser to visit http://192.168.60.165:3030, you can see the following interface, which means the startup is successful

Execute the following command to enter the container

docker exec -it kafka-lenses /bin/bash

Execute the following command to create a topic named oracle_test:

kafka-topics --zookeeper 127.0.0.1:2181 --create --topic oracle_test --partitions 3 --replication-factor 1

3. Build oracle database docker image

docker pull helowin/oracle_11g

start oracle image

docker run -d -p 1521:1521 --name oracle helowin/oracle_11g

Enter the oracle container configuration environment variables

docker exec -it oracle bash
su root
password：helowin
vi etc/profile

Edit the following content and save

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2    
export ORACLE_SID=helowin     
export PATH=$ORACLE_HOME/bin:$PATH

Environment variables take effect

source /etc/profile
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
su - oracle

The database must be in archivelog mode, and supplemental logging must be enabled

sqlplus /nolog
conn /as sysdba
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>alter database add supplemental log data (all) columns;

In order to successfully execute the connector, the connector must be started with a privileged Oracle user. If the given user has the DBA role, you can skip this step. Otherwise, you need to execute the following script to create a privileged user

SQL>create role logmnr_role;
SQL>grant create session to logmnr_role;
SQL>grant execute_catalog_role,select any transaction ,select any dictionary to logmnr_role;
SQL>create user kafka identified by kafka;
SQL>grant logmnr_role to kafka;
SQL>grant connect,resource,dba to kafka;
SQL>alter user kafka quota unlimited on users;

Create oracle test table MYKAFKA

4. Install kafka-connect-oracle plugin

clone plugin https://github.com/erdemcer/kafka-connect-oracle，Package and compile

Copy kafka-connect-oracle-master\target\kafka-connect-oracle-1.0.jar and kafka-connect-oracle-master\lib\ojdbc7.jar to the container kafka: /opt/kafka/lib:

docker cp kafka-connect-oracle-1.0.jar kafka:/opt/kafka/libs/kafka-connect-oracle-1.0.jar
docker cp ojdbc7.jar kafka:/opt/kafka/libs/ojdbc7.jar
docker exec -it kafka /bin/bash

modify /opt/kafka/config/connect-distributed.properties

bootstrap.servers=192.168.60.165:9092
rest.port=8083
rest.advertised.port=8083

start shell script connect-distributed.sh

connect-distributed.sh /opt/kafka/config/connect-distributed.properties

visit http://192.168.60.165:8000 to Create connection OracleSourceConnector

connector.class=com.ecer.kafka.connect.oracle.OracleSourceConnector
reset.offset=false
db.hostname=172.21.0.11
tasks.max=1
db.user.password=kafka
table.blacklist=KAFKA.AA
table.whitelist=KAFKA.MYKAFKA
db.user=kafka
db.port=1521
db.fetch.size=1
multitenant=false
topic=oracle_test
parse.dml.data=true
db.name=helowin
db.name.alias=test

Real-time synchronization and consumption of test data
Add, modify, and delete the database MYKAFKA, and consume kafka simultaneously

Synchronous consumer of kafka

docker exec kafka kafka-console-consumer.sh --bootstrap-server 172.21.0.11:9092 --from-beginning --topic oracle_test

You can see that the oracle data has been synchronized to the topic of the kafka cluster