Search code examples
oracle-databaseapache-kafkaapache-kafka-connectdebezium

Using Debezium Connector for Oracle with LogMiner and without XSTREAM

I am trying to create a connector for oracle using LogMiner adapter. I preconfigured my oracle db in that way. My dockerfile

FROM store/oracle/database-enterprise:12.2.0.1

# creating directory inside the container, where all sql scripts will be locaited
#WORKDIR /home/oracle/setup/custom_scripts

WORKDIR /opt/oracle/oradata

WORKDIR /opt/oracle/oradata/recovery_area

WORKDIR /opt/oracle/oradata/ORCLCDB
WORKDIR /opt/oracle/oradata/ORCLCDB/ORCLPDB1/

WORKDIR /u01/app/oracle/product/12.2.0/dbhome_1/inventory
WORKDIR /u01/app/oracle/product/12.2.0/dbhome_1/bin

#Running my custom scripts
COPY configDBora.sh /home/oracle/setup/

RUN chgrp 54321 /opt/oracle/oradata
RUN chown 54321 /opt/oracle/oradata

RUN chgrp 54321 /opt/oracle/oradata/recovery_area
RUN chown 54321 /opt/oracle/oradata/recovery_area

RUN chgrp 54321 /u01/app/oracle/product/12.2.0/dbhome_1/inventory
RUN chown 54321 /u01/app/oracle/product/12.2.0/dbhome_1/inventory

RUN chgrp 54321 /u01/app/oracle/product/12.2.0/dbhome_1/bin
RUN chown 54321 /u01/app/oracle/product/12.2.0/dbhome_1/bin

My configDBora.sh based on Debezium oracle set up that I copy to image when building it

echo "STAGE 1"
   sqlplus /nolog 2>&1 <<EOF
    CONNECT sys/Admin123 AS SYSDBA
    alter system set db_recovery_file_dest_size = 15G;
    alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
    shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    archive log list
    exit;
EOF
echo "STAGE 2"
   # Enable LogMiner required database features/settings
   sqlplus sys/Admin123 as sysdba <<- 'EOF'
     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

     SELECT SUPPLEMENTAL_LOG_DATA_MIN min,
            SUPPLEMENTAL_LOG_DATA_PK pk,
            SUPPLEMENTAL_LOG_DATA_UI ui,
            SUPPLEMENTAL_LOG_DATA_FK fk,
            SUPPLEMENTAL_LOG_DATA_ALL "all"
         from v$database;
     ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
     exit;
EOF
echo "STAGE 3"
   # Create Log Miner Tablespace and User
   sqlplus sys/Admin123 as sysdba <<- EOF
     CREATE TABLESPACE LOGMINER_TBS DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
     exit;
EOF

   sqlplus sys/Admin123 as sysdba <<- EOF
     alter session set container=ORCLPDB1;
     CREATE TABLESPACE LOGMINER_TBS DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
     exit;
EOF
echo "STAGE 4"
   sqlplus sys/Admin123 as sysdba <<- 'EOF' #THE  MOST IMPORTANT PART
     CREATE USER c##dbzuser IDENTIFIED BY dbz DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS CONTAINER=ALL;
     GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL;
     GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$DATABASE TO c##dbzuser CONTAINER=ALL;
     GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
     GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ANY DICTIONARY TO c##dbzuser CONTAINER=ALL;
     GRANT LOGMINING TO c##dbzuser CONTAINER=ALL;
     GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL;
     GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL;
     GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL;
     exit;
EOF
echo "STAGE 5"
   sqlplus sys/Admin123 as sysdba <<- EOF
     alter session set container=ORCLPDB1;
     CREATE USER debezium IDENTIFIED BY dbz;
     GRANT CONNECT TO debezium;
     GRANT CREATE SESSION TO debezium;
     GRANT CREATE TABLE TO debezium;
     GRANT CREATE SEQUENCE to debezium;
     ALTER USER debezium QUOTA 100M on users;
     exit;
EOF
echo "STAGE 6"
   sqlplus sys/Admin123 as sysdba <<- EOF
     alter session set current_schema = C##DBZUSER;
     exit;
EOF

My Source CDC Connector:

    "tasks.max": 1,
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "database.server.name": "server1",
    "database.hostname" : "oracle",
    "database.port" : "1521",
    "database.user" : "c##dbzuser",
    "database.password" : "dbz",
    "database.dbname" : "ORCLCDB",
    "database.history.kafka.bootstrap.servers" : "broker:29092",
    "database.history.kafka.topic": "server1.oracle.history",
    "database.history.skip.unparseable.ddl": "true",
    "include.schema.changes": "true",
    "table.include.list": "C##DBZUSER.*",
    "database.connection.adapter": "logminer",
    "database.tablename.case.insensitive": "true",
    "database.url": "jdbc:oracle:thin:@oracle:1521:ORCLCDB",
    "snapshot.mode": "initial",
    "errors.log.enable": "true",
    
    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "key.converter.schema.registry.url": "http://schema-registry:8081",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schema.registry.url": "http://schema-registry:8081"

Connector launches successfully and generate topics with my tables. enter image description here

When I try to insert into any of this table on oracle db there is no capture of insert (or update) query. Who can tell me where have I gone wrong ? I really need help over this question.

Solution
    1. The first step that I decided to do moving my DB to Oracle 19c Enterprise Edition. I added setup-logminer.sh from my repo based on this debezium repo. I copied setup-logminer.sh to this path /opt/oracle/scripts/extensions/startup/ when I was building a new docker image.
    FROM container-registry.oracle.com/database/enterprise:19.3.0.0

WORKDIR /opt/oracle/oradata

WORKDIR /opt/oracle/oradata/recovery_area

WORKDIR /opt/oracle/oradata/ORCLCDB
WORKDIR /opt/oracle/oradata/ORCLCDB/ORCLPDB1/

WORKDIR /u01/app/oracle/product/19c/dbhome_1/inventory
WORKDIR /u01/app/oracle/product/19c/dbhome_1/bin

#Running my custom scripts
COPY setup-logminer.sh /opt/oracle/scripts/extensions/startup/

RUN chgrp 54321 /opt/oracle/oradata
RUN chown 54321 /opt/oracle/oradata

RUN chgrp 54321 /opt/oracle/oradata/recovery_area
RUN chown 54321 /opt/oracle/oradata/recovery_area

RUN chgrp 54321 /u01/app/oracle/product/19c/dbhome_1/inventory
RUN chown 54321 /u01/app/oracle/product/19c/dbhome_1/inventory

RUN chgrp 54321 /u01/app/oracle/product/19c/dbhome_1/bin
RUN chown 54321 /u01/app/oracle/product/19c/dbhome_1/bin

    I modified a little bit original script of setup-logminer.sh because I had this error in connector logs (STRUCT) type doesn't have a mapping to the SQL database column type.

    1. After that I had to SET AUTOCOMMIT ON. By default in Oracle AUTOCOMMIT is off and I had to turn it on. As I am working in IDE I have to always set AUTO (look image) because it was Manual always. P.S. I also turned on autocommit in Oracle 12c but I had no result and I decided to drop it totally and move to Oracle 19c. Image from Intelllij IDEA

    3. I moved my DB from CDB to PDB because it's bad to work in CDB. You have to use pluggable database.

    {
...
    "table.include.list": "DEBEZIUM.*",
...
}