Edward Whalen

01/03/2019

DDL replication in GoldenGate is much easier in Oracle database version 12 and GoldenGate version 12.3 because of the native support for it.  In the past we used to have to setup DDL replication in GoldenGate within the database by running several SQL scripts.  Now it is all native.  DDL replication in GoldenGate is setup using the DDL parameter.  Optionally DDLOPTIONS can be set as well.

The DDL statement allows you to setup DDL replication in GoldenGate on all DDL statements or to filter which DDL statements that you want to replicate.  This allows for very specific DDL replication.

For mapped statements allow

create AND table
OR
create AND index
OR
alter AND table
OR
alter AND index

The eventual syntax that worked is this:

DDL INCLUDE MAPPED OPTYPE create OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE create OBJTYPE ‘index’ &
INCLUDE MAPPED OPTYPE alter OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE alter OBJTYPE ‘index’ &
INCLUDE MAPPED OPTYPE drop OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE drop OBJTYPE ‘index’

This took a few tries and I verified that a drop table does not replicate.  Only that statements that I want to allow will replicate.

When configuring DDL replication in GoldenGate, I would also recommend excluding the drop partition statement.  This can be very dangerous when using interval partitioning because the partition name SYS_Pnnnnn would most likely not line up on the source and target system, thus causing the wrong partition to be dropped.  Adding the following could help this problem:

EXCLUDE MAPPED OPTYPE drop OBJTYPE ‘partition’

This would give us the following DDL statement:

DDL INCLUDE MAPPED OPTYPE create OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE create OBJTYPE ‘index’ &
INCLUDE MAPPED OPTYPE alter OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE alter OBJTYPE ‘index’ &
INCLUDE MAPPED OPTYPE drop OBJTYPE ‘table’ &
INCLUDE MAPPED OPTYPE drop OBJTYPE ‘index’ &

EXCLUDE MAPPED OPTYPE drop OBJTYPE ‘partition’