Oracle GoldenGate 12c:
Conflict Detection and Resolution
What is it and how to use it
Bobby Curtis, EMBA
Senior Technical Consultant
E4 2015
May 31 – June 2, 2015
Hilton Hotel – Southlake Town Square
Dallas, TX
Submit an Abstract: http://www.enkitec.com/e4/submit-an-abstract
Accenture Enkitec Group
Enkitec joined Accenture’s Infrastructure Services as Accenture
Enkitec Group (May 2014)
➢ 17,000 Infrastructure Services professionals
➢ 52,000 Oracle professionals
Focus on Oracle Engineered Systems Solutions
➢ Database Migrations & Transformations
➢ Database-as-a-Service
➢ Oracle Applications on Engineered Systems
➢ Cloud-based solutions leveraging Engineered Systems
Speaker
Douglasville, Georgia
Senior Technical Consultant
IOUG, RMOUG, GAOUG, RACSIG
@dbasolved
http://dbasolved.com
bcurtis@enkitec.com
curtisbl@gmail.com
Agenda
• Oracle GoldenGate 12c Architecture
• Conflict Management
• Examples
• Statistics
• Summary
Oracle GoldenGate 12c
Architecture
Capture
Trail
Files
Pump
Delivery
Trail
Files
Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs.
As of V.11.2.1,
GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture
Trail: stages and queues data for routing.
Pump: distributes data for routing to target(s).
Route: data is compressed, encrypted for routing to target(s).
Delivery: applies data with transaction integrity.
New with
GoldenGate 12c, Integrated Delivery.
Oracle GoldenGate 12c
Architecture
Capture
Trail
Files
Pump
Delivery
Trail
Files
Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs.
As of V.11.2.1,
GoldenGate offers two options for capture for Oracle; Classic & Integrated Capture
Trail: stages and queues data for routing.
Pump: distributes data for routing to target(s).
Route: data is compressed, encrypted for routing to target(s).
Delivery: applies data with transaction integrity.
New with
GoldenGate 12c, Integrated Delivery.
Delivery
Trail
Files
Trail
Files
Pump
Capture
bi - directional
Oracle GoldenGate 12c
Conflict Management
Capture
Trail
Files
Pump
Delivery
Trail
Files
Delivery
Trail
Files
Trail
Files
Pump
Capture
bi - directional
• All most all Active/Active systems will have a form of conflict
• Conflicts happen due to at least one row is modified on more than one
system with difference data
• Common Situations:
1. Row already exists (Inserts)
2. Row does not exist (deletes)
3. Values in row already changed (updates)
Conflict Management
Example - Financial Institution: Active/Active
Capture
Trail
Files
Pump
Delivery
Trail
Files
Delivery
Trail
Files
Trail
Files
Pump
Capture
bi - directional
ACCTINFO
ID (PK)
NAME
ADDRESS
CITY
BALANCE
CHANGE_TS
ACCTINFO
ID (PK)
NAME
ADDRESS
CITY
BALANCE
CHANGE_TS
AcctInfo Table: General details related to bank accounts between
San Francisco and Atlanta
Capture
Trail
Files
Pump
Delivery
Trail
Files
Delivery
Trail
Files
Trail
Files
Pump
Capture
bi - directional
Conflict Management
Example - Financial Institution: Active/Active
ACCTINFO
ID (PK)
NAME
ADDRESS
CITY
BALANCE
CHANGE_TS
ACCTINFO
ID (PK)
NAME
ADDRESS
CITY
BALANCE
CHANGE_TS
Note: Tables should be able to identify when a record has been
changes.
CHANGE_TS
CHANGE_TS
Conflict Management
Conflicts Supported
Basic Resolution for conflicts within GoldenGate environment:
• INSERT
Uniqueness conflict for INSERT
• UPDATE
conflict for “No Data Found” when row exists (before image diffs)
conflict for “No Data Found” when row does not exists
• DELETE
conflict for “No Data Found” when row exists (before image diffs)
conflict for “No Data Found” when row does not exists
Conflict Management
Supported Data Types
Data Types that can be compared are supported:
• NUMERIC
• DATE
• TIMESTAMP
• CHAR/NCHAR
• VARCHAR/NVARCHAR
Typically these data types are used with parameters like COMPARECOLS,
GETBEFORECOLS, and in resolution parameters using RESOLVECONFLICT
([USEMIN] | [USEMAX])
Conflict Management
Before and After Images
ACCTINFO
BEFORE
AFTER
ID (PK)
10
10
NAME
32
32
ADDRESS
96 Smith Rd
96 Smith Rd
CITY
Atlanta
Atlanta
BALANCE
1500
1550
CHANGE_TS
2014-10-29
00.00.00.000000 AM
2014-10-30
01.32.05.000000 PM
• Transaction logs are needed
for recovery
• Before Images
• Deletes and Updates
• After Images
• Inserts and Updates
Note: LogDump utility is useful for
reading trail files to identify before/after
images
Conflict Management
Requirements - Environment
Full before image of each record
Transactional Data
GGSCI> dblogin user <gg user> password <password>
GGSCI> add trandata SFAA.ACCTINFO, allcols
Behind the scene:
SQL> ALTER TABLE SFAA.ACCTINFO ADD SUPPLEMENTAL LOG GROUP
GGS_94879(id, name, address, city, balance, change_ts)
ALWAYS;
Note: System generated log group names can be located in DBA_LOG_GROUPS
Conflict Management
Requirements - Environment
• Sequences need to be unique on each
system
• Use starting value and increment by
number of systems
• Applies to existing sequences and
identity columns
1+3
3+3
2+3
Conflict Management
Requirements - Extract
• LOGALLSUPCOLS
Extract captures before images for UPDATE operations
Extract captures before images of supplemental logged columns for both
UPDATE and DELETE operations
• GETBEFORECOLS
Ensures certain columns are logged
GETBEFOREUPDATES (DB2 only)
TABLE option in extract
GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL)
Conflict Management
Requirements - Extract
• TRANSLOGOPTION EXCLUDETAG
• Tag supplied to transaction as it is extracted to prevent receiving replicat
from trying to send it back to source
• Classic/Integrated Extract (primary or pump)
• Tag can be any number/letter [0-9 A-Z]
Example:
TRANSLOGOPTION EXCLUDETAG 0294
Conflict Management
Requirements - Replicat
• REPERROR
Used to control how Replicat responds to errors
Default:
REPERROR(default, abend)
For CDR:
REPERROR(default, exception)
REPERROR(default2, [ abend | discard ])
REPERROR(-1, exception)
Conflict Management
Requirements - Replicat
• COMPARECOLS
Used by Replicat to detect and resolve update/delete conflicts
• RESOLVECONFLICT
Used by Replicat in bi-directional/multi-master to handle conflicts for DML
operations
Example:
MAP SFAA.ACCTINFO, TARGET ATLAA.ACCTINFO,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT,USEMAX(CHANGE_TS)));
ResolveConflict
Insert
Delete
Update
InsertRowExists
UpdateRowMissing
UpdateRowExists
DeleteRowMissing
DeleteRowExists
Overwrite
Ignore
Discard
Min/Max
Overwrite
Ignore
Discard
Overwrite
Ignore
Discard
Ignore
Discard
Ignore
Discard
Min/Max
Delta
Overwrite
Oracle GoldenGate 12c
Resolve Conflict Parameter
Conflict Management
Requirements - Exceptions
• Exception Table[s]
• User defined
• Used for troubleshooting or
handling errors
• Referenced in REPERROR and
MAP parameters
• Best if defined through macros
Conflict Management
Beware
BATCHSQL
• Increases apply performance of Replicat by organizing similar SQL statements
and apply them at an accelerated rate.
• CDR is not performed in this mode
• Replicat reverts, as needed, to help resolve conflict
1. GROUPTRANSOPS
2. Single-transaction mode
Conflict Management
Example 1 – Insert Row Exists
MAP SFAA.ACCTINFO, TARGET ATLAA.ACCTINFO,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX(CHANGE_TS)));
ACCTINFO
SF
ATL
ID
944
944
NAME
32
32
ADDRESS
55 5th Street
55 5th Street
CITY
Symrna
Symrna
BALANCE
1100
1200
CHANGE_TS
31-OCT-14
10.40.36.000000000 PM
31-OCT-14
10.40.
40.000000000 PM
INSERTROWEXISTS
• Violates the unique
constraint on the target
Conflict Management
Example 2 – Update Row Exists
MAP SFAA.ACCTINFO, TARGET ATLAA.ACCTINFO,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN(CHANGE_TS)));
ACCTINFO
BEFORE
AFTER
ID
944
944
NAME
32
32
ADDRESS
55 5th Street
55 5th Street
CITY
Symrna
Symrna
BALANCE
1150
1100
CHANGE_TS
31-OCT-14
10.40.
36.000000000 PM
31-OCT-14
10.40.40.000000000 PM
UPDATEROWEXISTS
• Updated row exists on
target side
• One or more columns
have a before image
different from current
value
Conflict Management
Example 3 – Delete Row
MAP SFAA.ACCTINFO, TARGET ATLAA.ACCTINFO,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, [ DISCARD | IGNORE ]));
DELETEROWMISSING
• Deleted row does not exist
on target
ACCTINFO
SF
ATL
ID
944
null
NAME
32
null
ADDRESS
55 5th Street
null
CITY
Symrna
null
BALANCE
1100
null
CHANGE_TS
31-OCT-14
10.40.36.000000000 PM null
Conflict Management
Statistics
Replicating from ATLAA.ACCTINFO to SFAA.ACCTINFO:
*** Total statistics since 2014-11-02 00:30:43 ***
Total inserts
719.00
Total updates
93.00
Total deletes
524.00
Total discards
0.00
Total operations
1336.00
Total CDR conflicts
377.00
CDR resolutions succeeded
377.00
CDR INSERTROWEXISTS conflicts
257.00
CDR UPDATEROWEXISTS conflicts
93.00
CDR DELETEROWMISSING conflicts
27.00
GGSCI> stats replicat <group name>, reportcdr
Example Extract Parameter File
-- CHECKPARAMS
EXTRACT EXTAA
USERID <gg user>, PASSWORD <pwd>
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDETAG 0294
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/db12cr1")
SETENV (ORACLE_SID="oragg")
WARNLONGTRANS 10m, CHECKINTERVAL 5m
LOGALLSUPCOLS
EXTTRAIL ./dirdat/la
WILDCARDRESOLVE IMMEDIATE
TABLE SFAA.ACCTINFO,
GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL);
Example Replicat Parameter File
-- CHECKPARAMS
REPLICAT REPAB
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/db12cr1")
SETENV (ORACLE_SID="oragg")
USERID ggate, PASSWORD <pwd>
DISCARDFILE ./dirrpt/REPAB.discard APPEND MEGABYTES 100
REPERROR (default, exception)
REPERROR (default2, abend)
ASSUMETARGETDEFS
WILDCARDRESOLVE IMMEDIATE
INCLUDE ./dirmac/exceptions.mac
MAP ATLAA.ACCTINFO, target SFAA.ACCTINFO,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (insertrowexists, (default, usemax(change_ts))),
RESOLVECONFLICT (updaterowexists, (default, usemin(change_ts))),
RESOLVECONFLICT (deleterowmissing, (default, discard));
map ATLAA.ACCTINFO,
#exception_handler(ggate);
• Architecture
• Conflict Management
• Parameters Required
• Examples
• INSERTROWEXISTING
• UPDATEROWEXISTING
• DELETEROWMISSING
• Statistics
Conflict Management
Summary
Contact Info
@dbasolved
http://dbasolved.com
bcurtis@enkitec.com
curtisbl@gmail.com
