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|
|COMMIT_TMSTP||16-DEC-16 09.18.22.00||16-DEC-16 09.18.38.00||16-DEC-16 09.20.12.00|
|TARGET_INSERT_TMSTP||16-DEC-16 09.22.33.27||16-DEC-16 09.29.20.84||16-DEC-16 09.29.41.23|
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.