Using Debezium Connector for Oracle with LogMiner and without XSTREAM

[Max Alpert]

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](https://debezium.io/documentation/reference/stable/connectors/oracle.html#setting-up-oracle) 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][1]][1]


  [1]: https://i.stack.imgur.com/woa7L.png
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.

[Chris Cranford]

HI Max -

So the one thing I noticed in your connector configuration is that you did not specify a "database.pdb.name" configuration option.  Without setting this option, the connector will connect to the root database.  Since the database is deployed with container database support (CDB) enabled, the connector is trying to examine and send changes from the root database only, not the PDB which is why you don't see changes.  Please be sure you set the "database.pdb.name" to "ORCLPDB1" so that the right database is mined for changes.

I would also suggest that you don't create tables in the c##dbzuser tablespace.  This tablespace should be reserved for connector-specific tables and using "c##dbzuser.*" as a table.include.list would mean that we'll be mining and excluding lots of unnecessary traffic.  You should ideally use the "debezium" user you set-up for creating tables, and performing DDL/DML operations. In this case you would then set "table.include.list=DEBEZIUM.*" or "schema.include.list=DEBEZIUM" in your configuration instead.

HTH,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/6fa8e983-d704-4d28-bf7c-b83cb1e763d2n%40googlegroups.com.