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!
🛩️ Shane Borden's Technology Blog 