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.
i think you can use the following to ignore triggers
EXCLUDE OBJTYPE ‘TRIGGER’
Hrm, I tried that in an “and” that is supposed to be ‘legal’ according to the docs (see last example), but perhaps I need to add the word ‘exclude’ to that line. I’ll have to test that when I get a chance. Thank you!