From Database Changes to Real-Time Actions at Scale: Oracle GoldenGate and Java

DB changes can be streamed in real time via Oracle GoldenGate. Java applications can then consume them to validate/act on/route events without polling the DB.

Goal

Listen to real-time transactions in the DB, act on all operations within transactions, and maintain transaction boundaries with near real-time SLAs.

Component Details

1. Source DB 

This is the source DB from which the Java service listens for transaction changes in real time. It can be any DB supported by the GoldenGate service (e.g., MySql/ Oracle).

2. OCI Source GoldenGate Service Deployment

• Input: DB Redo logs
• Output: Trail file

The GoldenGate service has two primary components: Extract and Replicat. Extract reads changes from the source DB in the form of redo and archive logs. It commits each transaction's DML operations until either COMMIT or ROLLBACK is identified. If it encounters a COMMIT, it stores the transaction in TRAIL files. If ROLLBACK is encountered, it discards those changes.

The output of this deployment is a TRAIL file with an ordered list of all transactions that occurred in the source DB.

3. OCI Target GoldenGate Big Data Deployment

• Input: Trail files generated by traditional GG deployment
• Output: Ordered stream of operations within a transaction

This is another deployment type that can be used for transaction management. We can use the DATA STREAMS feature instead, but this uses the AsyncAPI publish/subscribe interface, whereas big data deployment is a managed integration runtime for CDC delivery to Kafka.

4. Streaming Service

• Input: Trail files generated by traditional GG deployment
• Output: Ordered stream of operations within a transaction

Ideally, Kafka keeps up with the scale of GoldenGate and downstream scalability. Kafka provides an option to add partitions while the partitions are receiving data. This feature is particularly useful for handling traffic bursts or batch SQL jobs on the source DB.

5. Java Service Kubernetes Deployment

• Input – Kafka messages, in this case individual operations
• Output – Application logic

A Java application instance that can scale horizontally depending on the Kafka partition backlog.

Use Case

1. Java service is interested in listening to the entire transaction in the order that it is happening for a predefined set of tables.
2. It does not want to listen to all the source table changes
3. It also does not want all columns of the changed row. It is interested in listening to only specific set of columns from given list of tables.

Component Setup

The detailed setup guide here can be used as general guidance, but I will focus on the specific changes needed to achieve the use case above. 

1. Source DB

Here we want to listen to redo logs from the source DB. To enable supplemental logging on the source DB, use the command below. 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

In our use case, we also want to make sure we always get only the columns from the table that the Java service is interested in, and not all the columns. To enable table-specific supplemental logs, use the command below.

ALTER TABLE HR.EMPLOYEE ADD SUPPLEMENTAL LOG GROUP emp_group_loc (EMPLOYEE_ID, EMP_NAME, EMP_DEPARTMENT_ID);

2. OCI Source GG Deployment

In this step, OCI source GG deployment is configured to listen to redo logs of the source DB. As per our use case the important step is to configure only required tables in Extract's parameter file. The list of tables basically makes sure transactions happening on only those tables will be identified by GG and will be collected in trail files. You can use this article to set up Extract. 

Example of parameter file for extract:

table HR.EMPLOYEE;

Along with extract, a distribution path needs to be created within the same source deployment. This distribution path makes sure the target big data deployment can access the trail file records easily. The details to create the distribution path can be found in TASK 3 of the article. 

3. OCI Target GoldenGate Big Data Deployment

In this deployment, a process called 'replicat' is created; replicat reads transaction data from the distribution path created in step 2 above, then publishes those transactions. Replicat works using two configuration files.

Parameter File

Similar to Extract, Replicat also has a parameter file. This file contains mapping information on how operations from the source DB map to the target schema and table. Since we are not replicating transactions in the DB, a JSON with the target table mapped in the parameter file gets written to kafka topic instead. 

Example for a complete setup guide: use this guide.

MAP HR.EMPLOYEE, TARGET *.*;

Properties File

Three important parameters that decide how the source transactions get written to the target are 

1. TopicMappingTemplate – Tells replicat about the target, where source transactions will be written,
2. opMode – How the source transaction will be written. Complete transaction or one operation at a time.
3. keyMappingTemplate – This parameter is the hashing key used by the replicat Kafka producer.

For all the parameters that are available in the properties file, please refer to section "11.2.10.1.3.2.2 Kafka Handler Configuration" in this guide.

4. Streaming Service and Java Consumer Service

Since GG hashes based on transaction ID, it is guaranteed that all operations in a transaction will always be in the same partition. The Java service needs a consumer group used by all pods. At bootstrap, all pods register their client ID with the Kafka broker and reserve a partition. This mechanism makes sure a single pod always listens to one partition until a rebalance happens due to a pod going down or other reasons. 

This setup ensures the Kafka consumer in the Java service can poll and collect transactions while maintaining operation boundaries and sequence for downstream application logic within the same service. Separating polling logic from processing logic in the Java service helps avoid various timeout scenarios. We can dive deep into this mechanism in a follow-up article.

Summary

With this setup, we can guarantee that transactions at the source DB are accessed by a Java service in order and can run within a single transaction at a time. This workflow is useful for industries that need an eventing mechanism, such as fraud detection and financial applications, telecommunications and real-time billing,  Retail and real-time inventory management, and AI and semantic search pipelines.