tl;dr: specify the --user root argument:

docker exec --interactive \
            --tty \
            --user root \
            --workdir / \
            container-name bash

This is a short summary discussing what the options are for integrating Oracle RDBMS into Kafka, as of December 2018 (refreshed June 2020). For a more detailed background to why and how at a broader level for all databases (not just Oracle) see this blog and this talk.

What techniques & tools are there?

Franck Pachot has written up an excellent analysis of the options available here.

The Kafka Connect JDBC Connector by default does not cope so well with:

• NUMBER columns with no defined precision/scale. You may end up with apparent junk (bytes) in the output, or just errors.
• TIMESTAMP WITH LOCAL TIME ZONE. Throws JDBC type -102 not currently supported warning in the log.

Read more about NUMBER data type in the Oracle docs.

tl;dr : How do I make it work?

There are several options:

New in Confluent Platform 4.1.1 : numeric.mapping

• In the connector configuration, set "numeric.mapping":"best_fit"
• New in Confluent Platform 4.1.1 (Doc)

Avoid the problem in the first place

• Change the DDL of the source object. For example:
  • refine the NUMBER ’s precision and scale
  • Use a TIMESTAMP type that is supported

CAST the datatypes in the query

• Pull from the object directly, and use query in the JDBC connector (instead of table.whitelist)—and cast the columns appropriately:

This article is part of a series exploring Streaming ETL in practice. You can read about setting up the ingest of realtime events from a standard Oracle platform, and building streaming ETL using KSQL.

This post shows how we take data streaming in from an Oracle transactional system into Kafka, and simply stream it onwards into Elasticsearch. This is a common pattern, for enabling rapid search or analytics against data held in systems elsewhere.

This is the detailed step-by-step if you want to recreate the process I describe in the Confluent blog here

I used Oracle’s Oracle Developer Days VM, which comes preinstalled with Oracle 12cR2. You can see the notes on how to do this here. These notes take you through installing and configuring:

• Swingbench, to create a sample “Order Entry” schema and simulate events on the Oracle database
• Oracle GoldenGate (OGG, forthwith) and Oracle GoldenGate for Big Data (OGG-BD, forthwith)
  • I’m using Oracle GoldenGate 12.3.1 which includes the Kafka Connect handler as part of its distribution. A connector for earlier versions can be found here. Some of the syntax may differ in the configuration below - if you hit problems then check out an article that I wrote with an earlier version of the tool.
• OGG extract from the Order Entry schema
• Confluent Platform
• KSQL
• Elasticsearch

From this point, I’ll now walk through configuring OGG-BD with the Kafka Connect handler

Some notes that I made on installing and configuring Oracle GoldenGate with Confluent Platform. Excuse the brevity, but hopefully useful to share!

I used the Oracle Developer Days VM for this - it’s preinstalled with Oracle 12cR2. Big Data Lite is nice but currently has an older version of GoldenGate.

Login to the VM (oracle/oracle) and then install some useful things:

sudo rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install -y screen htop collectl rlwrap p7zip unzip sysstat perf iotop
sudo su -
cd /etc/yum.repos.d/
wget http://download.opensuse.org/repositories/shells:fish:release:2/CentOS_7/shells:fish:release:2.repo
yum install fish

Check Oracle version etc:

Here’s where I’ll be!

If you use Google Calendar you can click on individual entries above and select copy to my calendar - which of course you’ll want to do for all the ones I’ve marked as [SPEAKING] :-)

Here’s a list of all the Apache Kafka talks at OpenWorld and JavaOne, most of which I’ll be trying to get to.

The Replicat was kapput:

GGSCI (localhost.localdomain) 3> info rkconnoe

REPLICAT   RKCONNOE  Last Started 2017-09-12 17:06   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:46:34 ago)
Log Read Checkpoint  File /u01/app/ogg/dirdat/oe000000
                     First Record  RBA 0

So checking the OGG error log ggserr.log showed

2017-09-12T17:06:17.572-0400  ERROR   OGG-15051  Oracle GoldenGate Delivery, rkconnoe.prm:  Java or JNI exception:
                              oracle.goldengate.util.GGException: Error detected handling operation added event.
2017-09-12T17:06:17.572-0400  ERROR   OGG-01668  Oracle GoldenGate Delivery, rkconnoe.prm:  PROCESS ABENDING.

So checking the replicat log dirrpt/RKCONNOE_info_log4j.log showed:

There are various reasons for this error, but the one I hit was that the table name is case sensitive, and returned from Oracle by the JDBC driver in uppercase.

If you specify the tablename in your connecter config in lowercase, it won’t be matched, and this error is thrown. You can validate this by setting debug logging (edit etc/kafka/connect-log4j.properties to set log4j.rootLogger=DEBUG, stdout), and observe: (I’ve truncated some of the output for legibility)

Oracle’s excellent Big Data Lite VM has been updated, to version 4.5.

Download it here

Changes:

• CDH 5.5 -> 5.7
• Big Data Spatial and Graph 1.1 -> 1.2
• Big Data Discovery 1.1 -> 1.2
• Oracle Big Data Connectors 4.4 -> 4.5
• Oracle NoSQL 3.5 -> 4.0
• GoldenGate 12.2.0.1 -> 12.2.0.1.1

Recently added to the oracledi project over at java.net is an adaptor enabling Oracle GoldenGate (OGG) to send data to Elasticsearch. This adds a powerful alternative to [micro-]batch extract via JDBC from Oracle to Elasticsearch, which I wrote about recently over at the Elastic blog.

Elasticsearch is a ‘document store’ widely used for both search and analytics. It’s something I’ve written a lot about (here and here for archives), as well as spoken about - preaching the good word, as it were, since the Elastic stack as a whole is very very good at what it does and a pleasure to work with. So, being able to combine that with my “day job” focus of Oracle is fun. Let’s get started!

In Oracle 11g, the DBMS_STATS procedure GATHER_SCHEMA_STATS takes a parameter ‘options’ which defines the scope of the objects processed by the procedure call, as well as the action. It can be either GATHER or LIST (gather the stats, or list out the objects to be touched, respectively), and AUTO, STALE or EMPTY (defining the object selection to process).

• GATHER on its own will gather stats on all objects in the schema
• GATHER EMPTY / LIST EMPTY is self-explanatory - objects with no statistics.
• GATHER STALE / LIST STALE is pretty obvious too - objects that have stale statistics (i.e. have had 10% change to them since statistics were last gathered). NB this 10% can be changed at an object/schema/DB level.
• However, the documentation is ambiguous as to the precise function of GATHER AUTO / LIST AUTO.

There’s even a MOS note, “Differences between GATHER STALE and GATHER AUTO (Doc ID 228186.1)”, which strangely enough - given the precision of its title - doesn’t really explain the difference.

Have you defined CLIENT_ID in your OBIEE RPD yet? You really ought to.

As well as helping track down users of troublesome queries, it also tags dump files with the OBIEE user of an offending query should the worst occur:

For details, see:

• Identify your OBIEE users by setting Client ID in Oracle connection
• Instrumenting OBIEE for tracing Oracle DB calls

Here’s a scenario that’ll be depressingly familiar to most reading this: after ages of running fine, and no changes to the code, a query suddenly starts running for magnitudes longer than it used to.

In this instance it was an ETL step which used to take c.1 hour, and was now at 5 hours and counting. Since it still hadn’t finished, and the gods had conspired to bring down Grid too (unrelated), I generated a SQL Monitor report to see what was happening: [sourcecode language=“sql”] select DBMS_SQLTUNE.REPORT_SQL_MONITOR( type=>‘HTML’, report_level=>‘ALL’,sql_id=>‘939abmqmvcc4d’) as report FROM dual; [/sourcecode] (h/t to Martin Berger for this)

There is a well-documented problem relating to DBMS_STATS.COPY_TABLE_STATS between partitions where high/low values of the partitioning key column were just copied verbatim from the source partition. This particular problem has now been patched (see 8318020.8). For background, see Doug Burns’ blog and his excellent paper which covers the whole topic of statistics on partitioned tables.

This post Maintaining statistics on large partitioned tables on the Oracle Optimizer blog details what the dbms_stats.copy_table_stats does with regards to the high/low values:

Chucking a stick in the spokes of your carefully-tested ETL/BI …

My opinion is that automated stats gathering for non-system objects should be disabled on Oracle Data Warehouses across all environments.

All it does it cover up poor design or implementation which has omitted to consider statistics management. Once you get into the realms of millions or billions of rows of data, the automated housekeeping may well not have time to stat all of your tables on each run. And then it becomes a quasi-lottery when your tables will get processed. Or what if you’re working with intra-day loads (eg. near real-time) - the housekeeping job only runs once a day by default.

Just because something produces the correct numbers on the report, it doesn’t mean you can stop there.

How you are producing those numbers matters, and matters a lot if you have an interest in the long-term health of your system and its ability to scale.

OBIEE is the case in point here, but the principle applies to any architecture with >1 tiers or components.

Let me start with a rhetorical question. The user has got a report which has ten rows of data. Which of the following methods is going to be a more efficient way to generate the report?

Chris Mellor at The Register posted an interesting article a couple of days ago, entitled HP and Violin build Oracle Exadata killer. The slidedeck has been removed from HP’s FTP site, but a bit of Google magic throws up a couple of mirror copies.

It’s an entertaining read (“Do a Proof of Concept! 94% win rate!! We can and do win against Exadata!!”), and a nice illustration of the FUD techniques that companies use in marketing their products against others. Greg Rahn has taken Netezza to task in the past for this, and to be fair at least Netezza had a serious white paper to back up their arguments. HP’s deck (including choice sections such as “How to sell against Exadata”) is IMHO nothing more than a biased set of arguments for salespeople to use to attempt to bullshit customers.

Whilst perusing the Oracle database documentation, I noticed something which caught my eye:

As well as reading the documentation online as HTML or downloading as PDF for viewing on your computer etc, you can also download it in formats (Mobi and ePub) designed for eReaders such as the Kindle and iPad (the latter obviously isn’t “just” an eReader). For information on format support, there’s a handy table on Wikipedia.

It looks like the availability of mobi/epub files isn’t universal. For example, the OBI 11g documentation appears still to be HTML & ZIP only. And whilst the Database documentation doesn’t link to the mobi files on the contents page, only each document’s TOC, the Essbase documentation does: