Oracle GoldenGate (OGG) supports DDL capture and apply for Oracle and MySQL databases only, and only with homogeneous databases. Because of this, maintenance tasks for other databases require a lot of coordination and frequently caused extended outages for the database and/or GoldenGate. In this article we shall discuss one mechanism for applying DDL operations using the Oracle GoldenGate option EVENTACTIONS, in an Oracle Database to PostgreSQL (PG) Database replication stream.

All testing performed for this paper used the Oracle GoldenGate 23ai release; however, the concepts presented should function on any Oracle GoldenGate version and target database that supports Coordinated Apply and EVENTACTIONS. While this paper is specific for Oracle GoldenGate for PostgreSQL as the target database, the concepts presented could be modified to provide this functionality for additional platforms.

Disclaimer

This document was written using Oracle GoldenGate release 23ai. The same functionality exists in prior releases (Oracle GoldenGate 19c and 21c); however, we did not test against any earlier release. It is intended solely to help you assess the business benefits of integrating the features discussed into your Oracle GoldenGate installations and to plan your I.T. projects.

This document in any form, software or printed matter, contains information that is the exclusive property of Oracle. Your access to and use of this material is subject to the terms and conditions of your Oracle software license and service agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced or distributed to anyone without prior written consent of Oracle. This document is not part of your license agreement, nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

This document is for informational purposes only and is intended solely to assist you in planning for the implementation and upgrade of the product features described. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.

Prerequisites

Prerequisite actions include:

  1. Installing the Oracle GoldenGate for Oracle Database and Oracle GoldenGate for PostgreSQL Database in a hub architecture configuration.
  2. Create source and target Oracle GoldenGate Microservices deployments.
  3. Create an Oracle GoldenGate event table in the source and target database. 
    • For this article, we shall be using very simple tables consisting of three columns.
      • Oracle Database: create table oggddlevt (ddlevtid numeric(38) not null, ddlevtts date default sysdate not null, ddlfileloc varchar2(200) not null, primary key (ddlevtid) );
      • Postgres Database: create table public.oggddlevt ( ddlevtid numeric(38) not null, ddlevtts date not null, ddlfileloc varchar(200) not null, primary key (ddlevtid) );
    • Metadata details:
      • ddlevtid – A unique identifier for each row/ddl event. For this article, we’re using numeric values consisting of month, day, year, hours, and minutes. Example: 042220251218 = 12:18PM 22 April 2025
      • ddlevtts – The date the event record was added to the table.
      • ddlfileloc – The fully qualified disk location containing Postgres DDL statements on the target Oracle GoldenGate Hub server.
  4. On the target Oracle GoldenGate Hub:
    • Install the psql command line tool, which is part of the PostgreSQL Database client package.
      • Redhat/Oracle Linx: sudo yum install postgres -y
      • Debian/Ubuntu: sudo apt install postgressql-client -y
    • Create a directory for housing files containing DDL commands. For this paper, we created a subdirectory in our Oracle GoldenGate Microservices deployment home location: /u01/OGG/OGG23ai/OGGPG/Deployments/OGG4PG/ddlevt.
      • In this directory, create a file named “.pgpass”. 
      • The .pgpass file in PostgreSQL securely stores database connection information, including host, port, database name, username, and password. This allows you to avoid repeatedly entering passwords when connecting to a PostgreSQL database, which is especially useful for automation scripts. 
      • The file format is hostname:port:database:username:password. Passwords containing colons must be escaped with a backslash.

Oracle GoldenGate Configuration

Oracle Integrated Extract

Integrated Extract is configured using the wildcard table specification. Schema level supplemental logging has been enabled for Oracle Database capture, so we do not need to make any changes to the Oracle GoldenGate for Oracle Database configuration.

EXTRACT earth
USERIDALIAS dc2ggroot DOMAIN OracleGoldenGate
EXTTRAIL en
— Report operation capture rates every 60 minutes
— RATE provides operations per second
REPORTCOUNT EVERY 60 MINUTES, RATE
TABLE PDBNORTH.TPC.*;

PostgreSQL Coordinated Apply

REPLICAT rtpc 
USERIDALIAS pg_tpc_tgt DOMAIN OracleGoldenGate
—
— Report operation capture rates every 60 minutes.
— RATE provides operations per second.
REPORTCOUNT EVERY 60 MINUTES, RATE 
—
— Force Replicat to maintain a dedicated coordination thread to apply barrier transactions.
— The thread ID of this thread is always 0. 
USEDEDICATEDCOORDINATIONTHREAD 
—
— Ignore source delete and update operations for all tables below this setting. 
IGNOREDELETES 
IGNOREUPDATES 
—
— If an insert operation is processed for the oggddlevt table, execute the event action
— with the following options: 
—   1. Start a linux shell. 
—   2. Set the PGPASSFILE environment variable for the location of the .pgpass files. 
—   3. Set the LD_LIBRARY_PATH environment variable to the
—      location of the PostgreSQL-client libraries. This prevents issues caused by
—      the OGG provided libraries when sql is executed. (Message: “libpq.so.5:
—      no version information available (required by psql)” is written to output
—      file.) 
—   4. Execute psql with the following options: 
—       -h [dns name | ipaddress]
—          (The DNS name or ipddress of the PostgreSQL Database server.)
—       -p [number]  
—          (The listener port of the target PostgreSQL database.)
—       -U [user]
—          (The database user for executing the DDL commands.)
—       -d [database] 
—          (The database to which this Replicat connects.) 
—       -a 
—          (Print all input lines to standard output as they are read.)
—       -o 
—          (Put all query output into the specified file. The file name used is                                       
—           specified by the incoming column DDLFILELOC and appended with
—           “.log”.)
—       -w 
—          (Do not prompt for a database connection password. Use the .pgpass
—           file.)
—       -f 
—          (Execute SQL commands stored in the specified file. The file name used
—           is specified by the incoming column DDLFILELOC.)
—       &>> $DDLFILE.log 
—           (Append all stdin, stdout, and stderr output to the specified file. The file 
—            name used is specified by the incoming column DDLFILELOC and
—            appended with “.log”.)
—       VAR $DDLFILE = DDLFILELOC 
—           (Sets the variable $DDLFILE to the contents of the incoming column
—           DDLFILELOC.) 
—       IGNORE RECORD 
—           (Do not apply the record to the target oggddlevt table.)
—       LOG INFO 
—           (Write an informational record to the Replicat report file when the event 
—            ction is executed.)
—
— For the source to target MAP:
—       THREAD (1) 
—           (Specifies the Coordinated Apply thread to be used for this table’s apply
—            operations.)
—       COORDINATED 
—           (Transactions made on objects in the same MAP statement are processed as 
—            barrier transactions. Causes the threads across all MAP statements to synchronize
—            to the same trail location. The synchronized position is the beginning of the 
—            transaction that contains a record that satisfies a MAP that contains the 
—            COORDINATED keyword. The transaction is then applied atomically by a single 
—            thread, which is either the thread with the lowest thread ID among the currently
—            running threads or a dedicated thread with the ID of 0 if
—            USEDEDICATEDCOORDINATIONTHREAD is specified in the parameter file.)
—
MAP PDBNORTH.TPC.OGGDDLEVT, TARGET “public”.”oggddlevt”, 
EVENTACTIONS (shell (‘export PGPASSFILE=”/u01/OGG/OGG23ai/OGGPG/Deployments/OGG4PG/ddlevt/.pgpass” && export LD_LIBRARY_PATH=”/usr/lib64″ && dc2.postgres.example.com -p 5432 -U tpc -d tpc -a -o $DDLFILE.log -w -f $DDLFILE &>> $DDLFILE.log’, VAR $DDLFILE = DDLFILELOC), IGNORE RECORD, LOG INFO), THREAD (1), COORDINATED; 
—
— Apply delete and update operations for all tables below this setting. 
GETDELETES 
GETUPDATES 
—
— Use thread #2 to apply operations for the orders table. 
MAP PDBNORTH.TPC.ORDERS, TARGET “public”.”orders”, THREAD (2); 
—
— Use thread #1 to apply operations for all other tables not previously listed. 
MAP PDBNORTH.TPC.*, TARGET “public”.*, THREAD (1);

Coordinated Apply settings are fully explained by the comments in Table 2 (Note: Oracle GoldenGate parameter file comments are denoted by two dashes). However, it is important to note:

  1. All explicitly mapped tables must come before the wildcard designation in the Replicat parameter file.
  2. It is a good practice to have the map statement for the event table listed before any other map statements.

Functional Demonstration

To demonstrate functionality, we need to prepare our maintenance scripts. The Oracle Database script we’ll use is:

insert into oggddlevt values (042320250940,sysdate,’/u01/OGG/OGG23ai/OGGPG/Deployments/OGG4PG/ddlevt/ddltest1′);
—
create table ddltest1 (
cola number(10) not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);
—
insert into ddltest1 values (1,’Row 1 insert’, current_timestamp);
insert into ddltest1 values (2,’Row 2 insert’, current_timestamp);
insert into ddltest1 values (3,’Row 3 insert’, current_timestamp);
—
create table ddltest2 (
cola number(10) not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);
—
insert into ddltest2 values (1,’Row 1 insert’, current_timestamp);
insert into ddltest2 values (2,’Row 2 insert’, current_timestamp);
insert into ddltest2 values (3,’Row 3 insert’, current_timestamp);
commit;

The first line of the script is the insert into our oggddlevt table; which specifies the filename and location of the PostgreSQL Database script to execute. The remaining lines are Oracle Database DDL and DML statements.

Notice that we did not commit the data until the end of the script. When creating your script, the commit of the oggddlevt table insert must occur after all DDL statements.

On our Oracle GoldenGate for PostgreSQL Hub server, create DDL script file. The PostgreSQL Database script we’ll use is:

 

create table public.ddltest1 (
cola bigint not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);
—
create table public.ddltest2 (
cola bigint not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);

 

Data is being generated in our source application and is being replicated. We can check this by reviewing the Coordinated Apply statistics from the Oracle GoldenGate Microservices WebUI.

Coordinated Apply stats before script execution
Image 1. Coordinated Apply statistics before script execution

Execute the Oracle Database script. Refresh the Coordinated Apply statistics and we see the two new tables displayed.

Coordinated Apply stats after script execution
Image 2. Coordinated Apply statistics after script execution

We can also see the two tables in the PostgreSQL Database.

PostgreSQL tables after script execution
Image 3. PostgreSQL tables after script execution

Scrolling down in the Coordinated Apply statistics screen, we can see that the event action operation was execution was executed as a barrier transaction.

Coordinated Apply barrier transaction statisctics
Image 4. Coordinated Apply barrier transaction statisctics

Checking the logfile contents, we can see that the pgsql execution completed without error.

Logfile contents
Image 5. Logfile contents

Summary

In this article we presented one method of applying Oracle Database DDL to PostgreSQL databases using an Oracle GoldenGate event actions table and the EVENTACTIONS option to the MAP parameter. To learn more about Oracle GoldenGate functionality, you may refer to: