Purge DDL tables in GoldenGate for multiple instances

When setting up a DDL replication in Integrated Capture there are couple tables that gets populated on a regular basis, Oracle provides a purge process that one can add to the manager parameter file and it will purge as configured.

e.g:

 userid ogg, password xxxx  
 PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  
 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  

This works really well when you have one instance that uses GoldenGate to replicate.

However if you have 2 or more database instances and you enable DDL replication from the same GoldenGate Home, multiple database logins in the same manager are not supported.

e.g:

 userid ogg@SID1, password xxxx  
 PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  
 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  
 userid ogg@SID2, password xxxx  
 PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  
 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30  

At this time the only way to get around this restriction is to have multiple GoldenGate homes, one for each database instance the extract need to connect or create a temporary customized purge process as per below.

This procedure will delete rows older then 10 days.

 CREATE OR REPLACE PROCEDURE GGATE.PURGE_GGS_HISTORY IS     
    BEGIN  
    delete from GGS_MARKER where sysdate - to_date(OPTIME, 'yyyy-mm-dd hh24:mi:ss')  > 10;  
    delete from GGS_DDL_HIST where sysdate - to_date(OPTIME, 'yyyy-mm-dd hh24:mi:ss')  > 10;  
    commit;  
    EXCEPTION  
      WHEN NO_DATA_FOUND THEN  
        NULL;  
      WHEN OTHERS THEN  
        -- Consider logging the error and then re-raise  
        RAISE;  
 END PURGE_GGS_HISTORY;  
 /  

It important to point out that DDL replication needs those records to replicat to the target, if for some reason the purge deletes the row before it gets replicated it will cause issues with DDL.

That’s why the supported PURGE procedure is reliable and preferred method.

From GoldenGate Documentation:

Caution:

“Use caution when purging the history tables. They are critical to the integrity of the DDL synchronization processes. Premature purges are non-recoverable through Oracle GoldenGate. To prevent any possibility of permanent DDL data loss, make regular backups of the Oracle GoldenGate DDL schema.”

https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters120.htm#GWURF594

https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters121.htm#GWURF596

Advertisements

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