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.

About these ads

2 thoughts on “Oracle GoldenGate tip: DDL INCLUDE/EXCLUDE and comments

    • tylerv says:

      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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s