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 cause extended outages for the database and/or GoldenGate.

In a prior article (Oracle GoldenGate: Oracle to PostgreSQL DDL), we demonstrated one method of applying PostgreSQL DDL that originated from an Oracle source database using the Oracle GoldenGate option EVENTACTIONS. In this article we shall discuss an alternative mechanism for applying DDL operations using the Oracle GoldenGate option SQLEXEC, for real-time or stage and merge apply, 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 SQLEXEC. 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. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. 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.

Prerequisites

Prerequisite actions include:

1. Installing the Oracle GoldenGate for Oracle Database and Oracle GoldenGate for PostgreSQL Database in a hub architecture configuration.
   • See the Oracle GoldenGate documentation for details regarding software installation
2. Create source and target Oracle GoldenGate Microservices deployments.
   • See the Oracle GoldenGate documentation for details regarding deployment creation
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 four columns.
     • Oracle Database: create table oggddlevt (ddlevtid numeric(38) not null, ddlevtitm integer not null, ddlevttype varchar2(25) not null, ddlevtstmt varchar2(4000) not null, primary key (ddlevtid, ddlevtitm));
     • PostgreSQL Database: create table public.oggddlevt (ddlevtid numeric(38) not null, ddlevtitm integer not null, ddlevttype varchar(25) not null, ddlevtstmt varchar(4000) not null, primary key (ddlevtid, ddlevtitm));
   • 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: 051220251218 = 12:18PM 12 May 2025.
     • ddlevtitm – An integer denoting a sequence number of the event item; i.e., 1, 2, 3, 4, etc.
     • ddlevttype– The type of DDL statement to be executed; create, alter, drop.
     • ddlevtstmt – The DML statement to execute.

PostgreSQL Procedure

Below is the PostgreSQL Procedure we’ll be using to execute DDL in the target database.

create or replace procedure public.do_ddl(
   p_ddl_type      IN varchar,
   p_ddl_statement IN varchar,
   p_ogg_op        IN varchar
)
language plpgsql
as $$
BEGIN
   IF (p_ogg_op NOT ILIKE 'DELETE') AND ((p_ddl_type ILIKE 'create') OR
       (p_ddl_type ILIKE 'alter') OR (p_ddl_type ILIKE 'drop')) THEN
         EXECUTE p_ddl_statement;
   END IF;
END;
$$;

This database procedure checks the operation type of the source record contained in the p_ogg_op variable. If it is a DELETE, the operation is ignored by the procedure (but applied to the oggddlevt table). If the operation is an INSERT or UPDATE, then the value of p_ddl_type is validated to be one of create, alter, or drop. If this returns true, the SQL DML statement in the p_ddl_statement variable is executed.

Important: Be sure to grant execute privileges on the procedure to the Oracle GoldenGate PostgreSQL Database user.

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. 

PostgreSQL Replicat

As SQLEXEC is a barrier operation, we can use either Coordinated Apply or Parallel Replicat as our data apply mechanism.

Coordinated Apply

Coordinated Apply parameters we’ll be using for our demonstration:

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

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

Parallel Replicat

Here’s the Parallel Replicat parameters we’ll be using:

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

• All explicitly mapped tables must come before the wildcard designation in the Replicat parameter file.
• 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, first we need to create our Oracle Database maintenance script. To keep things simple, we’ll execute these commands:

Coordinated Apply

Coordinate Apply statistics verifies that we have application data being replicated from our Oracle source.

When the event table operations are applied, we see a Barrier Transaction recorded in the Coordinated Apply statistics.

The Coordinated Apply table statics are updated to show our newly added table, and the source operations replicated.

Getting a list of tables in our PostgreSQL database, we see the new table, public.ddltest1 is owned by the tpc database user.

To test dropping a table via the database procedure, we insert a row into the Oracle oggddlevt table:

insert into oggddlevt values (202505151323,1,'drop', 'drop table public.ddltest1');

When the Coordinated Apply processes the source record, its statistics will record a barrier transaction for the SQLEXEC procedure call.

Checking our PostgreSQL Database, we see the ddltest1 table has been removed.

Parallel Replicat

SQLEXEC operations are a barrier transaction for Parallel Replicat. Barrier transactions must be applied serially; using this we can leverage the concurrent apply mechanism’s high-performance features to also replicate our DDL.

In our demo system, Parallel Replicat stats shows there is activity in the replication stream.

We execute our script in the Oracle Database source. The Parallel Replicat statistics shows serialization occurs for a single operation, which is a SQLEXEC.

The statistics also show the new table, ddltest1, and the number of operations applied at the time of refresh.

Stage and Merge

In the previous section we demonstrated an option for replicating DDL in real-time. With a modification of the PostgreSQL Procedure, we can instead stage DDL in preparation for later execution.

PostgreSQL Procedure

Below is our PostgreSQL Procedure to perform stage and merge of DDL replicated from the source Oracle Database. Be sure to grant execute privileges on the procedure to the Oracle GoldenGate PostgreSQL Database user.

create or replace procedure public.do_ddl2(
   p_ddl_evtid     IN numeric,
   p_ddl_evtitm    IN integer,
   p_ddl_type      IN varchar,
   p_ogg_op        IN varchar
)
language plpgsql
as $$
DECLARE
   ddlop   varchar;
BEGIN
   IF (p_ogg_op NOT ILIKE 'DELETE') AND (p_ddl_type ILIKE 'merge') THEN
      SELECT ddlevtstmt INTO ddlop FROM oggddlevt 
         WHERE ddlevtid = p_ddl_evtid AND ddlevtitm = p_ddl_evtitm;
      EXECUTE ddlop;
   END IF;
END;
$$;

This Procedure accepts the values of ddlevtid, ddlevtitm, and ddltype replicated from the Oracle oggddlevt table, and the database operation type supplied by the Replicat.

If the database operation is an INSERT or UPDATE, the value of p_ddl_type is checked. If p_ddl_type is merge, then a select is executed against the oggddlevt table and all records matching the values p_ddl_evtid and p_ddl_evtitm are returned and then executed.

If the database operation is DELETE, the operation is passed to the database and the record is deleted from the oggddlevt table.

Parallel Replicat

Here are the Parallel Replicat parameters we’ll use for executing the PostgreSQL Procedure:

REPLICAT PRDDL
USERIDALIAS pg_tpc_tgt DOMAIN OracleGoldenGate

-- Report operation capture rates every 60 minutes. 
-- RATE provides operations per second  
REPORTCOUNT EVERY 60 MINUTES, RATE  

--  Use the SQLEXEC option to call the database Procedure, providing:
--     the DDL event id,
--     the DDL event item number,
--     the DDL event type (merge) and,
--     the operation type.
-- Abend if an error is returned from the Procedure.
MAP PDBNORTH.TPC.OGGDDLEVT, TARGET "public"."oggddlevt",  
SQLEXEC (ID do_ddl2, QUERY 'call public.do_ddl2(?,?,?,?)', PARAMS (p1 = ddlevtid, p2 = ddlevtitm, p3 = ddlevttype, p4 = @GETENV ('GGHEADER', 'OPTYPE')), ERROR FATAL);

-- Apply operations for all other tables not previously listed.  
MAP PDBNORTH.TPC.*, TARGET "public".*;

Functional Demonstration

Below is the data we’ll insert into our Oracle oggddlevt table to stage the creation of the PostgreSQL tables.

insert into oggddlevt values (202505191001,1,'stage', 'create table public.ddltest1 (cola bigint not null, colb varchar(25), colc timestamp(0), primary key (cola))');
insert into oggddlevt values (202505191001,2,'stage', 'create table public.ddltest2 (cola bigint not null, colb varchar(25), colc timestamp(0), primary key (cola))');
insert into oggddlevt values (202505191001,3,'stage', 'create table public.ddltest3 (cola bigint not null, colb varchar(25), colc timestamp(0), primary key (cola))');

insert into oggddlevt values (202505191001,4,'stage', 'alter table public.ddltest1 owner to tpc');
insert into oggddlevt values (202505191001,5,'stage', 'alter table public.ddltest2 owner to tpc');
insert into oggddlevt values (202505191001,6,'stage', 'alter table public.ddltest3 owner to tpc');

commit;

The records are replicated to the PostgreSQL target, as shown in the Parallel Replicat statistics.

When we’re ready to create the tables in the PostgreSQL database, we execute an Oracle Database maintenance script that updates the value of ddlevttype from “stage” to “merge”:

-- Update the ddlevttype to merge. This will create the target tables.
-- This update must be executed first in the script.
update oggddlevt set ddlevttype = 'merge' where ddlevtid = 202505191001;
commit;

-- Create the Oracle tables
create table ddltest1 (
cola number(10) not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);
create table ddltest2 (
cola number(10) not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);
create table ddltest3 (
cola number(10) not null,
colb varchar(25),
colc timestamp(0),
primary key (cola)
);

-- Execute DML on the new tables.
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);
insert into ddltest1 values (4,'Row 4 insert', current_timestamp);
insert into ddltest1 values (5,'Row 5 insert', current_timestamp);
insert into ddltest1 values (6,'Row 6 insert', current_timestamp);
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);
insert into ddltest2 values (4,'Row 4 insert', current_timestamp);
insert into ddltest2 values (5,'Row 5 insert', current_timestamp);
insert into ddltest2 values (6,'Row 6 insert', current_timestamp);
insert into ddltest3 values (1,'Row 1 insert', current_timestamp);
insert into ddltest3 values (2,'Row 2 insert', current_timestamp);
insert into ddltest3 values (3,'Row 3 insert', current_timestamp);
insert into ddltest3 values (4,'Row 4 insert', current_timestamp);
insert into ddltest3 values (5,'Row 5 insert', current_timestamp);
insert into ddltest3 values (6,'Row 6 insert', current_timestamp);
commit;

update ddltest1 set colb = 'Row3 update', colc = current_timestamp where cola = 3;
update ddltest2 set colb = 'Row3 update', colc = current_timestamp where cola = 3;
update ddltest3 set colb = 'Row3 update', colc = current_timestamp where cola = 3;
commit;

The Parallel Replicat statistics shows execution of the PostgreSQL Procedure via SQLEXEC as serialized transactions.

Statistics also shows DML applied to our new tables.

Summary

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

• The Oracle Integration Services GoldenGate web site
  • https://www.oracle.com/integration/goldengate/
• The Oracle Data Integration blog site.
  • https://blogs.oracle.com/dataintegration/
• The OracleGoldenGate YouTube channel.
  • https://www.youtube.com/c/oraclegoldengate
• Oracle GoldenGate A-Team articles.
  • https://blogs.oracle.com/authors/loren-penton
  • https://blogs.oracle.com/authors/sourav-blog
  • https://blogs.oracle.com/authors/michael-papio
  • https://blogs.oracle.com/authors/somu-rajarathinam
  • https://blogs.oracle.com/authors/sunil-vernekar14