Change Data Capture from Oracle with StreamSets Data Collector

Change Data Capture from Oracle with StreamSets Data Collector

Franck PachotToday's guest post is by Franck Pachot, an Oracle Consultant at dbi services in Switzerland. Franck has over 20 years of experience in Oracle, covering every aspect of the database from architecture and data modeling to tuning and operation. Franck recently documented his experiences testing StreamSets Data Collector‘s Oracle CDC origin, and kindly allowed us to repost his blog entry here.

With the trend of CQRS architectures where the transactions are streamed to a bunch of heterogenous eventually consistent polyglot-persistence microservices, logical replication and change data capture (CDC) becomes an important component, even at the architecture design phase. This is good for existing product vendors such as Oracle GoldenGate (which must be licensed even to use only the CDC part in the Oracle Database as Streams is going to be desupported) or Dbvisit replicate to Kafka. But also for open source projects. There are some ideas for running on Debezium and VOODOO but they are not yet released.

Today I tested the StreamSets Oracle CDC OriginStreamSets Data Collector is an open source project, started by former Cloudera and Informatica employees, to define pipelines streaming data from data sources. It is easy, simple and has a bunch of destinations possible. The Oracle CDC origin is based on LogMiner which means that it is easy but may have some limitations (mainly datatypes, DDL replication and performance).

Install

The installation guide is online. I choose the easiest way for testing as they provide a Docker container:

And that’s all. I am ready to connect with http on port 18630.

The default user/password is admin/admin

StreamSets Login

The GUI looks simple and efficient. There’s a home page where you define the ‘pipelines’ and monitor them running. In the pipelines, we define sources and destinations. Some connectors are already installed, others can be automatically installed. For Oracle, as usual, you need to download the JDBC driver yourself because Oracle doesn’t allow to get it embedded for legal reasons. I’ll do something simple here just to check the mining from Oracle.

In ‘Package Manager’ (the little gift icon on the top) go to JDBC and check ‘install’ for the streamsets-datacollector-jdbc-lib library.

Install JDBC Stage Library
Then in ‘External Libraries’, install (with the ‘upload’ icon at the top) the Oracle jdbc driver (ojdbc8.jar).

Install Oracle JDBC driver
I’ve also installed the MySQL one for future tests:

Oracle CDC pipeline

I’ll use the Oracle Change Data Capture here, based on Oracle LogMiner. The GUI is very easy: just select ‘Oracle CDC’ as source in a new pipeline. Click on it and configure it. I’ve set the minimum here.
In JDBC tab I’ve set only the JDBC Connection String to: jdbc:oracle:thin:scott/tiger@//192.168.56.188:1521/pdb1 which is my PDB (I’m on Oracle 18c here and multitenant is fully supported by StreamSets). In the Credentials tab I’ve set sys as sysdba as username and its password. The configuration can also be displayed as JSON and here is the corresponding entry:

I provided SYSDBA credentials and only the PDB service, but it seems that StreamSets figured out automatically how to connect to the CDB (as LogMiner can be started only from CDB$ROOT). The advantage of using LogMiner here is that you need only a JDBC connection to the source – but of course, it will use CPU and memory resource from the source database host in this case.

Then I defined the replication in the Oracle CDC tab: Schema to ‘SCOTT’ and Table Name Pattern to ‘%’. Initial Change as ‘From Latest Change’ as I just want to see the changes and not actually replicate for this first test. But of course, we can define a SCN here which is what must be used to ensure consistency between the initial load and the replication. Dictionary Source to ‘Online Catalog’ – this is what will be used by LogMiner to map the object and column IDs to table names and column names. But be careful as table structure changes may not be managed correctly with this option.

I’ve left the defaults. I can’t think yet about a reason for capturing the ‘select for update’, but it is there.

Named Pipe destination

I know that the destination part is easy. I just want to see the captured changes here and I took the easiest destination: Named Pipe where I configured only the Named Pipe (/tmp/scott) and Data Format (JSON)

Supplemental logging

The Oracle redo log stream is by default focused only on recovery (replay of transactions in the same database) and contains only the minimal physical information requried for it. In order to get enough information to replay them in a different database we need supplemental logging for the database, and for the objects involved:

Run

And that’s all. Just run the pipeline and look at the logs:

Data Collector Log

StreamSet Oracle CDC pulls continuously from LogMiner to get the changes. Here are the queries that it uses for that:

This starts to mine between two timestamps. I suppose that it will read the SCNs to get finer grain and avoid overlapping information.

And here is the main one:

This reads the redo records. The operation codes 7 and 36 are for commit and rollbacks. The operations 1, 3, 2, 25 are those that we want to capture (insert, update, delete, select for update) and were defined in the configuration. Here the pattern ‘%’ for the SCOTT schema has been expanded to the table names. As far as I know, there’s no DDL mining here to automatically capture new tables.

Capture

Then I ran this simple insert (I’ve added a primary key on this table as it is not there from utlsampl.sql):

And I committed (as it seems that StreamSet buffers the changes until the end of the transaction)

and here I got the message from the pipe:

The graphical interface shows how the pipeline is going:

StreamSets Data Collector UI

I’ve tested some bulk loads (direct-path inserts) and it seems to be managed correctly. Actually, this Oracle CDC is based on LogMiner so it is fully supported (no mining of proprietary redo stream format) and limitations are clearly documented.

Monitoring

Remember that the main work is done by LogMiner, so don’t forget to look at the alert.log on the source database. With big transactions, you may need large PGA (but you can also choose buffer to disk). If you have Oracle Tuning Pack, you can also monitor the main query which retrieves the redo information from LogMiner:

Oracle UI
You will see a different SQL_ID because the filter predicates uses literals instead of bind variables (which is not a problem here).

Conclusion

This product is very easy to test, so you can do a proof of concept within a few hours and test for your context: supported datatypes, operations and performance. By easy to test, I mean: very good documentation, very friendly and responsive graphical interface, and very clear error messages.

Thanks, Franck! If you'd like to contribute a guest post on anything StreamSets-related, please get in touch.

Share This Article :

Related Posts

Schedule a Demo
Receive Updates

Receive Updates

Join our mailing list to receive the latest news from StreamSets.

You have Successfully Subscribed!

Pin It on Pinterest