Versions in use

• Kafka 2.3.0 / Confluent Platform 5.3.0
• Debezium 0.10
• Oracle 11g EE 11.2.0.4

tl;dr

It works, though you need some small adjustments to the DB setup that is described in the Debezium Tutorial Using Oracle. You need a minimum version of 11.2.0.4. You also need an Oracle license for XStream in addition to your 11g database. Watch out for Oracle data types that come through Debezium as a byte array: KSQL can’t read it, so you need some other way to deal with that – I’m going to blog on this in the future!

Starting out

We have an Oracle 11g (11.2.0.4) DB and I wanted to try out a CDC implementation rather than using the JDBC Kafka Connector. Oracle GoldenGate is an option, however Debezium appeared to be a good alternative – without spinning up a GoldenGate server. You still need an Oracle license for XStream however – something that the good folks at Debezium would like to change. This wasn’t an obstacle for me however as my current client have licenses for all Oracle products.

The Oracle 11g (11.2.0.4) EE DB

The Debezium tutorial is based around spinning up an Oracle DB using vagrant. Following the steps would result in you running an Oracle 12.2 DB – no good for my scenario.

I also had a look at Robin Moffat’s blog (always a great resource on Kafka) on Streaming data from Oracle into Kafka. He contrasts the JDBC connector against the Debezium Oracle connector. For this, you need to look after firing up your Oracle DB yourself – Robin provides the link to the Oracle Github account where scripts are available to build a docker image (you still need to download the database binaries from the Oracle Technology Network). However, these scripts aren’t suitable for creating an Oracle 11g EE DB – only XE is supported at that version.

I had a third option – where I’m working atm, I had a suitable docker image already available for my use. This is already in use for automated tests and has a number of flyway scripts which build the database for me. Given this is the closest I had to a production database, it seemed like the best place to start.

I’m afraid you’re still on your own here to work out how to get yourself a suitable DB. Maybe its just lying around – or perhaps like me you have already got it dockerised. This is something you’ll need to bring to the party!

Preparing the Database

The Debezium Oracle Tutorial (“Manual Steps“) tells us that we have some work to do on the DB to get things running. Robin has automated this in a script. Unfortunately, these are all geared to an example DB schema for Oracle 12. Unlike Oracle 12, 11g is not a “containerised” database: “All Oracle databases before Oracle Database 12c were non-CDBs”. I’m now going to take you through the customised steps I carried out.

While I’ll explain the steps one at a time, I have also created a script that applies all of these steps automatically.

Set archive log mode and enable GG replication

docker exec -ti -e ORACLE_SID=db1 oracleDBContainer sqlplus sys/oracle as SYSDBA

alter system set db_recovery_file_dest_size = 5G;
alter system set db_recovery_file_dest = '/home/oracle/app/oracle/oradata/recovery_area' scope=spfile;
alter system set enable_goldengate_replication=true;
shutdown immediate

-- WAIT FOR DB TO SHUTDOWN. START IT UP AGAIN WITH:
startup mount
alter database archivelog;
alter database open;

-- Should show "Database log mode: Archive Mode":
archive log list

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/home/oracle/app/oracle/oradata/db1/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER c##xstrmadmin IDENTIFIED BY xsa DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs;

GRANT CREATE SESSION TO c##xstrmadmin;

BEGIN
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      grantee                 => 'c##xstrmadmin',
      privilege_type          => 'CAPTURE',
      grant_select_privileges => TRUE
   );
END;
/

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;

CREATE TABLESPACE xstream_tbs DATAFILE '/home/oracle/app/oracle/oradata/db1/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER c##xstrm IDENTIFIED BY xs DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs;

GRANT CREATE SESSION TO c##xstrm;
GRANT SELECT ON V_$DATABASE to c##xstrm;
GRANT FLASHBACK ANY TABLE to c##xstrm;
GRANT SELECT_CATALOG_ROLE to c##xstrm;

exit;

Create XStream Outbound server:

docker exec -ti oracleDBContainer sqlplus c##xstrmadmin/xsa@//localhost:1521/db1

DECLARE
  tables  DBMS_UTILITY.UNCL_ARRAY;
  schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
    tables(1)  := NULL;
    schemas(1) := 'debezium';
  DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
    server_name     =>  'dbzxout',
    table_names     =>  tables,
    schema_names    =>  schemas);
END;
/

exit;

Alter the XStream Outbound server to allow the xstrm user to connect to it

docker exec -ti -e ORACLE_SID=db1 oracleDBContainer sqlplus sys/oracle as SYSDBA

BEGIN
  DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
    server_name  => 'dbzxout',
    connect_user => 'c##xstrm');
END;
/

exit;

Bespoke Table Setup

For every database table that you want to stream into Kafka, you need to run the following:

alter table <SCHEMA.TABLENAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
GRANT SELECT ON <SCHEMA.TABLENAME> to c##xstrm;

Automated Script

The previous section walked you through what needs to be done. Of course, this can be automated. You can find the script I wrote for this purpose in my github account here. This simply copies 4 other sql scripts onto the docker container, and then executes them using sqlplus.

As with the previous section, there will be a few differences between my database and yours, so be sure to inspect the scripts and change the necessary settings. For sure, you’ll need to change alterForCdc.sql which specifies the tables that you’re going to be streaming.

Also, the scripts make use of Docker, so be sure you’ve got that available, and have a think about how you’re going to look after the passwords for the users that are created.

Running Debezium

With your DB now suitable configured, we can move on to configuring Debezium itself. The simplest way of running the stack is through docker images. I used docker-compose. For my POC, I decided to use the Debezium images that already had the connectors installed on top of Kafka Connect.

The first thing to be aware of is that (due to licensing requirements) you need to download the Oracle “Instant Client“. The Debezium Oracle documentation also mentions this. Having downloaded it, it needs to be uncompressed (I created a directory for this called “oracle_instantclient” beside my docker-compose file) and copied onto the docker image for kafka connect. This is perhaps easier explained by looking at the Dockerfiles.

Starting at the top, here is my docker-compose file:

If you study this, you’ll spot that “connect” isn’t run directly from a prebuilt image, but instead it is read another Dockerfile. This allows us to install the Oracle “Instant Client” that you’ve downloaded and extracted above. Here’s the Dockerfile for the connect image:

Running “docker-compose up” will give us our stack. At this piint, we’ve got a database ready for streaming plus all the stack we need to do the streaming. We just need the glue – the kafka connect Connector.

Note that I’m also using the Schema Registry as I want to write the output in Avro rather than JSON.

Create the Connector

This is happily back in line with the Debezium Connector for Oracle documentation and also the script used in Robin Moffat’s blog I mentioned earlier. However, there are a couple of extra tweeks needed to make it work for Oracle 11:

Here’s my complete connector:

Note that the “database.history.kafka.bootstrap.servers” property points to the kafka docker container.

Finally?

We should be there now. Congratulations!

If you’ve got data in your database table, then you should find that a topic has been created and it contains your data. Adding, Updating or Deleting data will cause new messages to be written to your topic.

Don’t forget…

You might not quite be home and dry. While your data is sitting in a topic, you’ll likely want to be doing something with it!

I intended to read the data using KSQL, however, I found that some fields in the database had been defined as “NUMBER” without scale or precision. This meant that the connector wrote the value into the topic as a byte array, something which KSQL does not yet support. Stay tuned for a forthcoming blog detailing what I did to read this data!