AWS Database Blog

Implement automatic conflict detection and resolution for Oracle GoldenGate bi-directional replication between Amazon RDS for Oracle databases

Bi-directional replication for Oracle databases is crucial for businesses because it provides high availability and supports disaster recovery. Additionally, it helps with load balancing by distributing the load across multiple databases. Geographical distribution of databases allows data to be closer to users, reducing latency and improving access times. When it’s supported with bi-directional replication across databases in different AWS Regions, it provides high availability.

Oracle GoldenGate can be deployed for bi-directional replication between two Amazon Relational Database Service (Amazon RDS) for Oracle databases across Regions for cross-region disaster recovery or to achieve database proximity to improve the performance of the application that is deployed across Region. However, a potential challenge arises when the same dataset gets updated, deleted, or inserted in two RDS for Oracle databases at the same point in time. This situation will lead to a data conflict, causing data inconsistencies and halting the replication. Another challenge in bi-directional replication is the possibility of a data loop, where changes keep bouncing back and forth between two Oracle databases. Managing these data conflicts and maintaining consistent data replication is a critical process. Oracle GoldenGate offers built-in features to tackle the data inconsistency challenge automatically using automatic conflict detection and resolution (Auto-CDR).

The Auto-CDR feature saves you time and effort by eliminating the need to manually configure conflict error handling parameters. It also reduces the possibility of failure because conflicts are detected and resolved automatically by Oracle GoldenGate.

In this post, we show how to implement Auto-CDR for Oracle GoldenGate bi-directional replication between RDS for Oracle databases.

Oracle GoldenGate Auto-CDR

Oracle GoldenGate’s Auto-CDR feature is available for Oracle Database 12c Release 2 (12.2) and later, and works with Oracle GoldenGate 12c (12.3.0.1) and later releases.

One of the advantages of Auto-CDR is that application changes are not required to handle conflicts because it has the following features:

  • Oracle Database automatically creates and maintains invisible timestamp columns
  • It can insert, update, delete, and use the delete tombstone log table to determine if a row was deleted
  • Large object (LOB) column conflicts can be detected
  • Oracle Database automatically configures supplemental logging on required columns

Oracle GoldenGate’s Auto-CDR offers two methods to handle conflicts in active-active replication environments:

  • Latest timestamp conflict detection and resolution – A row LCR (Logical Change Record) is a structured representation of a DML row change. With latest timestamp conflict detection and resolution, a conflict is detected when the timestamp column of the row LCR doesn’t match the timestamp of the corresponding table row. The row LCR is applied if its timestamp is later. Otherwise, the row LCR is discarded, and the table row is not changed.
  • Delta conflict detection and resolution – The delta resolution method does not depend on a timestamp or an extra resolution column. With delta conflict resolution, the conflict is resolved by adding the difference between the new and old values in the row LCR to the value in the table. This resolution method is generally used for financial data such as an account balance.

We demonstrate Auto-CDR using the latest timestamp conflict detection and resolution method in this post.

Solution overview

In this post, we implement Auto-CDR between two RDS for Oracle databases (Oracle 19c) deployed in the us-east-1 and us-west-1 Regions using Oracle GoldenGate 21c running on Amazon Elastic Compute Cloud (Amazon EC2). Refer to the supported versions of Oracle GoldenGate on Amazon RDS for Oracle for more information.

The following diagram shows the architecture of our solution.

DBblog-3427-GGarch

Prerequisites

Complete the following prerequisites before implementing Oracle GoldenGate in RDS for Oracle databases:

  1. Launch EC2 instances (Amazon Linux 2) in two Regions. In this post, we deploy them in the us-east-1 and us-west-1
  2. Set up a VPC peering connection between VPCs in each Region.
  3. Install the latest Oracle client on both EC2 instances as an Oracle user. Refer to the Oracle client documentation for instructions.
  4. Install Oracle GoldenGate version 21c on each EC2 instance in both Regions as an Oracle user. Refer to Using Oracle GoldenGate with Amazon RDS for Oracle for instructions.
  5. Configure security groups on both Oracle GoldenGate EC2 instances to allow Oracle GoldenGate processes in one Region to communicate with Oracle GoldenGate processes in the other Region.

Refer to Oracle GoldenGate Process and Key Terms for more information. Review Considerations for an Active-Active Configuration when implementing an active-active application.

Set up RDS for Oracle databases in both Regions

Complete the following steps to create an RDS for Oracle database in both us-east-1 and us-west-1 Regions. For more information, see Creating and connecting to an Oracle DB instance.

  1. Set up a source database for use with Oracle GoldenGate on Amazon RDS in each Region
  2. Set up a target database for use with Oracle GoldenGate on Amazon RDS in each Region.
    1. Make sure the TNS_ADMIN variable value is added in .bash_profile for an Oracle user.
    2. In a bi-directional replication setup, grant admin privileges to the Oracle GoldenGate user account so the grant provided by rdsadmin_dbms_goldengate_auth.grant_admin_privilege for capture or apply is not overwritten.

Implement Auto-CDR

In this post, for us-east-1 RDS for Oracle database, we create a GoldenGate user account as oggadm1, and a database user as usestusr, and a STAFF table owned by this user. In the us-west-1 RDS for Oracle database, we create a GoldenGate user account as oggadm1 and a database user uswestusr and a STAFF table owned by this user. The STAFF table will be replicated bi-directional and Auto-CDR will be implemented. Complete the following steps:

Create users and tables in both Regions

  1. Connect to the us-east-1 RDS database and create the user, assign privileges, and create a table: 
    create user usestusr identified by <password>;
    grant connect, resource to usestusr;
    CREATE TABLE usestusr.STAFF 
(	"STAFF_ID" NUMBER, 
	"STAFF_NAME" VARCHAR2(30 BYTE), 
	"DOJ" DATE, 
	"DEPTNO" NUMBER, 
	"JOBCODE" VARCHAR2(30 BYTE), 
	"SAL" NUMBER, 
	CONSTRAINT "STAFFID_PK" PRIMARY KEY ("STAFF_ID"));
  2. Connect to the us-west-1 database and create the user, assign privileges, and create a table: 
    create user uswestusr identified by <password>;
    grant connect, resource to uswestusr;

    CREATE TABLE uswestusr.STAFF
(	"STAFF_ID" NUMBER, 
	"STAFF_NAME" VARCHAR2(30 BYTE), 
	"DOJ" DATE, 
	"DEPTNO" NUMBER, 
	"JOBCODE" VARCHAR2(30 BYTE), 
	"SAL" NUMBER, 
	CONSTRAINT "STAFFID_PK" PRIMARY KEY ("STAFF_ID"));
  3. Run the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package for the table that will be replicated bi-directional between the RDS for Oracle databases in two Regions: 
    exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR (schema_name=> '<your username>', table_name=>'<table for bidirectional>');
  4. Connect to the us-east-1 RDS for Oracle database and run the package: 
    exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR (schema_name=> 'usestusr', table_name=>'STAFF');
  5. Connect to the us-west-1 RDS for Oracle database and run the package for the bi-directional table: 
    exec DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR (schema_name=> 'uswestusr', table_name=> 'STAFF');

After you run the DBMS_GOLDENGATE_ADM package, a hidden timestamp column is added to the table by Oracle GoldenGate, which records the time of a row change. This information is used to detect and resolve conflicts.

The following code shows the DDL for the STAFF table after we ran the DBMS_GOLDENGATE_ADM package on the STAFF table at us-east-1, which has the additional hidden timestamp column CDRTS$ROW:

CREATE TABLE "USESTUSR"."STAFF" 
   (	"STAFF_ID" NUMBER, 
	"STAFF_NAME" VARCHAR2(30 BYTE), 
	"DOJ" DATE, 
	"DEPTNO" NUMBER, 
	"JOBCODE" VARCHAR2(30 BYTE), 
	"SAL" NUMBER, 
	"CDRTS$ROW" TIMESTAMP (6) INVISIBLE DEFAULT SYSTIMESTAMP at TIME ZONE '0:0' NOT NULL ENABLE, 
	 CONSTRAINT "STAFFID_PK" PRIMARY KEY ("STAFF_ID");

Configure bi-directional replication

When configuring bi-directional replication, note the following:

  • MAPINVISIBLECOLUMNS has to be specified in the Replicat parameters file at both the source and target to resolve the invisible timestamp column that was created by the DBMS_GOLDENGATE_ADM package in the STAFF table.
  • Data looping is a critical issue in bi-directional replication. It occurs when changes made on the source system are replicated on the target, which initiates replication back to the source, creating an endless loop of updates. TranLogOptions Excludetag 00 is specified in the Extract parameter file at the source and target for CDR loop detection and resolution.
  • In the GLOBALS parameter file, ALLOWOUTPUTDIR is used to specify the allowed permission for the output trail directory that exists in the remote server.

Configure the Extract process in us-east-1

In the us-east-1 Oracle GoldenGate server, complete the following steps to configure the Extract process:

  1. Create the directories: 
    cd $GG_ORACLE_HOME/dirdat
mkdir rmtuseast
    cd $GG_ORACLE_HOME
mkdir -p data/uswest
  2. Create the Extract parameter files: 
    ./ggsci
GGSCI (<us east host>) 1> edit params mgr
PORT 15000
DYNAMICPORTLIST 8192-8198
    
GGSCI (<us east host>) 3> edit params ./GLOBALS
GGSCHEMA oggadm1
CHECKPOINTTABLE oggadm1.CHECKPOINTABLE
ALLOWOUTPUTDIR dirdat/rmtuseast
    GGSCI (<us east host>) 1> edit params extusest
EXTRACT extusest
USERID oggadm1@ORCL_USEAST, PASSWORD <passwordvalue>
EXTTRAIL dirdat/extusest/la
TranLogOptions Excludetag 00
TABLE USESTUSR.STAFF;
    GGSCI (<us east host>) 2> edit params extestdp
EXTRACT extestdp
USERID oggadm1@ORCL_USEAST, PASSWORD oggadm1
RMTHOST <us west host>,MGRPORT 15000
RMTTRAIL dirdat/rmtuswest/ra
TABLE USESTUSR.STAFF;
  3. Configure the Extract process: 
    GGSCI (<us east host>) 3> start mgr
    GGSCI(<us east host>)4> add extract extusest tranlog, INTEGRATED tranlog, begin now
EXTRACT (Integrated) added.
    GGSCI (<us east host>) 5> add exttrail dirdat/extusest/la extract extusest
EXTTRAIL added.
    GGSCI (<us east host>) 6> dblogin userid oggadm1@ORCL_USEAST
Password:
Successfully logged into database.
    GGSCI (<us east host>) 7> register EXTRACT extusest database

It would show as registered mentioning the SCN #
    GGSCI (<us east host>) 8> ADD EXTRACT extestdp EXTTRAILSOURCE dirdat/extusest/la
EXTRACT added.
    GGSCI (<usesthost>) 9> add trandata usestusr.staff
    GGSCI (<usesthost>) 10> add rmttrail dirdat/rmtuswest/ra extract extestdp
RMTTRAIL added.

Configure the Replicat process in us-west-1

In the us-west-1 Oracle GoldenGate server, complete the following steps to configure the Replicat process:

  1. Create the directories: 
    cd $GG_ORACLE_HOME/dirdat
mkdir rmtuswest
    cd $GG_ORACLE_HOME
mkdir -p data/usest
  2. Create the Replicat parameter files: 
    GGSCI (<us west host>) 1> edit params mgr
PORT 15000
DYNAMICPORTLIST 8192-8198
    GGSCI (<us west host>) 2> edit params ./GLOBALS
GGSCHEMA oggadm1
CHECKPOINTTABLE oggadm1.CHECKPOINTTABLE
ALLOWOUTPUTDIR dirdat/rmtuswest
    GGSCI (<us west host>) 3> edit params repuswst
REPLICAT repuswst
USERID oggadm1@ORCL_USWEST, PASSWORD <passwordvalue>
ASSUMETARGETDEFS
DISCARDFILE /<$GG_ORACLE_HOME>/data/usest/discards, PURGE
MAPINVISIBLECOLUMNS
MAP USESTUSR.STAFF, TARGET USWESTUSR.STAFF;
  3. Configure the Replicat process: 
    GGSCI (<us west host>) 4> dblogin userid oggadm1@ORCL_USWEST
Password:
Successfully logged into database.
    GGSCI (<us west host>) 5> add checkpointtable oggadm1.CHECKPOINTTABLE
    GGSCI (<us west host>) 17> add replicat repuswst integrated exttrail dirdat/rmtuswest/ra

Configure the Extract process in us-west-1

In the us-west-1 Oracle GoldenGate server, complete the following steps to configure the Extract process:

  1. Create the Extract parameter files: 
    ./ggsci
GGSCI (<us west host>) 1> view params mgr
PORT 15000
DYNAMICPORTLIST 8192-8198
    GGSCI (<us west host>l) 2> edit params ./GLOBALS
GGSCHEMA oggadm1
CHECKPOINTTABLE oggadm1.CHECKPOINTTABLE
ALLOWOUTPUTDIR dirdat/rmtuswest
    GGSCI (<us west host>) 3> edit params extuswst
EXTRACT extuswst
USERID oggadm1@ORCL_USWEST, PASSWORD <password value>
EXTTRAIL dirdat/extuswest/la
TranLogOptions Excludetag 00
TABLE USWESTUSR.STAFF;
    GGSCI (<us westhost>) 4> edit params extwstdp
EXTRACT extwstdp
USERID oggadm1@ORCL_USWEST, password <password value>
RMTHOST < us east host >, MGRPORT 15000
RMTTRAIL dirdat/rmtuseast/rt
TABLE USWESTUSR.STAFF;
  2. Configure the Extract process: 
    GGSCI (<us west host>) 1> start mgr
GGSCI (<us west host>) 2> add extract extuswst tranlog, INTEGRATED tranlog, begin now
GGSCI (<us west host>) 4> add exttrail dirdat/extuswest/la extract extuswst
EXTTRAIL added.
    
GGSCI (<us west host>) 5> dblogin userid oggadm1@ORCL_USWEST
Password:
Successfully logged into database.
    GGSCI (<us west host>) 6> register EXTRACT extuswst database

It would show as registered mentioning the SCN #
    GGSCI (<us west host>) 7> ADD EXTRACT extwstdp EXTTRAILSOURCE dirdat/extuswest/la
EXTRACT added.
    GGSCI (<us west host>) 8> add trandata USWESTUSR.STAFF
    GGSCI (<us west host>) 13> add rmttrail dirdat/rmtuseast/rt extract extwstdp
RMTTRAIL added.

Configure the Replicat process in us-east-1

In the us-east-1 Oracle GoldenGate server, complete the following steps to configure the Replicat process:

  1. Create the Replicat parameter file: 
    ./ggsci
GGSCI (<us east host>) 3> edit params repusest
REPLICAT repusest
USERID oggadm1@ORCL_USEAST, PASSWORD <password>
ASSUMETARGETDEFS
DISCARDFILE /<$GG_ORACLE_HOME>/data/uswest/discards, PURGE
MAPINVISIBLECOLUMNS
MAP USWESTUSR.STAFF, TARGET USESTUSR.STAFF;
  2. Configure the Replicat process: 
    GGSCI (<us east host>) 14> dblogin userid oggadm1@ORCL_USEAST
Password:
Successfully logged into database.
    
GGSCI (<us east host>) 16> add checkpointtable oggadm1.CHECKPOINTTABLE
    
GGSCI (<us east host>) 17> add replicat repusest integrated exttrail dirdat/rmtuseast/rt

Run bi-directional replication

Now you’re ready to run the bi-directional replication process. Complete the following steps:

  1. In the us-east-1 Oracle GoldenGate server, start the integrated Extract process: 
    GGSCI (<us east host>) 2> start extract extusest
GGSCI (<us east host>) 7> start EXTRACT extestdp
  2. In the us-west-1 Oracle GoldenGate server, start the integrated Replicat process: 
    GGSCI (<us west host>) 2> start replicat repuswst
  3. In the us-west-1 Oracle GoldenGate server, start the integrated Extract process: 
    GGSCI (<us west host>) 2> start extract extuswst
GGSCI (<us west host>) 7> start EXTRACT extwstdp
  4. In the us-east-1 Oracle GoldenGate server, start the integrated Replicat process: 
    GGSCI (<us east host>) 2> start replicat repusest

Test the bi-directional replication Auto-CDR

To test the bi-directional replication Auto-CDR, we insert, delete, and update a few records in the source and target at the same time using a cron job.

Log in to either the us-east-1 or us-west-1 Oracle GoldenGate server and create two scripts, one for running SQL in the us-east-1 RDS for Oracle database and one for running SQL in the us-west-1 RDS for Oracle database.

  1. Create a file named txn_gg_usest.sh to run in the us-east-1 RDS for Oracle database: 
    #!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u02/instantclient_19_19
export PATH=/home/oracle/.local/bin:/home/oracle/bin:/u02/instantclient_19_19:/u03/oracle/oragg:/u02/instantclient_19_19/bin:/u02/instantclient_19_19:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
export PATH=$PATH:$ORACLE_HOME/bin
/u02/instantclient_19_19/sqlplus -s admin/<password value>@ORCL_USEAST<<EOF
insert into usestusr.staff values (73,'Nancy',sysdate-300,6001,'EM',99500);
insert into usestusr.staff values (74,'Bryan',sysdate-200,6002,'PM',85500);
insert into usestusr.staff values (75,'Mani',sysdate-30,6001,'TL',43500);
insert into usestusr.staff values (76,'Murphy',sysdate-600,6002,'PM',73500);
insert into usestusr.staff values (77,'Rachel',sysdate-420,6001,'TL',55500);
insert into usestusr.staff values (78,'Ramki',sysdate-500,6002,'TL',42500);
insert into usestusr.staff values (79,'King',sysdate-100,6001,'EM',82500);
delete from usestusr.staff where staff_id=1;
update usestusr.staff set deptno=105 where staff_id=3;
commit;
EOF
  2. Create a file named txn_gg_uswest.sh to run in the us-west-1 RDS for Oracle database: 
    #!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u02/instantclient_19_19
export PATH=/home/oracle/.local/bin:/home/oracle/bin:/u02/instantclient_19_19:/u03/oracle/oragg:/u02/instantclient_19_19/bin:/u02/instantclient_19_19:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
export PATH=$PATH:$ORACLE_HOME/bin
/u02/instantclient_19_19/sqlplus -s admin/<password value>@ORCL_USWEST<<EOF
insert into uswestusr.staff values (73,'Nancy',sysdate-300,6001,'EM',89500);
insert into uswestusr.staff values (74,'Bryan',sysdate-200,7002,'PM',85500);
insert into uswestusr.staff values (75,'Mani',sysdate-30,6001,'TL',43500);
insert into uswestusr.staff values (76,'Murphy',sysdate-600,6002,'PM',73500);
insert into uswestusr.staff values (77,'Rachel',sysdate-420,6001,'TL',55500);
insert into uswestusr.staff values (78,'Ramki',sysdate-500,6002,'TL',42500);
insert into uswestusr.staff values (79,'King',sysdate-100,6001,'EM',82500);
delete from uswestusr.staff where staff_id=1;
update uswestusr.staff set deptno=106 where staff_id=3;
commit;
EOF
  3. Now you can edit the cron job and run both scripts at the same time: 
    $ crontab -l
22 15 * * * /home/oracle/txn_gg_usest.sh
22 15 * * * /home/oracle/txn_gg_uswest.sh

Bi-directional replication for the same set of primary key IDs (staff_id) is run in the us-east-1 and us-west-1 RDS for Oracle databases at the same time. We insert statements with the same primary key and different set of values, where staff_id is 73 and 74. Then we delete the statement for the same primary key where staff_id=1, and update the statement with a different set of values for same primary key where staff_id=3.

However, Auto-CDR has resolved the conflicts and committed only one set of changes in both the us-east-1 and us-west-1 RDS for Oracle databases depending on the latest changes as compared with the hidden timestamp column, which records the time of a row change, and this information is used to detect and resolve conflicts.

The following screenshot shows the results from the us-east-1 RDS for Oracle database.

dbblog-3427-sql1

The following screenshot shows the results from the us-west-1 RDS for Oracle database.

dbblog-3427-sql2

For INSERT, UPDATE, DELETE statements, a conflict is detected. Auto-CDR has resolved the data conflict and persisted the data based on the timestamp. We can check the number of conflicts that were resolved by issuing stats <replicatname> latest reportcdr.

The following screenshot shows the statistics of CDR conflicts and resolution.

dbblog-3427-ggstats

 

Monitor Oracle GoldenGate using Amazon CloudWatch

You can implement Amazon CloudWatch monitoring for Oracle GoldenGate error logs and set alarms to send notifications for specific events such as STOP or ABEND, so you can take appropriate actions to resume replication quickly. For more details, see Monitor Oracle GoldenGate logs by using Amazon CloudWatch.

Clean up

To clean up your resources, complete the following steps:

  1. Stop the Extract and Replicat parameters in the us-east-1 and us-west-1 Oracle GoldenGate servers.
  2. Delete the RDS for Oracle databases in the source and target.
  3. Delete the VPC peering connection.
  4. Delete the Oracle GoldenGate EC2 instances in the source and target.
  5. Delete Security Groups.

Conclusion

In this post, we showed how to set up Oracle GoldenGate bi-directional replication with automatic data conflict detection and resolution between source and target RDS for Oracle databases.

For more information on Oracle GoldenGate replication with high availability, see Implement Oracle GoldenGate high availability in the AWS Cloud.

As always, we welcome your feedback. If you have any comments or questions on this post, share them in the comments section.

About the authors

Chithra Krishnamurthy serves as a Database Consultant in the Professional Services team at AWS. She specializes in migrating databases to AWS and implementing bi-directional replication. Chithra’s expertise helps enterprise customers achieve their business goals through efficient database management and optimization. Outside of work, she actively participates in book clubs and explores new travel destinations.

Arun Shanmugam is a Senior Analytics GTM Specialist Solutions Architect at AWS, with a focus on data and analytics. He has been successfully delivering scalable data analytics solutions for customers across diverse industries. Outside of work, Arun is an avid outdoor enthusiast who actively engages in CrossFit, road biking, and cricket.