Troubleshooting GoldenGate Bidirectional (Active-Active) Replication 1 - Conflict Resolution by Using Odd and Even Sequences

For Part 2 click here: Troubleshooting GoldenGate Bidirectional (Active-Active) Replication 2 - Configuration of Macros and Exceptions Handling Routines

The following is an example of testing and troubleshooting Oracle Goldengate bidirectional active-active replication using odd and even sequences in Oracle 11gR2. For uni-directional replication configuration examples please visit homepage

We have two servers:

In bidirectional GoldenGate configuration changes made to either database replicate to the other database in real time. The process works like this:

esource1 (extract on source) -> psource1 (datapump on source) -> rsource1 (replicat on target)
etarget1 (extract on target) -> ptarget1 (datapump on target) -> rtarget1 (replicat on source)

GoldenGate does not replicate Oracle database sequences values in an active-active environment. The sequences must insert values on the target database from its own sequences. Therefore, to ensure that the source and destination sequences are unique, it is recommended to generate odd and even values to each system.

Lets create these processes and test our replication (please keep in mind we will have to change some of the parameters later on).

Now lets start all these created Goldengate processes on both servers and test this configuration.

To test replication we will create table CUSTOMERS on source and target, then create a sequence with odd CUSTOMER_ID on source and even on target.

SQL> CREATE TABLE
CUSTOMERS
(CUSTOMER_ID NUMBER PRIMARY KEY
,CUSTOMER_NAME VARCHAR2(30) NOT NULL
,CUSTOMER_REGISTRATION DATE
,CURRDATE DATE);

Table created.

 SQL> CREATE SEQUENCE
CUSTOMER_ID_SEQ
INCREMENT BY 2
START WITH 2
NOMAXVALUE
NOMINVALUE
NOCYCLE
CACHE 500;

Sequence created.

This is an even sequence. It will generate numbers like 2, 4, 6, 8 and so on. Odd CUSTOMER_ID will be replicated from target only to avoid collision.

Now lets insert a hundred rows on source and see if they are replicated to target.

SQL> INSERT INTO CUSTOMERS SELECT CUSTOMER_ID_SEQ.NEXTVAL,
DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)),
(SYSDATE-1825)+DBMS_RANDOM.VALUE(1,1820),
CURRENT_TIMESTAMP FROM DUAL CONNECT BY LEVEL <= 100;

100 rows created.

SQL> commit;

Commit complete.


SQL> select count(*) from customers;

COUNT(*)
----------
100

On target - these 100 rows replicated OK.

SQL> select count(*) from customers;

COUNT(*)
----------
100

So, we know that uni-directional replication from source to target works because we just inserted a hundred rows into source and they were copied to target.

Now we will create an odd sequence and run the same insert statement on target. Number of rows should double in both databases (inserted by hand in target and then replicated by GG to source).

Lets create an odd sequence (1,3,5,7,9, etc.) and insert another 100 rows with odd values, so they should be created OK and replicated BACK to source:

SQL> CREATE SEQUENCE
CUSTOMER_ID_SEQ
INCREMENT BY 2
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
CACHE 500;

Sequence created.

SQL> INSERT INTO CUSTOMERS SELECT CUSTOMER_ID_SEQ.NEXTVAL,
DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)),
(SYSDATE-1825)+DBMS_RANDOM.VALUE(1,1820),
CURRENT_TIMESTAMP FROM DUAL CONNECT BY LEVEL <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from customers;

COUNT(*)
----------
200

However, even though source has even customer_id and target has odd customer_id, the replicat that runs on source abends without inserting the new 100 rows from target:

GoldenGate error log reports:

2011-11-03 20:12:21 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: OCI Error ORA-00001: unique
constraint (SOURCE.SYS_C0011221) violated (status = 1), SQL <INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SOURCE"."CUSTOMERS" ("CUSTOMER_ID","CUSTOMER_NAME","CUSTOMER_REGISTRATION","CURRDATE") VALUES (:a0,:a1,:a2,:a3)>.
2011-11-03 20:12:21 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: Aborted grouped transaction
on 'SOURCE.CUSTOMERS', Database error 1 (OCI Error ORA-00001: unique constraint (SOURCE.SYS_C0011221) violated (status =
1), SQL <INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SOURCE"."CUSTOMERS" ("CUSTOMER_ID","CUSTOMER_NAME","CUSTOMER_REGISTRATION","CURRDATE") VALUES (:a0,:a1,:a2,:a3)>).
2011-11-03 20:12:21 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: Repositioning to rba 163624
in seqno 1.
2011-11-03 20:12:21 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: SQL error 1 mapping SOURCE.
CUSTOMERS to SOURCE.CUSTOMERS OCI Error ORA-00001: unique constraint (SOURCE.SYS_C0011221) violated (status = 1), SQL <IN
SERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SOURCE"."CUSTOMERS" ("CUSTOMER_ID","CUSTOMER_NAME","CUSTOMER_REGISTRATION","CURRDATE") VALUES (:a0,:a1,:a2,:a3)>.
2011-11-03 20:12:21 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: Repositioning to rba 163624
in seqno 1.
2011-11-03 20:12:21 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: Error mapping from SOURCE.CUSTOMERS to SOURCE.CUSTOMERS.
2011-11-03 20:12:21 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rtarget1.prm: PROCESS ABENDING.

So, what is the problem?

There is no conflict between unique values in SOURCE.CUSTOMERS on source or destination. Yet GoldenGate abends the replicat on Source server with a unique constraint violation.

Metalink states that TRANLOGOPTIONS EXCLUDEUSER <repuser> needs to be added to extract param files.

From the docs:

Oracle (Oracle 10g and later) Do either of the following to specify the Replicat database user.
All transactions generated by this user will be excluded from being captured. This information is available to Extract in the transaction record.
● Identify the Replicat database user by name with the following parameter statement in the Extract parameter file.
TRANLOGOPTIONS EXCLUDEUSER <user name>
● Identify the Replicat database user by its numeric Oracle user-id (uid) with the
following parameter statement in the Extract parameter file.
TRANLOGOPTIONS EXCLUDEUSERID <user-id>

http://download.oracle.com/docs/cd/E22355_01/doc.11111/e21512.pdf
Oracle® GoldenGate
Windows and UNIX Reference Guide
11g Release 1 Patch Set 1 (11.1.1.1)
E21512-02
page 39
TRANLOGOPTIONS EXCLUDEUSER
Typically, this option is used to identify Replicat transactions in a bi-directional or cascading processing configuration, for the purpose of excluding or capturing them. However, it can be used to identify transactions by any other user, such as those of a specific business application.

So, in an effort to resolve abending replicat on source, this parameter is added to both extract param files:

--Redo log options--
TRANLOGOPTIONS EXCLUDEUSER ggate

Now, lets refresh the processes and restart the abended replicat on source.

GGSCI (source) 7> info RTARGET1 showch

GGSCI (source) 16> start replicat RTARGET1, aftercsn 1161218

Sending START request to MANAGER ...
REPLICAT RTARGET1 starting


GGSCI (source) 17> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ESOURCE1 00:00:00 00:00:08
EXTRACT RUNNING PSOURCE1 00:00:00 00:00:08
REPLICAT RUNNING RTARGET1 00:00:00 00:00:06


Now, lets clean up customers table


On source:

SQL> delete from customers;

100 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from customers;

COUNT(*)
----------
0


Lets check if odd and even sequences are in order:

Source:

SQL> SELECT CUSTOMER_ID_SEQ.NEXTVAL from dual;

NEXTVAL
----------
404

SQL>


Target:

SQL> delete from customers;

200 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from customers;

COUNT(*)
----------
0

SQL> SELECT CUSTOMER_ID_SEQ.NEXTVAL from dual;

NEXTVAL
----------
601

SQL>



Now lets test corrected bi-directional replication:

On source:

SQL> INSERT INTO CUSTOMERS SELECT CUSTOMER_ID_SEQ.NEXTVAL,
DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)),
(SYSDATE-1825)+DBMS_RANDOM.VALUE(1,1820),
CURRENT_TIMESTAMP FROM DUAL CONNECT BY LEVEL <= 100;

100 rows created.

SQL> commit;

Commit complete.

On target:

SQL> select count(*) from customers;

COUNT(*)
----------
100

SQL>


On target:

SQL> INSERT INTO CUSTOMERS SELECT CUSTOMER_ID_SEQ.NEXTVAL,
DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)),
(SYSDATE-1825)+DBMS_RANDOM.VALUE(1,1820),
CURRENT_TIMESTAMP FROM DUAL CONNECT BY LEVEL <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from customers;

COUNT(*)
----------
200

These are 100 rows we just inserted on target on top of 100 rows replicated from source by GoldenGate.

On target:

SQL> select count(*) from customers;

COUNT(*)
----------
200


There are no loops (continuing re-insertion of 100 rows) and inserting 100 rows on either target or source replicates them to the second instance only once.
Both odd and even CUSTOMER_IDs can be found on source and dest and they are identical:

We now successfully configured and tested bidirectional replication in Oracle GoldenGate

These are corrected param files for bidirectional replication with the exclusion clause, the rest of parameters can be found in table above:

SOURCE:

+++++++++++++++++++++++++++++

---------------------------------------------------------------------
-- Local extract ESOURCE1 for Source schhema --
-- Updated on 2011 Nov 18 by VG --
---------------------------------------------------------------------
--extract group--
extract ESOURCE1
--export sid
setenv (ORACLE_SID=source)
--connection to database--
userid ggate, password oracle
--reporting--
ReportCount Every 30 Minutes, Rate
Report at 01:00
ReportRollover at 01:15
--discard--
DiscardFile dirrpt/DRPROD1.dis, Append
DiscardRollover at 02:00 ON SUNDAY
--Bidirectional params--
--GETAPPLOPS--
--IGNOREREPLICATES--
--Redo log options--
TRANLOGOPTIONS EXCLUDEUSER ggate COMPLETEARCHIVEDLOGTIMEOUT 600
--bound recovery--
BR BROFF
--path and name for local destination trrail--
exttrail /opt/oracle/gg/dirdat/c1
--DDL support--
ddl include mapped objname source.*
--DML support--
table source.*;
+++++++++++++++++++++++++++++

TARGET

+++++++++++++++++++++++++++++

---------------------------------------------------------------------
-- Local extract etarget1 for SOURCE on TARGET db --
-- Updated on 2011 Nov 18 by VG --
---------------------------------------------------------------------
--extract group--
extract ETARGET1
--export sid
setenv (ORACLE_SID=target)
--connection to database--
userid ggate, password oracle
--reporting--
ReportCount Every 30 Minutes, Rate
Report at 01:00
ReportRollover at 01:15
--discard--
DiscardFile dirrpt/DRPROD1.dis, Append
DiscardRollover at 02:00 ON SUNDAY
--Redo log options--
TRANLOGOPTIONS EXCLUDEUSER ggate COMPLETEARCHIVEDLOGTIMEOUT 600
--bidirectional parameters--
--GETAPPLOPS--
--IGNOREREPLICATES--
--bound recovery--
BR BROFF
--path and name for local destination trrail--
exttrail /opt/oracle/gg/dirdat/t1
--DDL support--
ddl include mapped objname source.*
--DML support--
table source.*;

++++++++++++++++++++++++++++++++++++

More demos:

- Test your knowledge of Oracle GoldenGate
- >Oracle GoldenGate Instantiation with DataPump
- Oracle GoldenGate Data Flow (Architecture)
- Oracle GoldenGate: Install GoldenGate on RedHat Linux
- Oraacle GoldenGate: Configure Uni-Directional Replication
- Oraclle GoldenGate: Test Uni-Directional Replication

Copyright © 2012 Vladimir Grigorian. All Rights Reserved