Maximum Availability Architecture – Oracle’s industry-leading set of database high availability capabilities
- December 18, 2014
Oracle GoldenGate Active-Active Part 3
Here is the last (3 of 3) blog posting on Active-Active
replication for OGG, and my post this time will cover the actual usage of the
CDR resolution routines and examples of how they are built. Part 1 is located here, and part 2, here. I’ll cover 2 different use cases. The first
will be timestamp based and the second will be trusted source. As a refresher, timestamp is going to have
the record with the lowest timestamp win (i.e. whichever record came in first)
and the trusted source is going to assume that one system always takes
precedence over another system.
For these examples, I’m going to use macros, which makes it
so much easier and cleaner to read, and it dramatically reduces the amount of
typing I have to do.
My macro file, will be called cdr_macros.prm. I normally wouldn’t want to mix trusted
source and timestamp in the same environment, but I’m doing it here just as an
example. In this macro file, I have
included every CDR function that I want to use, on all systems, for both
extracts and replicats. This way if I need to make a change to my CDR rules, I
can make the change in the macro file and it effects the entire server. Just make sure to make the same change to
each OGG environment. Inside each
macro, there is a short description of what the command is going to be used for.
MACRO
#ExtractCdrDate BEGIN COMMENT This is used to ensure
that the key columns + the UPDATE_TIME COMMENT column is always brought
over as part of the trail file record GETBEFORECOLS (ON
UPDATE KEYINCLUDING(UPDATE_TIME), ON DELETE KEYINCLUDING(UPDATE_TIME)) ,
FETCHCOLS (*) END; COMMENT END TO ExtractCdrDate
MACRO
#ExtractCdrAllColunms BEGIN COMMENT This is used when I want
to ensure that ALL columns are in the COMMENT trail file for each
record. It has a higher overhead, so be COMMENT careful on how
frequently it is used. GETBEFORECOLS (ON
UPDATE ALL, ON DELETE ALL), FETCHCOLS(*) END; COMMENT END TO
ExtractCompAllColunms
MACRO
#DateCompare BEGIN COMMENT This is used when doing
a timestamp resolution where the lowest COMMENT timestamp wins. COMPARECOLS (ON
UPDATE KEYINCLUDING (UPDATE_TIME),ON DELETE KEYINCLUDING (UPDATE_TIME)), RESOLVECONFLICT
(UPDATEROWEXISTS,(mon_resolution_method, USEMIN (UPDATE_TIME),COLS(*))
(DEFAULT, DISCARD)), RESOLVECONFLICT
(INSERTROWEXISTS, (DEFAULT, USEMIN
(UPDATE_TIME) , COLS(*))), RESOLVECONFLICT
(DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT
(UPDATEROWMISSING, (DEFAULT, OVERWRITE)), RESOLVECONFLICT
(DELETEROWMISSING, (DEFAULT, DISCARD)) END; COMMENT END TO DateCompare
MACRO
#FromTrusted BEGIN COMMENT This resolution is used
on the non-trusted environment to COMMENT allow operations from
the trusted server to overwrite the existing COMMENT data when there is a
conflict. COMPARECOLS (ON
UPDATE ALL,ON DELETE ALL), RESOLVECONFLICT
(UPDATEROWEXISTS, (DEFAULT, OVERWRITE))
, RESOLVECONFLICT
(INSERTROWEXISTS, (DEFAULT, OVERWRITE))
, RESOLVECONFLICT
(DELETEROWEXISTS, (DEFAULT, OVERWRITE))
, RESOLVECONFLICT
(UPDATEROWMISSING, (DEFAULT, OVERWRITE)) , RESOLVECONFLICT
(DELETEROWMISSING, (DEFAULT, DISCARD)) END; COMMENT END TO FromTrusted
MACRO
#FromNoNTrusted BEGIN COMMENT This resolution is used
to discard the record any time there is a COMMENT conflict, when the
record comes from the non-trusted server COMPARECOLS (ON
UPDATE ALL,ON DELETE ALL), RESOLVECONFLICT
(UPDATEROWEXISTS, (DEFAULT, DISCARD)) , RESOLVECONFLICT
(INSERTROWEXISTS, (DEFAULT, DISCARD)) , RESOLVECONFLICT
(DELETEROWEXISTS, (DEFAULT, DISCARD)) , RESOLVECONFLICT
(UPDATEROWMISSING, (DEFAULT, DISCARD)) , RESOLVECONFLICT
(DELETEROWMISSING, (DEFAULT, DISCARD)) END; COMMENT END TO FromNonTrusted
*********************************************************************************************************
Now that all the hard work is done, and I've defined my rules for both Extract and Replicat in the Macro file, I can easily add those in. In the Extract I simply modify my TABLE statements to
include the additional macro to tell OGG which columns to write to the trail
file. In this case, I'm using the #ExtractCdr macros from the first part of the file to instruct OGG which columns to include in the trail file. This ensures that the resolution routines always have the data they need to perform the specified resolution.
TABLE DEMO.VCRYPT_ACCOUNTS , #ExtractCdrDate();
TABLE DEMO.VCRYPT_ACCOUNTS_HIST ,
#ExtractCdrAllColunms();
The changes to the MAP statements in the Replicat
parameter file itself is extremely elegant and simple. In the Replicat, the changes are also very
straightforward, by simply adding the macros that were defined above.
MAP DEMO. VCRYPT_ACCOUNTS, TARGET DEMO.VCRYPT_ACCOUNTS, #DateCompare(); MAP DEMO.VCRYPT_ACCOUNTS_HIST, TARGET DEMO.VCRYPT_ACCOUNTS_HIST , #FromTrusted();
Using the macro method, it’s easy to identify which
objects are using each conflict detection and resolution routine, and if you
need to make a change, you can make it once in the macro file and it will
affect every parameter file. The methodologies and best practices in the last
few of my blog postings on Active-Active Replication in GoldenGate and the white
paper here: http://www.oracle.com/us/products/middleware/data-integration/golden-gate-active-active-1887519.pdf should help implement robust Active-Active replication..