How can I use oracdc with Oracle 10.2.0.4 EE

[iamjavadev1]

   I want to use oracdc with oracle 10 and detect change to kafka but i don't know how to configure it. Can you tell me the steps to configure and testing for use?

Thank you.

[averemee-si]

Hi iamjavadev1,

The following steps need to be performed in order to prepare the Oracle database so the oracdc Connector can be used.

Enabling Oracle RDBMS ARCHIVELOG mode

Log in to SQL*Plus as SYSDBA and check results of query

select LOG_MODE from V$DATABASE

If the query returns ARCHIVELOG, it is enabled. Skip ahead to Enabling supplemental log data.
If the query returns NOARCHIVELOG :

shutdown immediate
startup mount
alter database archivelog;
alter database open;

To verify that ARCHIVELOG has been enabled run again

select LOG_MODE from V$DATABASE

This time it should return ARCHIVELOG

Enabling supplemental logging

Log in to SQL*Plus as SYSDBA, if you like to enable supplemental logging for whole database:

alter database add supplemental log data (ALL) columns;

Alternatively, to enable only for selected tables and minimal supplemental logging, a database-level option (recommended):

alter database add supplemental log data;
alter table <OWNER>.<TABLE_NAME> add supplemental log data (ALL) columns; 

To verify supplemental logging settings at database level:

select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL
from V$DATABASE;

To verify supplemental logging settings at table level:

select LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS, 'ALWAYS', 'Unconditional', NULL, 'Conditional') ALWAYS
from DBA_LOG_GROUPS;

Creation of non-privileged Oracle user for running LogMiner

create user ORACDC identified by ORACDC
  default tablespace SYSAUX
  temporary tablespace TEMP
  quota unlimited on SYSAUX;
grant
  CREATE SESSION,
  SELECT ANY TRANSACTION,
  SELECT ANY DICTIONARY,
  EXECUTE_CATALOG_ROLE
to ORACDC;
grant select on V_$LOGMNR_CONTENTS to ORACDC;
grant execute on DBMS_LOGMNR to ORACDC;

Enable supplemental logging for required tables, below examples for schema SCOTT (The story behind scott/tiger - the default login/pass for Oracle)

sqlplus scott/tiger
alter table DEPT add supplemental log data (ALL) columns;
alter table EMP add supplemental log data (ALL) columns;
alter table BONUS add supplemental log data (ALL) columns;
alter table SALGRADE add supplemental log data (ALL) columns;

Set valid parameters in $KAFKA_HOME/config/connect-standalone.properties:

....
bootstrap.servers=<KAFKA-BROKER-HOST>:<KAFKA-BROKER-PORT>
....
offset.storage.file.filename=<FILE-NAME-WHERE-CONNECT-WILL-STORE-OFFSET>
....
plugin.path=<PATH-TO-DIRECTORY-WITH-oracdc-XXXX-standalone.jar>

Create oracdc configuration file $KAFKA_HOME/config/oracdc-test01.properties:

name=oracdc-test01
connector.class=solutions.a2.cdc.oracle.OraCdcLogMinerConnector
tasks.max=1

a2.jdbc.url=jdbc:oracle:thin:@//<ORACLE_HOST>:<ORACLE_PORT>/<ORACLE_SID>
a2.jdbc.username=ORACDC
a2.jdbc.password=ORACDC

Create oracdc startup script $KAFKA_HOME/bin/oracdc-test01.sh:

#!/bin/sh
KAFKA_HOME=<PATH-TO-TOP-KAFKA-DIRECTORY>
ORACDC_HOME=<PATH-TO-ORACDC-TOP-DIR>
$KAFKA_HOME/bin/connect-standalone.sh $KAFKA_HOME/config/connect-standalone.properties $KAFKA_HOME/config/oracdc-test01.properties

Run script created at previous step

It's much easier to work with Confluent Platform, there are more configuration and tuning options but for start this is enough

Feel free to contact me (averemee@a2.solutions), in case of problems we can schedule a call using Google Meet or another conferencing tool.

Regards,
Aleksei