Oracle GoldenGate tip: DDL INCLUDE/EXCLUDE and comments

Figured this out by trial and error, actually while I was compiling a convincing “you have a bug” log for Oracle Support prior to opening an SR.  🙂  The odds of hitting this are small, but it’s worth putting a post out there just in case.  Certainly drove us nuts for a while.

GoldenGate DDL include/exclude statements look something like this:

 DDL INCLUDE MAPPED &
 EXCLUDE ALL INSTR 'TRIGGER' &
 EXCLUDE ALL INSTR 'GRANT' &
 EXCLUDE ALL OPTYPE 'ALTER' &
 OBJTYPE 'SESSION'
;

My goal was to exclude all trigger operations.  I tried both OPTYPE/OBJTYPE (for ‘create’ and ‘trigger’) and INSTR in a one-table extract/pump/replicat setup, doing the DDL excludes in the replicat file.  It complete ignored whatever I did.  Finally, I removed a line leftover from testing, which was commented out as follows:

--DDL INCLUDE ALL &   <--- this line
 DDL INCLUDE MAPPED &
 EXCLUDE ALL INSTR 'TRIGGER' &
 EXCLUDE ALL INSTR 'GRANT' &
 EXCLUDE ALL OPTYPE 'ALTER' &
 OBJTYPE 'SESSION'
;

Well it turns out that in this particular formatting case, GoldenGate ignores your comment “–” and parses the “INCLUDE ALL” and then ignores the excludes.  After a bunch of testing to try and find something that would actually work to exclude TRIGGER operations, removing this comment:

--DDL INCLUDE ALL &

was the ‘magic’ fix.  Now triggers operations are correctly excluded when using INSTR. However, it still does not work to do the following:

DDL INCLUDE MAPPED &
 EXCLUDE ALL OPTYPE 'CREATE' & 
             OBJTYPE 'TRIGGER' 
;

CREATE TRIGGER DDL was still replicated with this configuration in the replicat.  But at least INSTR works.

Advertisements