GoldenGate Active-Active Database Replication with Conflict Detection and Resolution

Bi-Directional Replication also known as Active-Active Replication, is a configuration where both databases are used as an active site and applications perform DML/DDL activities on both Databases. The OGG processes is configured to replicate the transaction data of both the source databases back and forth to each other.

1. Advantages

Bi-directional GoldenGate Replication can be useful for the following scenarios.

• High Availability
• Load Balancing
• Disaster Recovery
• Supports Homogeneous and Heterogeneous Configuration
• Efficient Utilization Of Hardware

2. Key Considerations

For a successful implementation of Bi-Directional Replication a careful analysis is required. You must understand what the supported and unsupported objects are, business requirements and more importantly how to identify and resolve conflicts.

a. Primary Key
A Primary Key is used to identify and resolve conflicts. Make sure every table to be included in the replication has a Primary Key.

b. Sequences
Sequences are not supported in Bi-Directional Replication. You must deploy one of the methods below to overcome it.

i. ODD/EVEN
ii. RANGE
iii.CONCATENATE

c. Triggers
Trigger causes uniqueness issues. You must do the following to overcome them.
i. You must disable the triggers on the target or use OGG to suppress them.
ii. Specify the DBOPTIONS SUPPRESSTRIGGERS parameter in Extract parameter file when using GoldenGate version 10.2.0.4 or 11.2.0.2 (NOT 11.1).
 
d. Data Looping
In a Bi-Directional replication configuration, each side processes transaction actively. For Insert transactions it can cause uniqueness issues and for updates it can spawn into an infinite loop.

To avoid data looping specify the following parameters should be used in Extract process, on both databases.

TRANLOGOPTIONS EXCLUDEUSER GGUSER
OR
TRANLOGOPTIONS EXCLUDEUSERID 10

e. TRUNCATE Table
Truncate table operations are not detected by data looping. You must truncate tables only in one database. You can accomplish it by one of the following ways:

i. Control user access using privileges so that they can truncate tables only in one direction.
OR
ii. Specify the parameter “GETTRUNCATE” from source to target and “IGNORETRUNCATE” from target to source.

f. LAG
Oracle GoldenGate is an Asynchronous solution. It is possible that there may be LAG at times depending on the transaction volume or network issue or when process are down. This LAG can cause data inconsistencies. To avoid this situation make sure that there is very little or no LAG or have proper SLAs.

g. OGG CONFLICTS
Conflicts are born to happen in Bi-Directional Replication as Oracle GoldenGate is an Asynchronous solution.

You will see the following four different conflicts in OGG replication.
i. CONFLICT FOR INSERTS
ii. CONFLICT FOR UPDATES
iii. CONFLICT FOR DELETES
iv. CONFLICT FOR UPDATE/DELETE

h. Timestamp Column
A Primary Key alone is NOT sufficient to handle conflicts. You must use another column or combination of columns to handle conflicts. A Timestamp column stores the commit time of the DML. This column can be populated with the help of Application or a Trigger. We will make use of timestamp column along with the Primary Key column to identify and resolve conflicts.

Make sure every table part of replication have a column with timestamp or date data type.

i. Conflict Detection And Resolution
Start with OGG version 11.2, Oracle has provided built-in CDRs. These built-in CDRs can be used in OGG 11.2.  We will use “RESOLVECONFLICT” parameter of MAP statement to resolve conflicts.

3. Limitations

• Bi-Directional Replication works only on Windows, UNIX/Linux.
• CDR works with numeric, date/timestamp colums and char/varchar2 only.
• LOB , ADT and UDT data types are NOT supported with CDR.
• BATCHSQL is not supported in Bi-Directional Replication.

OGG Bi-Directional Replication Setup

Environment Details

DESCRIPTION	SOURCE SITE A	TARGET SITE B
Database Name	SRCDB	TGTDB
Schema Name	SCOTT	SCOTT
Database Version	11.2.0.3	11.2.0.3
OGG Version	11.2.1.0.20	11.2.1.0.20
Operating System	Linux 64 Bits	Linux 64 Bits
Hostname	Source	Target

4. Identify Tables for Bi-Directional Replication & dependent Objects

a. For this Demo I will be using a table “SCOTT.ORDERS”
SQL> create table scott.orders
(rder_id         number(10) Primary key,
rder_date        date,
product         varchar2(30),
qty              number(10),
total_amount     number(10),
handling_note    varchar2(30),
cust_id          number(30),
dml_timestamp    date);b. We will use a sequence to populate oder_id column with unique number.
SQL> create sequence scott.orders_seq start with 101 increment by 2 cache 20 nocycle;NOTE: In Bi-Directional Replication we don’t want to replicat sequences using OGG. After the initial load we change sequences to ODD/EVEN values.

Source —-> ODD Sequences
Target —-> EVEN Sequences

c. In this demo we will use a trigger for orders table to update “DML_TIMESTAMP” column whenever insert and update is performed on this table.
SQL> create or replace trigger scott.orders_trig
before update or insert
on scott.orders
referencing old as old new as new
for each row
-- if SUPPRESSTRIGGERS is not used, depending on the oracle db version
-- to avoid firing of triggers for GGS user, WHEN clause can be used
-- WHEN (USER != 'GGS')
begin
if inserting then
:new.order_id :=scott.order_seq.nextval();
:new.dml_timestamp := sysdate;
else
:new.dml_timestamp := sysdate;
end if;
end;
/

5. Configure Manager Process

a. Configure OGG Manager Process on source and target.A Manager process is required for process communication.

On Source
$ cd $GGS_HOME
$./ggsci
GGSCI> Edit params mgr
PORT 7809
(Save and Exit file)
GGSCI> start mgr
GGSCI> info mgr

On Target
$ cd $GGS_HOME
$./ggsci
GGSCI> Edit params mgr
PORT 7809

(Save and Exit file)

GGSCI> start mgr
GGSCI> info mgr

6. Enable Supplemental Logging – Source

a. At Database-Level
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database;b. At Table-Level. We will be using CDR for resolving conflicts, so in-order to make CDR work properly we will enable supplemental logging of all columns of the tables.
GGSCI> dblogin userid gguser@amer, password oracle_4U
GGSCI> add trandata west.orders, cols (order_date, product, qty, total_amount, handling_note, cust_id, record_updated, record_created)

7. Configure Extract and Pump Processes – Source

a. Add Primary Extract
GGSCI> dblogin userid ggs@srcdb, password oracle
GGSCI> edit params extsrc
extract extsrc
userid ggs@srcdb, password oracle
exttrail /u01/app/ggs/dirdat/es
TRANLOGOPTIONS EXCLUDEUSER GGS
REPORTCOUNT EVERY 10 MINUTES, RATE
GETUPDATEBEFORES
GETTRUNCATES
-- TO GET THE BEFORE IMAGE OF ALL COLUMNS FOR CDR USING GETBEFORCOLS
TABLE SCOTT.ORDERS, GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL);

(Save and Exit the file)

GGSCI> add extract extsrc, tranlog, begin now
GGSCI> add exttrail /u01/app/ggs/dirdat/es, extract extsrc, megabytes 100
GGSCI> start extsrc
GGSCI> info allb. Add Data Pump Extract
GGSCI> edit params psrc
extract psrc
PASSTHRU
RMTHOST 192.168.1.30, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg_euro/dirdat/es
REPORTCOUNT EVERY 10 MINUTES, RATE
TABLE SCOTT.*;

(Save and Exit the file)

GGSCI> add extract psrc, exttrailsource /u01/app/ggs/dirdat/es
GGSCI> add rmttrail /u01/app/ggs/dirdat/es, extract psrc, megabytes 100
GGSCI> start psrc
GGSCI> info all

8. Perform Initial Load

At this stage perform the initial load from source to target.Since OGG online replication doesn’t copy the existing data from source to target an Initial load is required so that replication can start from a synchronized state.

In the last step we have configured Extract/Pump so that they can capture the committed transactions that are taking place during initial load. This results in NO Data Loss.

There are several ways in which you can perform Initial load from source to target:

a.  Oracle GoldenGate
b.  Data Pump
c.  RMAN
d.  Physical Standby

NOTE: Database specific methods are preferred over OGG in homogeneous environment as it faster than OGG.

Choose a method of your choose and perform Initial Load. Since we don’t have existing data in the source tables, I will simply create the table and its dependent objects without performing an initial load

On Target
a. Create table and its dependent objects
SQL> create table scott.orders
(order_id     number(10) Primary key,
order_date      date,
product         varchar2(30),
qty             number(10),
total_amount    number(10),
handling_note   varchar2(30),
cust_id         number(30),
dml_timestamp   date);

SQL> create sequence scott.orders_seq start with 101 increment by 2 cache 20 nocycle;

b. Modify sequences on target
SQL> alter sequence scott.orders_seq increment by 1;
SQL> select scott.orders_seq.nextval from dual;
SQL> alter sequence scott.orders_seq increment by 2;

c. Create trigger on the target to populate dml_timestamp column
SQL> create or replace trigger scott.orders_trig
before update or insert
on scott.orders
referencing old as old new as new
for each row
-- if SUPPRESSTRIGGERS is not used, depending on the oracle db version
   -- to avoid firing of triggers for GGS user, WHEN clause can be used
   -- WHEN (USER != 'GGS')
begin
            if inserting then
:new.order_id :=scott.order_seq.nextval();
:new.dml_timestamp := sysdate;
             else
:new.dml_timestamp := sysdate;
         end if;
end;
/

9. Enable Supplemental Logging – Target

a.  At Database-Level
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database;b.  At Table-Level
GGSCI> dblogin userid gguser@amer, password oracle_4U
GGSCI> add trandata west.orders, cols (order_date, product, qty, total_amount, handling_note, cust_id, record_updated, record_created)

10. Configure Extract and Pump Processes – Target

a. Add Primary Extract
GGSCI> dblogin userid ggs@tgtdb, password oracle
GGSCI> edit params exttgt
extract exttgt
userid ggs@tgtdb, password oracle
exttrail /u01/app/ggs/dirdat/et
TRANLOGOPTIONS EXCLUDEUSER GGS
REPORTCOUNT EVERY 10 MINUTES, RATE
GETUPDATEBEFORES
GETTRUNCATES

-- TO GET THE BEFORE IMAGE OF ALL COLUMNS FOR CDR USING GETBEFORCOLS
TABLE SCOTT.ORDERS, GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL);
(Save and Exit the file)
GGSCI> add extract exttgt, tranlog, begin now
GGSCI> add exttrail /u01/app/ggs/dirdat/et, extract exttgt, megabytes 100
GGSCI> start exttgt
GGSCI> info allb. Add Data Pump Extract
GGSCI> edit params ptgt
extract ptgt
PASSTHRU
RMTHOST 192.168.1.40, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg_euro/dirdat/et
REPORTCOUNT EVERY 10 MINUTES, RATE
TABLE SCOTT.*;
(Save and Exit the file)
GGSCI> add extract ptgt, exttrailsource /u01/app/ggs/dirdat/et
GGSCI> add rmttrail /u01/app/ggs/dirdat/et, extract ptgt, megabytes 100
GGSCI> start ptgt
GGSCI> info all

11. Add OGG Replicat Process

On Source
GGSCI> edit params rtgt
REPLICAT RTGT
USERID GGS@srcdb, PASSWORD oracle
ASSUMETARGETDEFS
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /u01/app/ggs/dirrpt/rtgt.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
GROUPTRANSOPS 2000
DBOPTIONS SUPPRESSTRIGGERS
MAP SCOTT.*, TARGET SCOTT.*;
(Save and Exit the file)
GGSCI> dblogin userid ggs@srcdb, password oracle
GGSCI> add replicat rtgt, exttrail /u01/app/ggs/dirdat/et
GGSCI> start replicat rtgtOn Target
GGSCI> edit params rsrc
REPLICAT RSRC
USERID GGS@tgtdb, PASSWORD oracle
ASSUMETARGETDEFS
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /u01/app/ggs/dirrpt/rsrc.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
GROUPTRANSOPS 2000
DBOPTIONS SUPPRESSTRIGGERS
MAP SCOTT.*, TARGET SCOTT.*;

(Save and Exit the file)

GGSCI> dblogin userid ggs@tgtdb, password oracle
GGSCI> add replicat rsrc, exttrail /u01/app/ggs/dirdat/es
GGSCI> start replicat rsrca. At this point we have successfully setup the Bi-Directional replication without CDRs.
b. If you can guarantee that there wouldn’t be any LAG or Network issues you can rely on OGG and there is NO need of CDRs. But one can’t guarantee that at all.

12. Setup CRDs

Edit your replicat parameter files on both the sides and add the following stanza.
MAP SCOTT.ORDERS, TARGET SCOTT.ORDERS,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP)),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));NOTE: In our Demo source and target schemas are same. If schema is name is different on source and target you have to make sure that you define the correct mapping on source.

Modify Replicat – Source and Target
On Source:
GGSCI> edit params rtgt
REPLICAT RTGT
USERID GGS@srcdb, PASSWORD oracle
ASSUMETARGETDEFS
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /u01/app/ggs/dirrpt/rtgt.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
GROUPTRANSOPS 2000
DBOPTIONS SUPPRESSTRIGGERS
MAP SCOTT.ORDERS, TARGET SCOTT.ORDERS,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP)),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

(Save and Exit the file)

GGSCI> stop replicat rtgt
GGSCI> start replicat rtgtOn Target:
GGSCI> edit params rsrc
REPLICAT RSRC
USERID GGS@tgtdb, PASSWORD oracle
ASSUMETARGETDEFS
REPORTCOUNT EVERY 10 MINUTES, RATE
DISCARDFILE /u01/app/ggs/dirrpt/rsrc.dsc, APPEND
DISCARDROLLOVER ON SUNDAY
GROUPTRANSOPS 2000
DBOPTIONS SUPPRESSTRIGGERS
MAP SCOTT.ORDERS, TARGET SCOTT.ORDERS,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP)),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

(Save and Exit the file)

GGSCI> stop replicat rsrc
GGSCI> start replicat rsrcAt this point we have successfully setup the Bi-Directional replicat using OGG built-in CDRs.