How to enable “only” drop partition DDL for Oracle Golden Gate

goldengateoracle-12c

I'm struggling with how to do this, and hoping somebody could assist with some ideas.

Oracle Golden Gate – general question. Version shouldn't be specific, however, I am currently running on:

Golden Gate: 12.2.0.1.170919

Oracle DB: 12.1.0.2.0

Using Golden Gate, Integrated Extract (and Replicat).

I have setup a test table using Interval partitioning (by month).
I have setup a GG flow to capture the data/rows for this table.

This works perfectly, no issues.

I then try to setup/enable DDL replication for this table.

I add the following to the extract .prm file:

DDL INCLUDE OBJNAME myschema.*

and that works perfectly. It captures ALL ddl ..

ALTER TABLE
TRUNCATE TABLE
CREATE INDEX
... etc.

Now, I want to restrict the DDL so that it captures ONLY :

ALTER TABLE myschema.* DROP PARTITION ...

that's it … no TRUNCATEs, no DROPs, no other ALTER TABLE

ie ALTER TABLE myschema.mytable MODIFY ( col ….
don't want to capture that – for example.

I've tried some of the following .. all to no avail:

DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'

this still allows ALL DDL

DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE ALL

This doesn't allow ANY DDL at all ..

DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE OPTYPE TRUNCATE      &
    EXCLUDE OPTYPE CREATE     

That manages to prevent TRUNCATEs and CREATEs ..
however, I can't add "ALTER" or "DROP" .. as it starts affecting/preventing the DROP PARTITION clause.

I've opened a ticket with Oracle to get some feedback from them .. however, so far, not much luck there.
I was hoping somebody here might actually know how to do this ?

Thanks!

Best Answer

Ok, so worked out a solution with Oracle via the SR .. wanted to put the solution here for completeness.

The solution was to use following DDL commmand:

DDL INCLUDE MAPPED INSTR 'DROP PARTITION'