gg-custom-formatters

Overview

This repository contains custom formatters for Oracle GoldenGate that are inspired by the Debezium transaction metadata format. These formatters are designed to provide a structured representation of transactions, including commit sequence numbers (CSN), transaction IDs (XID), timestamps, and event counts.

Transaction Event Format

The formatter produces JSON output with the following structure:

{
  "csn": "1234551111",
  "xid": "5.6.641",
  "tx_ts": 1486500577691,
  "event_count": 3,
  "data_collections": [
    {
      "data_collection": "ORDERS",
      "event_count": 1
    },
    {
      "data_collection": "ORDER_DETAILS",
      "event_count": 1
    },
    {
      "data_collection": "ORDER_LINE_ITEMS",
      "event_count": 1
    }
  ]
}

Configuration

1. Kafka Handler Integration

To use the custom formatter with the GoldenGate Kafka handler, see the following property:

gg.handler.kafkahandler.format=

2. Custom Library Directory

Create a directory for custom libraries and copy the required artifacts:

/opt/oracle/ggbd/ggjava/resources/customlib

Update the classpath in your configuration as follows:

gg.classpath=gg.classpath=dirprm/:/var/lib/kafka/libs/*:/opt/oracle/ggmabd/home/opt/DependencyDownloader/dependencies/kafka_3.3.2/*:/opt/oracle/ggbd/ggjava/resources/customlib/*

3. Replicat Settings

Below is an example of a replicat parameter files (replicat.prm):

REPLICAT rep3
TARGETDB LIBFILE libggjava.so SET property=/opt/oracle/ggbd/dirprm/rep3.props
REPORTCOUNT EVERY 15 SECONDS, RATE
GROUPTRANSOPS 1000
--MAXTRANSOPS 1000
IGNOREDELETES
MAP ORCLPDB.APPUSER.ORDERS, TARGET APPUSER.ORDERS;
MAP ORCLPDB.APPUSER.ORDER_DETAILS, TARGET APPUSER.ORDER_DETAILS;
MAP ORCLPDB.APPUSER.ORDER_LINE_ITEMS, TARGET APPUSER.ORDER_LINE_ITEMS;

Example Kafka handler properties ('.props''):

gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.kafkaProducerConfigFile=kp.properties
#The following resolves the topic name using the short table name
gg.handler.kafkahandler.topicMappingTemplate=dev.transaction_metadata_json
gg.handler.kafkahandler.mode=tx
gg.handler.kafkahandler.transactionsEnabled=false
gg.handler.kafkahandler.format=com.technext.goldengate.dasource.formatter.TransactionBoundaryEventJsonFormatter
#Sample gg.classpath for Apache Kafka
gg.classpath=dirprm/:/var/lib/kafka/libs/*:/opt/oracle/ggmabd/home/opt/DependencyDownloader/dependencies/kafka_3.3.2/*:/opt/oracle/ggbd/ggjava/resources/customlib/*
#Sample gg.classpath for HDP
javawriter.stats.full=TRUE
javawriter.stats.display=TRUE

###KAFKA Properties file ###
gg.log=log4j
gg.log.level=debug
gg.report.time=30sec
jvm.bootoptions=-Xms4g -Xmx6g

Testing

Example 1

The following PL/SQL block was executed to generate transaction event:

SET TIMING ON;
BEGIN
	FOR I IN  1..50
	LOOP
		UPDATE ORDERS SET ORDER_STATUS='CONFIRMED' WHERE ORDER_ID=1;
		UPDATE ORDER_DETAILS SET DELIVERY_STATUS='IN_TRANSIT' WHERE ORDER_ID=1;
		UPDATE ORDER_LINE_ITEMS SET ITEM_QTY=100 WHERE ORDER_ID=1 AND LINE_ITEM_ID=1;
	END LOOP;
END;
/
COMMIT;

Sample output:

{
  "xid": "1342848513.10.11.105432",
  "csn": "334506722",
  "tx_ts": "2025-07-07 22:34:16.000000",
  "event_count": 150,
  "data_collections": [
    {
      "data_collection": "ORDERS",
      "event_count": 50
    },
    {
      "data_collection": "ORDER_DETAILS",
      "event_count": 50
    },
    {
      "data_collection": "ORDER_LINE_ITEMS",
      "event_count": 50
    }
  ]
}

Example 2

SET TIMING ON;
BEGIN
	FOR I IN  1..50
	LOOP
		UPDATE ORDERS SET ORDER_STATUS='CONFIRMED' WHERE ORDER_ID=1;
		UPDATE ORDER_DETAILS SET DELIVERY_STATUS='IN_TRANSIT' WHERE ORDER_ID=1;
	END LOOP;
	UPDATE ORDER_LINE_ITEMS SET ITEM_QTY=200 WHERE ORDER_ID=1 AND LINE_ITEM_ID=1;
END;
/
COMMIT;

Sample output:

{
  "xid": "1342848513.10.11.105432",
  "csn": "334506722",
  "tx_ts": "2025-07-07 22:34:16.000000",
  "event_count": 101,
  "data_collections": [
    {
      "data_collection": "ORDERS",
      "event_count": 50
    },
    {
      "data_collection": "ORDER_DETAILS",
      "event_count": 50
    },
    {
      "data_collection": "ORDER_LINE_ITEMS",
      "event_count": 1
    }
  ]
}

Example 3

Pressure test with 150,000 updates to the same transaction

SET TIMING ON;
BEGIN
    FOR I IN  1..50000
        LOOP
            UPDATE ORDERS SET ORDER_STATUS='CONFIRMED' WHERE ORDER_ID=1;
            UPDATE ORDER_DETAILS SET DELIVERY_STATUS='IN_TRANSIT' WHERE ORDER_ID=1;
            UPDATE ORDER_LINE_ITEMS SET ITEM_QTY=100 WHERE ORDER_ID=1 AND LINE_ITEM_ID=1;
        END LOOP;
END;
/
COMMIT;

Sample output:

{
  "xid": "1342848513.10.11.105432",
  "csn": "334506722",
  "tx_ts": "2025-07-07 22:34:16.000000",
  "event_count": 150000,
  "data_collections": [
    {
      "data_collection": "ORDERS",
      "event_count": 50000
    },
    {
      "data_collection": "ORDER_DETAILS",
      "event_count": 50000
    },
    {
      "data_collection": "ORDER_LINE_ITEMS",
      "event_count": 50000
    }
  ]
}

Note: Ensure memory settings are sufficient for the JVM to handle large transactions. Example JVM options:

jvm.bootoptions=-Xms4g -Xmx6g
gg.log.level=debug
gg.report.time=30sec

Library location

Libraries required for building the project were retrieved manually from Oracle GoldenGate installation. The libraries are located in the following directory:

/opt/oracle/ggbd/ggjava/resources/lib

Known Issues

• NullPointerException in metaDataChangedEvent

ERROR 2025-07-07 22:08:48.000826 [main] - An exception occurred calling metaDataChanged in the formatter.                                                                                                         
java.lang.NullPointerException: null                                                                     
        at oracle.goldengate.format.json.JsonFormatter.metaDataChanged(JsonFormatter.java:550) ~[ggformatters-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.handler.NgFormattedOutputHandler.metaDataChanged(NgFormattedOutputHandler.java:156) [ggaddons-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.handler.kafka.KafkaHandler.metaDataChanged(KafkaHandler.java:358) [ggkafka-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.DsEventManager$6.send(DsEventManager.java:529) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.DsEventManager.distributeEvent(DsEventManager.java:120) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.DsEventManager.fireMetaDataChanged(DsEventManager.java:535) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.AbstractDataSource.fireMetaDataChanged(AbstractDataSource.java:550) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.UserExitDataSource.newTableMetaData(UserExitDataSource.java:2278) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]
        at oracle.goldengate.datasource.UserExitDataSource.newTableMetaData(UserExitDataSource.java:2118) [ggdbutil-21.4.0.0.0.002.jar:21.4.0.0.0.002]

Reference:

• Debezium Transaction Metadata