Tag Archives: REPLICAT

Extending GoldenGate Change Data Capture With Eventactions

In the previous post, I discussed a very simple setup of GoldenGate for the purpose of implementing Change Data Capture.  Occasionally, depending on the requirements and the data volume, it may be worthwhile to suspend replication while the application processes data or performs some other work.  One way to do this is with eventactions.  GoldenGate eventactions are a simple way of telling Goldengate to do something if a certain data situation is encountered.  In this example, I am going to data drive my event actions by using a control table with 2 rows:

EVENT_DESC EVENT_TMSTP
SUSPEND REPLICAT 13-DEC-16 11.31.00.881031 AM
RESUME REPLICAT 13-DEC-16 11.48.51.065772 PM

Based on an update to the timestamp in the source system, the downstream replicat will utilize that data to either suspend or resume the replicat.

The only way that this event action can be completely data driven, is to have 2 replicats.  One which processes all of the change data and another which processes the ‘resume’ command once issued.  The second replicat is needed due to the fact that the first replicat cannot process any ‘resume’ commands or apply any data on its own because it is SUSPENDED!

In the replicat parameter which will process the suspend, the parameters may look like the following:

--Standard entries in a replicat parameter file

MAP MYSCHEMA.EVENTS, TARGET MYSCHEMA.EVENTS, &
COLMAP (USEDEFAULTS), &
FILTER (@STREQ (EVENT_DESC, 'SUSPEND REPLICAT' )), &
EVENTACTIONS (IGNORE RECORD, LOG INFO, REPORT, SUSPEND, CP BOTH);

In the replicat that will process the resume, the parameters may look like:

--Standard entries in a replicat parameter file

ALLOWDUPTARGETMAP

MAP MYSCHEMA.EVENTS, TARGET MYSCHEMA.EVENTS, &
COLMAP (USEDEFAULTS), &
HANDLECOLLISIONS;

MAP MYSCHEMA.EVENTS, TARGET MYSCHEMA.EVENTS, &
FILTER (@STREQ (EVENT_DESC, 'RESUME REPLICAT' )), &
EVENTACTIONS (IGNORE RECORD, LOG, REPORT, CP AFTER,&
SHELL ('./dirshell/resume_replicat.sh $1', VAR $1 = 'RCDC1'));

If you need a more robust mechanism wherby you need to check some other condition prior to issuing the suspend, you can also extend the suspend action further by executing a SQL Statement as shown within the replicat.  In this case, we need to make sure that if a suspend was issued, it is not prior to a resume record being issued.  This could be very helpful to safeguard against accidental suspends being processed since it is very possible that the CDC replicat may be processing data which is well behind that of the one that processes the ‘resume’ event.

--Standard entries in a replicat parameter file

MAP MYSCHEMA.EVENTS, TARGET MYSCHEMA.EVENTS, &
COLMAP (USEDEFAULTS), &
FILTER (@STREQ (EVENT_DESC, 'SUSPEND REPLICAT' )), &
SQLEXEC (ID LOOKUP_RESUME_DATE, &
QUERY ' SELECT EVENT_DESC,EVENT_TMSTP, COUNT(*) SUSPEND_OK FROM MYSCHEMA.EVENTS &
WHERE EVENT_DESC = :p_resume_event_desc AND EVENT_TMSTP < :p_suspend_tmstp & AND NOT EXISTS( & SELECT 1 FROM MYSCHEMA.EVENTS & WHERE EVENT_DESC = :p_suspend_event_desc AND EVENT_TMSTP > :p_suspend_tmstp) &
GROUP BY EVENT_DESC, EVENT_TMSTP ', &
PARAMS (p_resume_event_desc = 'RESUME REPLICAT', p_suspend_event_desc = 'SUSPEND REPLICAT', p_suspend_tmstp = EVENT_TMSTP), &
TRACE ALL, &
BEFOREFILTER), &
FILTER (@STREQ (LOOKUP_RESUME_DATE.SUSPEND_OK, 1 )), &
EVENTACTIONS (IGNORE, LOG, REPORT, SUSPEND, CP BOTH);

As you can see, event actions are very powerful and can be extended in a variety of ways.  This is just one example.  If you choose to implement this, make sure you also account for the event action in any monitoring scripts you have because GoldenGate will show lag while the suspend action is valid.  Have fun!

Implement GoldenGate Change Data Capture

Recently, I was asked to implement a solution to fulfill data audit requirements which captured the complete lifecycle of a row of data.  As it turns out, Oracle GoldenGate, is a perfect tool to accomplish this requirement of Change Data Capture.

Setup of Change Data Capture

Setup is very similar to other GoldenGate installations.  You need to make sure that you capture both the before and after images in the Extract and the remaining parameters required to do this are within the replicat parameter file. Most of the time I implement this via a MACRO, but for sake of simplicity, I will show it as a basic parameter file:

MAP SOE.STRESSTESTTABLE, TARGET SOE.STRESSTESTTABLE_CDC INSERTALLRECORDS. &
COLMAP ( &
USEDEFAULTS, &
COMMIT_TMSTP = @GETENV('GGHEADER', 'COMMITTIMESTAMP'), &
RECORD_SCN_NBR = @GETENV('TRANSACTION', 'CSN'), &
ORIG_DML_TYPE_NM = @GETENV('GGHEADER', 'OPTYPE'), &
BFR_AFT_IND_CD = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'), &
TARGET_INSERT_TMSTP = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')), &
DML_TYPE_CD = @CASE (@GETENV ('GGHEADER', 'OPTYPE'), &
                'INSERT', 'I', &
                'UPDATE', 'U',&
                'SQL COMPUPDATE', 'U', &
                'PK UPDATE', 'U', &
                'DELETE', 'D', &
                'TRUNCATE', 'T', &
                'ENSCRIBE COMPUPDATE','U', &
                @COLSTAT(NULL)));

The important part to notice about this parameter file is the INSERTALLRECORDS keyword.  This takes every DML operation and converts it to an insert.  Additional data is also required to correctly interpret the row and is accomplished by the addition of the following columns to every table you wish to capture changes on:

  • COMMIT_TMSTP – date which the source row was committed
  • RECORD_SCN_NBR – corresponding SCN of when the source row was committed
  • ORIG_DML_TYPE_NM – GoldenGate Operation Type
  • BFR_AFT_IND_CD – Indicates if row is the before image or after image
  • TARGET_INSERT_TMSTP – Timestamp when row was inserted to target
  • DML_TYPE_CD – Translates the GoldenGate Operation Type to indicate a simple form of CREATE, UPDATE or DELETE operation.

Change Data Capture in Action

Once the GoldenGate scheme is started, the change data capture starts recording the changes as normal processing occurs.  Using the SOE.STRESSTEST provided by Swingbench, we can see the Change Data Capture in action:

COLUMN DATA ROW 1 DATA ROW 2 DATA ROW 3
ID 17346 17346 17346
AINT 4730828 4730829 4730829
AFLOAT 300.60 301.00 301.00
ASMALLVARCHAR HsnzUb HzdhHY HzdhHY
COMMIT_TMSTP 16-DEC-16 09.18.22.00 16-DEC-16 09.18.38.00 16-DEC-16 09.20.12.00
RECORD_SCN_NBR 11405081 11404603 11479311
ORIG_DML_TYPE_NM INSERT SQL COMPUPDATE DELETE
BFR_AFT_IND_CD AFTER AFTER BEFORE
TARGET_INSERT_TMSTP 16-DEC-16 09.22.33.27 16-DEC-16 09.29.20.84 16-DEC-16 09.29.41.23
DML_TYPE_CD I U D

Building Eventactions on top of Change Data Capture

This was an overly simple example of how to implement Change Data Capture.  In the next blog post, I will show how to use event actions to suspend the replicat using a data driven model.  This methodology will give a quiesced time where a separate application can process the changes and perform table truncates vs. a model where no suspend takes place and the application must perform transaction deletes.  When the application is finished processing, it can then ‘un-suspend’ the replicat using the same data driven model and continue processing.