This document is an introduction to Oracle GoldenGate (DIPC remote agent)’s best practices and guidelines for configuring OGG in an active-active environment using the built in functionality of DML Auto Conflict Detection and Resolution (Auto CDR), LOB data type column is also take into consideration.
· RDBMS is Oracle Database 12.2+
· GoldenGate version 12.3/DIPC agent 18.2.3
· OS: All Oracle GoldenGate supported platforms for Oracle
Setting up Conflict Management is key to the OGG bi-direction data sync. Most Active-Active or Multi-Master Systems will have some form of conflict. Conflicts happen due to at least one row being modified on more than one system.
Tables should contain timestamp columns, or we have to add column onto the table likewise to achieve the same goal. The timestamp column i.e. CHANGE_TS is used for Conflict management and decide resolution of conflict when it happen.
CDR steps are as following:
Datatypes 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]).
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
TABLE option in extract
Example:
GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL)
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)
• Tag can be any number/letter [0-9 A-Z]
Example:
TRANSLOGOPTION EXCLUDETAG 0294
COMPARECOLS
Used by Replicat to detect and resolve update/delete conflicts
Example:
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL)
Timestamp column can be added without effecting the application or code base- leverages invisible columns/RDBMS 12.2 kernel.
Auto CDR makes the conflict management much simpler than before, you also need to meet below prerequisites
· Requires source and target to be Oracle Database 12.2
· Requires Integrated Extract (Classic Extract does not support invisible columns)
· Requires Integrated Replicat or Integrated Parallel Replicat
No Visible Application Changes:
• Easy to enable
• Invisible timestamp columns automatically created and maintained by database kernel
• Delete tombstone log to support updates-delete conflicts (new capability)
• Enables supplemental logging on required columns
• Allows ability to detect conflicts for LOB column changes
• No need to specify CDR/REPERROR parameters
• Last record wins (highest timestamp)
SQL> BEGIN
dbms_goldengate_adm.add_auto_cdr(
schema_name => ‘HR‘,
table_name => ‘EMPLOYEES‘);
END;
/
PL/SQL procedure successfully completed.
The procedure crates an invisible timestamp column to associate with the row, (If no LOB column exist in table). If we have multiple LOB columns, i.e. 2 LOB columns, it will create 2 separate invisible timestamp columns to associate with each LOB columns and one invisible timestamp column associate with the other columns by default.
If we have a test table with below structure:
After enable the Auto CDR by:
SQL> BEGIN
DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
schema_name => ‘hr‘,
table_name => ‘test‘);
END;
/
PL/SQL procedure successfully completed.
We query the ALL_GG_AUTO_CDR_COLUMNS table
SQL>SELECT TABLE_OWNER,
TABLE_NAME,
COLUMN_GROUP_NAME,
COLUMN_NAME,
RESOLUTION_COLUMN
FROM ALL_GG_AUTO_CDR_COLUMNS
ORDER BY TABLE_OWNER, TABLE_NAME;
We can see 3 CDRTS timestamp columns were created, 2 for LOB columns and one for the other columns.
By enable the Auto CDR, we
• Automatically creates an invisible timestamp column to associate with the row
• Invisible timestamp column automatically maintained
• Automatically enables Delete Tombstone Log Table
• Tombstone table automatically maintained
• Automatically enables all column supplemental logging
We can get the statics for CDR resolution by
GGCSI> stats replicat <group name>,reportcdr
From my test till now, with OGG Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005_FBO and Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production, LOB CDR resolution stats is NOT show here.
[Source] [Target]
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>alter system switch logfile;
SQL>archive log list;
SQL> alter PLUGGABLE database all open;
SQL>create user c##ogg identified by ogg;
SQL>GRANT DBA to c##ogg container=all;
SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘c##ogg‘,container=>‘ALL‘);
[Source] [Target]
SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;
SQL>alter database add supplemental log data;
SQL>alter database force logging;
SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;
[Source] [Target]
SQL> alter system set enable_goldengate_replication = true scope=both;
[Source] [Target]
SQL> create user ogg identified by ogg;
SQL>GRANT DBA to ogg;
SQL> BEGIN
dbms_goldengate_auth.grant_admin_privilege
(
grantee => ‘c##ogg‘,
privilege_type => ‘CAPTURE‘,
grant_select_privileges => TRUE
);
END;
/
[Source] [Target]
SQL>BEGIN
dbms_goldengate_auth.grant_admin_privilege
(
grantee => ‘c##ogg‘,
privilege_type => ‘APPLY‘,
grant_select_privileges => TRUE
);
END;
/
We use the DIPC remote agent version 18.2.3 in this manual, just download it and unzip, we can use it.
tnsnames.ora
SRC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.106)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDBSRC)
)
)
TGT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.106)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDBTGT)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.106)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
.bash_profile
# .bash_profile
# Get the aliases and functions
# User specific environment and startup programs
ORACLE_HOME=/u01/src/oci; export ORACLE_HOME
export JAVA_HOME=/usr/java/jdk1.8.0_92
export JRE_HOME=/usr/java/jdk1.8.0_92/jre
PATH=$JAVA_HOME/bin:$ORACLE_HOME/bin:$PATH ; export PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$JAVA_HOME/lib:
LD_LIBRARY_PATH=$ORACLE_HOME:/lib:/usr/lib; export LD_LIBRARY_PATH
export TNS_ADMIN=‘/u01/src/oci/network/admin/‘
1. Manager:
PORT 8809
2. Extracts:
-- Extract :extsrc
extract extsrc
userid c##ogg@orcl,password ogg
exttrail ./dirdat/es
TranLogOptions Excludetag 00
table pdbsrc.hr.*;
--Pump: pumsrc
extract pumsrc
passthru
rmthost <ip>,mgrport 8919
rmttrail ./dirdat/rs
table pdbsrc.hr.*;
3. Replicat:
--Replicat: reptgt
replicat reptgt
userid ogg@src, password ogg
discardfile reptgt.dsc, append
NOMAPINVISIBLECOLUMNS
map pdbtgt.hr.test, target pdbsrc.hr. test;
map pdbtgt.hr.jobs, target pdbsrc.hr. jobs;
1. Manager:
PORT 8919
2. Extracts:
Extract exttgt:
extract exttgt
userid c##ogg@orcl,password ogg
exttrail ./dirdat/et
TranLogOptions Excludetag 00
table pdbtgt.hr.*;
Pump: pumtgt
extract pumtgt
passthru
rmthost <ip>,mgrport 8809
rmttrail ./dirdat/rt
table pdbtgt.hr.*;
3. Replicat:
replicat repsrc
userid ogg@tgt, password ogg
discardfile repsrc.dsc, append
NOMAPINVISIBLECOLUMNS
map pdbtgt.hr.test, target pdbsrc.hr. test;
map pdbtgt.hr.jobs, target pdbsrc.hr. jobs;
Source:
GGCSI> obey Src.oby
--Source OGG Setup
add extract extsrc, integrated tranlog, begin now
add exttrail ./dirdat/es, extract extsrc
dblogin userid c##ogg@orcl password ***
register extract extsrc database container(pdbsrc)
add schematrandata PDBSRC.HR,allcols
info trandata PDBSRC.HR.*
add extract pumsrc, exttrailsource ./dirdat/es
add rmttrail ./dirdat/rs, extract pumsrc
add replicat reptgt, integrated ,exttrail ./dirdat/rt
Target:
GGCSI> obey Tgt.oby
----Target OGG Setup
add extract exttgt,integrated tranlog, begin now
add exttrail ./dirdat/et, extract exttgt
dblogin userid c##ogg@orcl password ***
register extract exttgt database container(pdbtgt)
add schematrandata pdbtgt.hr,allcols
info trandata pdbtgt.hr.*
add extract pumtgt, exttrailsource ./dirdat/et
add rmttrail ./dirdat/rt,extract pumtgt
add replicat repsrc, integrated ,exttrail ./dirdat/rs
Start the processes
Source and Target:
GGSCI> start mgr !
GGSCI> start er *
Source:
Target:
In all our test cases below, we took the DML against the source database firstly and then against target in very short time.
Source:
And then on Target:
Query the source again:
Review in GGSCI:
This time we are going to test a table with LOB data type column,
INSERT INTO hr.test VALUES(3,‘Pic No.1‘,bfilename(‘IMAGE_DIR‘,‘1.png‘)); commit;
What we get on the source and target:
Source:
Target:
Update the LOB value in table:
Source:
update hr.test set IMAGE =(bfilename(‘IMAGE_DIR‘,‘2.png‘)),MEMO=‘PIC #2‘ where id=3; commit;
Target:
update hr.test set IMAGE =(bfilename(‘IMAGE_DIR‘,‘3.png‘)),MEMO=‘PIC #3‘ where id=3; commit;
We also query the sample data for those 2 LOB column
For Pic#2:
For Pic#3:
Query source and target we get:
We did not get the CDR stats for LOB columns by running:
stats <Group_name>,reportcdr
1. Run the SQL to delete the record on both source and target:
delete from hr.emp where empno=1; commit;
2. Query the result on both databases:
select EMPNO,SAL,CDRTS$ROW from hr.emp where empno=1;
No record returns
3. Review in OGG
Source:
Target:
This paper covers the OGG Auto CDR feature which introduced in version 12.3.
• It has No Visible Application Changes and more simple to handle.
• It can handle to detect conflicts for LOB column changes.
• A Last record wins (with highest timestamp).
If you want to customize the conflict detection resolution more complex, not only highest timestamp win, you can still try classic CDR. Please refer to the OGG Administrative Guide for a more extensive view of CDR.
1. Oracle GoldenGate Best Practices: Active-Active Configuration with DDL and DML CDR --Tracy West
2. Oracle GoldenGate 12.3: Conflict, Detection and Resolution –Bobby Curtis
https://www.slideshare.net/BobbyCurtisMBA/goldengate-cdr-from-ukoug-2017
3. CDR Info: https://docs.oracle.com/goldengate/c1230/gg-winux/GGODB/automatic-conflict-detection-and-resolution2.htm#GGODB-GUID-29EDF7BF-6BD8-4E02-98AB-DDBB4719D038
Oracle GoldenGate Best Practices: Active-Active Configuration with DML Auto CDR
原文：https://www.cnblogs.com/ak666666/p/11974868.html