Ideas for Event Sourcing in Oracle

Log Miner or are there other alternatives?

Franck Pachot
Jul 15 · 9 min read

With microservices, the architects want to dismantle the monolithic database and replicate data rather than share it. Then raises the need to audit the changes where the modifications are done (like the C in CQRS). The Oracle database already does that for recovery purpose, building the redo records before modifying the data blocks, but that’s a physical change vector. We need something logical with more possibilities to filter and transform. There are multiple possible methods for this. But unfortunately, the ones that were built in the database are slowly removed since Oracle has acquired Golden GAte and sells it separately.

And deprecated became desupported, and even removed in further releases, like Continuous Mine in 19c — the final pathset of 12cR2:

ORA-44609: CONTINOUS_MINE is desupported for use with DBMS_LOGMNR.START_LOGMNR

The doc says that the replacement is Golden Gate but that’s another product to buy, very powerful but expensive (and there’s no Standard Edition).

Debezium DBZ-137

Debezium, an open source distributed platform for change data capture, is working on an Oracle Database connector. Many ideas are mentioned in https://issues.jboss.org/browse/DBZ-137 and in this post I give my thought about them.

Oracle XStreams

The perfect solution as it has minimal overhead on the source and is very efficient. But it requires Golden Gate licensing, and then is probably not the best solution for an Open Source product.

Oracle LogMiner

LogMiner is included in all Editions, reads the redo stream (archived and online redo logs) and extracts all information. When enabling Supplemental Logging, we have enough information to build the logical change information. Many solutions are already based on that. But I see two problems with it.

LogMiner Limits: Basically, LogMiner was not made for replication. The idea was more to give a troubleshooting tool to understand what happened on the data: what is generating too much redo? Who deleted some data? Which sessions were locking rows?… There are limits, like unsupported datatypes. And it is not designed to be efficient. But there’s also the possibility to mine on another system. However, I think that those limits can be acceptable for an Open Source solution on simple databases with low rate of changes.

LogMiner Future: What is more wondering is how Oracle removes the features that may give an alternative to Golden Gate. In 19c the CONTINUOUS_MINE was removed. This means that we need to constantly open and read the whole the redo logs. And do we know what Oracle will remove in future versions when they will see a robust Open Source product that competes with Golden Gate?

On the DBZ-137 there are some remarks about RAC which is more complex because there are many redo threads. I don’t think that RAC is in the scope for this. RAC is an expensive option that is required only on large databases with very high load. That fits more in the Golden Gate scope.

Note that we can parse the SQL_REDO and SQL_UNDO from V$LOGMINER_CONTENTS but there’s also the possibility to get them from dbms_logmnr.mine_value

Mining the binary log stream

There are some attempts to mine the binary redo logs. Some well known commercial products and some Open Source attempts. That’s very complex, but that’s also fun for an open source community. The redo log structure is proprietary but Oracle will not change it too often because all availability features (recovery, standby,…) are based on it. However, there may be a legal issue to open source this mining as it exposes the proprietary format of the redo. Reverse engineering is clearly forbidden by the Oracle license.

Continuous Query Notification

I studied the usage of dbms_change_notification as a CDC alternative: https://blog.dbi-services.com/event-sourcing-cqn-is-not-a-replacement-for-cdc. This feature is aimed at nearly static data, in order to invalidate and refresh a cache. It is not designed for a high change rate and is not efficient at all for this.

Client Result Cache

In the same idea as refreshing a cache from data that do not change often, one can think about querying with client result cache as it has a mechanism to invalidate the cache when a modification occurs. However, the granularity is bad here as any change on the table will invalidate all queries on it.

Materialized View Logs

All changes can be logged in materialized view logs. This feature is built for materialized views fast refresh which is a kind of replication. This has nothing to do with the redo log used by LogMiner. With materialized view logs, the changes are stored in a table and must be deleted when consumed. But this feature exists for a long time and is widely used. However, I would seriously question the architecture if there’s a general need for double writing, then reading it and deleting it, just to put the same data into another place.

Triggers

With triggers, we can log the changes as with materialized view logs. It gives more possibilities, like sending the change rather than storing it in the database (but then we have to manage the transaction visibility). An optimized example to store the audited change has been published by Connor McDonald:

But this is still a lot of overhead and need to to be adapted when columns are added or removed.

ORA_ROWSCN

When we enable row dependencies, the ORA_ROWSCN pseudo-column can help to filter the rows that may have been updated recently. However, there are two problems with this approach.

full read: if we want a near real-time replication, we will probably pool for changes frequently. ORA_ROWSCN would be nice if indexed, but that’s not the case. It just reads the information stored in the table block. That means that to find the changes done in the last 5 minutes we need to full scan the table and ORA_ROWSCN will then help to identify those rows that were changed. It is a transparent alternative to a “last update” column timestamp but does not help to access quickly to those rows.

commit time: there’s a general problem with anything that reads a “change” timestamp. Let’s say that I pool the changes every 5 minutes. I have a long transaction that updates a row at 12:39 and commits at 12:42. The pool that runs at 12:40, looking for changes since 12:35, does not see the change as it is not committed yet. The pool that runs at 12:45 can see it but not when it filters on the changes that occurred since the last run, which is 12:20. This means that each run must look on a larger window, including the longest transaction start. And then it must deal with duplicates as some of the changes have been captured by the previous run. This is a general problem when there’s no “commit SCN” available.

Userenv(‘commitscn’)

While talking about the visibility time (commit SCN) vs. the change there is an undocumented way to get it. insert or update with userenv(‘commitscn’) and this will magically get back to the table row at the end of the transaction to set the Commit SCN. It is not supported and anyway it can be invoked only once in a transaction and then cannot be added automatically in a trigger.

Oracle Flashback Query

If we don’t want to add additional auditing on DML, the redo log is not the only internal logging. Oracle also logs the undo information for consistent reads (MVCC) and this, without any additional overhead on the modification, can show all changes that occurred in a table. Basically, we can SELECT … FROM … VERSION BETWEEN SCN … AND … and all changes will be visible with the new and old values and additional information about the operation and the transaction.

However, this is not indexed. Like with ORA_ROWSCN we need to full scan the table and the consistent read will build the previous versions of the blocks, thanks to the undo.

Flashback Data Archive

Flashback Query can reconstruct the recent changes, limited by the undo retention, and by the last DDL that occurred. Flashback Data Archive (which was called Total Recall) can go further. This feature is available in Enterprise Edition without the need for an additional option. It can go beyond the undo retention and allows some DDL. But, again, it is not optimized to get all changes since a specific point in time. The idea is that when you know the rows you want to read, then it can get to the previous version.

Minimal trigger + flashback query

Some of those solutions can be combined. For example, a trigger can log only the ROWID of the changed rows and the replication process will get more information for these rows through flashback query. This lowers the overhead on the changes, while still avoiding a full scan for the replication. Or you may get those ROWID directly from custom-mining the redo logs, which is much simple than trying to get all information from it.

DDL triggers

SQL is agile and allows the structure to change. If adding a column breaks the whole replication, then we have a problem. All the solutions above need to handle those changes. The redo log contains the changes in the dictionary, but it can be complex to decode. All other solution must adapt to those changes and that means having a DDL trigger and handling the different kinds of changes.

Not easy…

The summary is that there are no easy solutions, and the easiest ones have been removed by Oracle to push the sales for Golden Gate. My first recommendation when someone wants to replicate to changes to query it from another place is: don’t do that. Relational databases are made to ingest new data and modifications, and be able to query for different purposes. We have views to show data in different formats. We have indexes to get fast access for different use cases. Oracle is not like many of its competitors. It has been optimized for mixed workloads from the first versions. You can query the same database where the changes occur because a SELECT does not lock anything. You have a resource manager to be sure that runaway queries cannot slow down the transactional activity. And the CPU usage for those queries, when correctly tuned, will rarely be higher than the replication activity you need to implement to stream the changes to another database.

Then, which technology should an event sourcing be built upon? LogMiner looks good for small databases with basic usage. And the project should adapt to the features that are removed by Oracle in the future.

Hybrid trigger / flashback query

When only a few tables are concerned, generating DML triggers is probably the simplest, especially if they log only the minimum, like the ROWID. The ROWID will be visible only when the transaction is committed. Then the replication process must use flashback query, reading only those blocks from the ROWID. The nice thing here is that flashback query shows when the change was visible (the commit time) rather than the change time, which makes it easier to filter out the changes already processed by the last run.

Here is the idea when a trigger has logged the ROWID changed into a DEMO_CDC table, and we query:

The execution plan for this shows optimal access with the ROWID:

Explain Plan
------------------------------------------------
PLAN_TABLE_OUTPUT
Plan hash value: 3039832324
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | SORT UNIQUE | |
| 3 | TABLE ACCESS FULL | DEMO_CDC |
| 4 | TABLE ACCESS BY USER ROWID| DEMO |
------------------------------------------------

The important thing here is that the query cost is proportional to the changes and not to the full size of the table. And the trigger overhead is limited to the ROWID only. There’s no need to store in a table the values that are stored already in the base table and the undo segments. If reading this is done frequently, there are good chances that all blocks involved (the ROWID list, the UNDO records and the table block) are still in the buffer cache.

This trigger+flashback approach is just an idea that I’ve never used. So feedbacks welcome on https://twitter.com/franckpachot

Franck Pachot

Written by

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade