AWS Database Blog

Implement Oracle GoldenGate bidirectional replication between Amazon RDS for Oracle databases

In this post, we test Oracle GoldenGate (OGG) bidirectional replication between two Amazon Relational Database Service (Amazon RDS) for Oracle instances. This can allow mission-critical applications to be highly available across Regions and provide data redundancy and resilience.

Active-active replication allows multiple database instances with the same application tables to support read/write operations running independently with changes synchronized between them.

A common scenario for active-active database replication is to deploy a single application across the globe to improve the performance of the database across different Regions. In this case, the user queries are routed to the nearest Region, and any data changes are asynchronously replicated to the other Regions by the database layer, ensuring both visibility of these changes globally and acting as high availability and disaster recovery copies in the case of outages. Active-active replication is a core component of Oracle’s platinum Maximum Availability Architecture definition, which can be used to achieve zero downtime and zero data loss.

OGG is a solution that allows you to replicate, filter, and transform data from one database to another database. Due to their existing OGG licenses or DBA expertise, many AWS customers are looking to use their existing OGG software and knowledge to augment and enhance their AWS database deployments.

This post covers the concepts and methodology to set up multiple RDS for Oracle instances and use OGG bidirectional replication to synchronize databases either within a single Region or across multiple Regions according your requirements. This can allow mission-critical applications to be highly available across Regions and provide data redundancy and resilience.

Solution overview

The following diagram shows the architecture for our solution.

The architecture consists of the following components:

  • An instance type. The one you choose depends on your workloads. In this post, we use us-west-1 and us-east-2 Regions.
  • OGG running on Amazon Elastic Compute Cloud (Amazon EC2) instances in the same Region as the databases.
  • An established VPC peering between two VPCs.
  • The establishment of Amazon Route 53, which connects users to the application.

Prerequisites

To follow along, complete the following prerequisites:

  1. Create two EC2 instances (Amazon Linux 2) configured with OGG in the source and target database Regions.
  2. Set up a VPC peering connection between VPCs in each Region.
  3. Install the latest Oracle client on both EC2 instances. For instructions, refer to the Oracle client documentation.
  4. Install OGG version 19c or above on both EC2 instances. For instructions, refer to Setting up Oracle GoldenGate.
  5. Configure a security group to allow OGG processes in one Region to communicate with OGG processes in the other Regions. For instructions on installing OGG in a high availability configuration, see Implement Oracle GoldenGate high availability in the AWS Cloud.
  6. Set up an Amazon Simple Storage Service (Amazon S3) bucket in the source Region and enable cross-Region replication to the target Region.
  7. Create the AWS Identity and Access Management (IAM) role RDSS3IntegrationRole with PutObject, GetObject, and ListBucket access to Region-specific S3 buckets.
  8. Oracle GoldenGate and some RDS for Oracle environments on AWS require the customer to provide their own license. For more information, refer to RDS for Oracle licensing options.

Review Considerations for an Active-Active Configuration when implementing an active-active application.

Set up RDS for Oracle databases in both Regions

Complete the followings steps to set up your databases:

  1. Create an RDS for Oracle database in each Region. For instructions on creating database instances, see Creating an Oracle DB instance and connecting to a database on an Oracle DB instance.
  2. Set up a source database for use with GoldenGate on Amazon RDS.
  3. Set up a target database for use with GoldenGate on Amazon RDS.
  4. Add the S3_INTEGRATION option to the RDS for Oracle database in the source and target Regions for the initial load using the Oracle Data Pump expdp/impdp process.
  5. Assign the RDSS3IntegrationRole IAM role to the RDS for Oracle instance in the source and target Regions for the initial load of tables using expdp/impdp.

Configure OGG on the source database

In this section, we describe the tasks to configure OGG for the source database:

  1. Log in to the Amazon EC2 host where OGG is installed.
  2. Set the following environment variables in .bash_profile (Note: Change ORACLE_BASE, ORACLE_HOME and OGG_HOME as per your standards.):
    # Oracle Settings
    export TMP=/tmp
    export TMPDIR=$TMP
    
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/19.3.0.0/db_1
    export PATH=$ORACLE_HOME/bin:$PATH
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
    export OGG_HOME=/u01/app/ogg
    
    alias cdg='cd $OGG_HOME'
    
  3. Start the GoldenGate command line interface (ggsci) and run the following commands to create the necessary subdirectories:
    cdg
    ./ggsci
    
    GGSCI> create subdirs
  4. Create a manager parameter file. Select an unused port for the manager process:
    GGSCI> edit param mgr
    
    port 7810
    purgeoldextracts dirdat/*, usecheckpoints, minkeepdays 2
    
    lagcriticalhours 1
    laginfominutes 30
    lagreportminutes 15
    downreportminutes 30
    
    autostart er *
    autorestart er *, retries 5, waitminutes 2, resetminutes 10
    
    # start the manager process
    GGSCI >start mgr

    To learn more about the parameters, refer to Summary of Manager Parameters.

  5. Create the OGG wallet file to store encryption keys:
    GGSCI> create wallet
    
    Created wallet.
    
    Opened wallet.
    
    GGSCI> add masterkey
    
    GGSCI> info masterkey
    Master encryption key name: OGG_DEFAULT_MASTERKEY
    
    Version         Creation Date                   Status
    1               2022-08-31T20:52:20.000+00:00   Current
  6. Create credentialstore for securely storing database user passwords:
    GGSCI> add credentialstore
    
    GGSCI> alter credentialstore add user ggs_admin@west_orcl password xxxxx alias west_orcl
    
    Credential store altered.
    
    GGSCI> info credentialstore
    
    Reading from credential store:
    
    Default domain: OracleGoldenGate
    
      Alias: west_orcl
      Userid: ggs_admin@west_orcl
    
    GGSCI> dblogin USERIDALIAS west_orcl
    
    Successfully logged into database.
  7. Add a heartbeat and checkpoint table:
    GGSCI > add heartbeattable
    
    GGSCI > add checkpointtable ggs_admin.checkpoint
  8. Enable table-level supplemental logging on the schema:
    GGSCI> ADD SCHEMATRANDATA dms_sample
  9. Create an extract group to fetch incremental changes. The extract group name can be eight characters long and needs to be unique across a single configuration.The DMS_SAMPLE schema is a sample database for sporting event ticketing application. We’re using some of the tables in the application for demonstration purposes.
    GGSCI> edit param esrc
    
    setenv (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/db_1")
    setenv (TNS_ADMIN="/u01/app/oracle/product/19.3.0.0/db_1/network/admin")
    extract esrc
    useridalias west_orcl
    tranlogoptions integratedparams (max_sga_size 1024)
    tranlogoptions excludeuser ggs_admin
    exttrail dirdat/es
    cachemgr cachesize 1gb, cachedirectory ./dirtmp
    
    table dms_sample.seat;
    table dms_sample.person;
    table dms_sample.player;
    table dms_sample.sporting_event &
          getbeforecols(on update all , on delete all );
    table dms_sample.sport_location;
    table dms_sample.sport_team;
    table dms_sample.seat_type;
    table dms_sample.sport_type;
    table dms_sample.ticket_purchase_hist &
            getbeforecols(on update all , on delete all );

    To learn more about OGG bidirectional replication for Oracle, see Configuring Oracle GoldenGate for Active-Active Configuration. To prevent data looping, we set tranlogoptions excludeuser ggs_admin in the extract parameter file for the extract process to be aware of the Replicat database user and exclude its transactions.

  10. Before starting the OGG extract, you must register it with the corresponding database:
    GGSCI> register extract esrc database
    
    # Create extract
    GGSCI> add extract esrc tranlog, integrated tranlog, begin now
    
    # Create Local Trail File
    GGSCI> add exttrail dirdat/es extract esrc, megabytes 10
  11. Configure the OGG Pump process to send the extracted trail files to the target EC2 instance. We have decoupled the Extract and Pump processes to allow the extract to continue to pull changes even if there is a network issue between the source and target Regions. To create the Data Pump file, specify the target EC2 OGG hub instance host name/IP in the RMTHOST section:
    GGSCI> edit param psrc
    
    extract psrc
    useridalias west_orcl
    discardfile dirrpt/psrc.dsc, append, megabytes 10
    
    rmthost <gg-remote-host>, mgrport 7810, timeout 30
    rmttrail dirdat/es
    passthru
    
    table dms_sample.*;
    
    # Create extract pump
    GGSCI> add extract psrc, exttrailsource dirdat/es
    
    # Create remote trail file location
    GGSCI> add rmttrail dirdat/es, extract psrc
  12. Start the Extract and Data Pump processes on the source database:
    # Start Extract
    GGSCI> start esrc
    
    # Start Extract Pump
    GGSCI> start psrc
  13. Check the status of the processes:
    GGSCI> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING
    EXTRACT     RUNNING     ESRC        00:00:00      00:00:02
    EXTRACT     RUNNING     PSRC        00:00:00      00:00:05

Perform the initial data load from the source database to the target database

For OGG versions 12.2 and above, the FLASHBACK_SCN parameter is no longer required when the Replicat parameter DBOPTIONS ENABLE_INSTANTIATION_FILTERING is enabled. Data Pump and OGG have a tighter integration. The CSN for each table is captured on a Data Pump export. The CSN is then applied to system tables and views on the target database on an import. These views and system tables are referenced by Replicat when applying data to the target database.

Make sure there is sufficient undo space available to run through the export. If the production system is very busy and there is no disk space available to add any undo space, but you can configure a duplicate system, then you can use that duplicate system to run a transactionally consistent export. (Assuming the duplicate system will be idle, there’s no need to use FLASHBACK_SCN.)

During this initial load process, the source database (where all users are connected) can remain open for read/write activity. However, while we configure the bidirectional replication, users should not connect to the target database until all records are synchronized.

  1. Export tables to be set up for active-active replication:
    expdp admin@west_orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=filename_%U.dmp LOGFILE=schema_expdp.log schemas=dms_sample
    Validate export datapump log file for any errors.
    # Check instantiated scn
    sqlplus admin@west_orcl
    SQL> select table_name, scn from dba_capture_prepared_tables where table_owner = 'DMS_SAMPLE';

    Until the first export of the tables, dba_capture_prepared_tables doesn’t get populated. The system change number (SCN) is the smallest SCN for which the table can be instantiated. It isn’t the export SCN.

  2. Copy the file from DATA_PUMP_DIR to Amazon S3:
    SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
          p_bucket_name    =>  ‘<bucket_name>',
          p_prefix         =>  '',
          p_s3_prefix      =>  '',
          p_directory_name =>  'DATA_PUMP_DIR')
       AS TASK_ID FROM DUAL;
  3. Make sure that the upload to the S3 bucket is successful:
    aws s3 ls s3://<bucket_name> –recursive –human-readable –summarize
  4. When Amazon S3 replicates the data from the source to target Region, copy the Data Pump files from Amazon S3:
     S3 to DATA_PUMP_DIR:
    # Copy file from S3 in target region to DATA_PUMP_DIR
    SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
        p_bucket_name => ‘<bucket_name>',
        p_s3_prefix => '',
        p_directory_name => 'DATA_PUMP_DIR')
    AS TASK_ID FROM DUAL;
  5. Make sure that the download from the S3 bucket is successful:
    SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'PRODUCT_DESCRIPTIONS'));
  6. Import data in the target Region:
    impdp admin@east_orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=filename_%U.dmp LOGFILE=schema_impdp.log schemas=dms_sample
  7. Check the import Data Pump log file for any errors.

Configure OGG on the target database

Repeat the steps to configure OGG on the source database on the target EC2 instance. Make sure to change the following configurations in the target OGG hub instance:

  • Use TGT instead of SRC as the suffix in the OGG process names
  • Point OGG to the Region-specific database instance

Set up Replicat in target database

To set up Replicat in the target database, use the following code:

  1. Log in to the Amazon EC2 host where OGG is installed.
  2. Set the following environment variables in .bash_profile (Note: Change ORACLE_BASE, ORACLE_HOME and OGG_HOME as per your standards.):
    # Oracle Settings
    export TMP=/tmp
    export TMPDIR=$TMP
    
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/19.3.0.0/db_1
    export PATH=$ORACLE_HOME/bin:$PATH
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
    export OGG_HOME=/u01/app/ogg
    
    alias cdg='cd $OGG_HOME'
    
  3. Start the GoldenGate command line interface (ggsci):
    cdg
    ./ggsci
  4. Set up the Replicat file on the target:
    edit param rtgt
    
    setenv (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/db_1")
    setenv (TNS_ADMIN="/u01/app/oracle/product/19.3.0.0/db_1/network/admin")
    replicat rtgt
    useridalias east_orcl
    discardfile dirrpt/rtgt.dsc, append, megabytes 10
    reportcount every 30 minutes, rate
    reportrollover at 00:01 on sunday
    discardrollover at 00:01 on sunday
    
    batchsql
    
    dboptions enable_instantiation_filtering
    
    -- include ddl for mapped objects only
    ddl include mapped;
    ddloptions report
    --- Update metadata after DDL changes have been applied to source 
    ddloptions updatemetadata
    
    map dms_sample.seat, target dms_sample.seat;
    map dms_sample.person, target dms_sample.person;
    map dms_sample.player, target dms_sample.player;
    map dms_sample.sporting_event , target dms_sample.sporting_event, &
    comparecols (on update keyincluding (start_date_time), on delete keyincluding (start_date_time)), &
    resolveconflict (updaterowexists, (default, usemax (start_date_time))), &
    resolveconflict (insertrowexists, (default, usemax (start_date_time))), &
    resolveconflict (deleterowexists, (default, ignore)), &
    resolveconflict (updaterowmissing, (default, overwrite)), &
    resolveconflict (deleterowmissing, (default, discard));
    map dms_sample.sport_location, target dms_sample.sport_location;
    map dms_sample.sport_team, target dms_sample.sport_team;
    map dms_sample.seat_type, target dms_sample.seat_type;
    map dms_sample.sport_type, target dms_sample.sport_type;
    map dms_sample.ticket_purchase_hist , target dms_sample.ticket_purchase_hist, &
    comparecols (on update keyincluding (transaction_date_time), on delete keyincluding (transaction_date_time)), &
    resolveconflict (updaterowexists, (default, usemax (transaction_date_time))), &
    resolveconflict (insertrowexists, (default, usemax (transaction_date_time))), &
    resolveconflict (deleterowexists, (default, ignore)), &
    resolveconflict (updaterowmissing, (default, overwrite)), &
    resolveconflict (deleterowmissing, (default, discard));
  5. Add Replicat on the target:
    GGSCI> add replicat rtgt, exttrail dirdat/es, checkpointtable ggs_admin.checkpoint
  6. Start Replicat on the target:
    GGSCI> start replicat rtgt
  7. Check the status:
    GGSCI (ip-10-5-3-135.us-east-2.compute.internal) 4> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING
    EXTRACT     RUNNING     ETGT        00:00:00      00:00:01
    EXTRACT     RUNNING     PTGT        00:00:00      00:00:02
    REPLICAT    RUNNING     RTGT        00:00:00      00:00:06

Set up Replicat in the source database

Use the following code to set up Replicat in the source database:

  1. Log in to the Amazon EC2 host where OGG is installed.
  2. Set the following environment variables in .bash_profile (Note: Change ORACLE_BASE, ORACLE_HOME and OGG_HOME as per your standards.):
    # Oracle Settings
    export TMP=/tmp
    export TMPDIR=$TMP
    
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/19.3.0.0/db_1
    export PATH=$ORACLE_HOME/bin:$PATH
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
    export OGG_HOME=/u01/app/ogg
    
    alias cdg='cd $OGG_HOME'
    
  3. Start the GoldenGate command line interface (ggsci):
    Setup replicat file on source
    cdg
    ./ggsci
  4. Set up the Replicat file on the source:
    edit param rsrc
    
    setenv (ORACLE_HOME=”/u01/app/oracle/product/19.3.0.0/db_1”)
    setenv (TNS_ADMIN=”/u01/app/oracle/product/19.3.0.0/db_1/network/admin”)
    replicat rsrc
    useridalias west_orcl
    discardfile dirrpt/rsrc.dsc, append, megabytes 10
    reportcount every 30 minutes, rate
    reportrollover at 00:01 on Sunday
    discardrollover at 00:01 on Sunday
    
    dboptions enable_instantiation_filtering
    
    -- include ddl for mapped objects only
    ddl include mapped;
    ddloptions report
    --- Update metadata after DDL changes have been applied to source 
    ddloptions updatemetadata
    
    map dms_sample.seat, target dms_sample.seat;
    map dms_sample.person, target dms_sample.person;
    map dms_sample.player, target dms_sample.player;
    map dms_sample.sporting_event , target dms_sample.sporting_event, &
    comparecols (on update keyincluding (start_date_time), on delete keyincluding (start_date_time)), &
    resolveconflict (updaterowexists, (default, usemax (start_date_time))), &
    resolveconflict (insertrowexists, (default, usemax (start_date_time))), &
    resolveconflict (deleterowexists, (default, ignore)), &
    resolveconflict (updaterowmissing, (default, overwrite)), &
    resolveconflict (deleterowmissing, (default, discard));
    map dms_sample.sport_location, target dms_sample.sport_location;
    map dms_sample.sport_team, target dms_sample.sport_team;
    map dms_sample.seat_type, target dms_sample.seat_type;
    map dms_sample.sport_type, target dms_sample.sport_type;
    map dms_sample.ticket_purchase_hist , target dms_sample.ticket_purchase_hist, &
    comparecols (on update keyincluding (transaction_date_time), on delete keyincluding (transaction_date_time)), &
    resolveconflict (updaterowexists, (default, usemax (transaction_date_time))), &
    resolveconflict (insertrowexists, (default, usemax (transaction_date_time))), &
    resolveconflict (deleterowexists, (default, ignore)), &
    resolveconflict (updaterowmissing, (default, overwrite)), &
    resolveconflict (deleterowmissing, (default, discard)); resolveconflict (deleterowexists, (default, ignore)), &
    resolveconflict (updaterowmissing, (default, overwrite)), &
    resolveconflict (deleterowmissing, (default, discard));
  5. Add Replicat on the source:
    GGSCI> add replicat rsrc, exttrail dirdat/et, checkpointtable ggs_admin.checkpoint
  6. Start Replicat on the source:
    GGSCI> start replicat rsrc
  7. Check the status:
    GGSCI (ip-10-10-5-109.us-west-1.compute.internal) 4> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING
    EXTRACT     RUNNING     ESRC        00:00:00      00:00:01
    EXTRACT     RUNNING     PSRC        00:00:00      00:00:05
    REPLICAT    RUNNING     RSRC        00:00:00      00:00:06

To enable conflict detection and resolution, you need to make a few changes to the Extract and Replicat configuration.

Extract changes

Edit the extract parameter file and set the GetBeforeCols option of the extract table parameter to specify columns for which you want the extract to capture the before image of an update. In the following code, we tell OGG to extract a before image for all columns on update:

table dms_sample.sporting_event &
        getbeforecols(on update all , on delete all );

Replicat changes

We add the COMPARECOLS option to specify which columns are used to detect updates and delete conflicts. We use the RESOLVECONFLICT option to specify the conflict resolution method. In the following example, OGG persists the record with the latest transaction_date_time value:

map dms_sample.sporting_event , target dms_sample.sporting_event, &
comparecols (on update keyincluding (start_date_time), on delete keyincluding (start_date_time)), &
resolveconflict (updaterowexists, (default, usemax (start_date_time))), &
resolveconflict (insertrowexists, (default, usemax (start_date_time))), &
resolveconflict (deleterowexists, (default, ignore)), &
resolveconflict (updaterowmissing, (default, overwrite)), &
resolveconflict (deleterowmissing, (default, discard)); 

For more information about these rules, refer to CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD.

Testing replication

Create the following tables for testing:

CREATE TABLE dms_sample.person(
id DOUBLE NOT NULL,
full_name varchar(60) NOT NULL,
last_name varchar(30),
first_name varchar(30)
);

create table sporting_event
(id               NUMBER NOT NULL,
 sport_type_name  VARCHAR2(15) NOT NULL
  constraint se_sport_type_fk references sport_type(name),
 home_team_id     NUMBER NOT NULL
  constraint se_home_team_id_fk references sport_team(id),
 away_team_id     NUMBER NOT NULL
  constraint se_away_team_id_fk references sport_team(id),
 location_id      NUMBER NOT NULL
  constraint se_location_id_fk references sport_location(id),
 start_date_time  DATE NOT NULL,
 sold_out         NUMBER(1) DEFAULT 0 NOT NULL,
 constraint chk_sold_out CHECK (sold_out IN (0,1)),
 constraint sporting_event_pk primary key(id)
);

Insert test

Use the dms_sample.person table for your one-way replication tests.

  1. Connect to the source database and insert one row:
    WEST_ORCL> insert into dms_sample.person values (7055279,'Sean Adams','Adams','Sean');
    
    1 row created.
    
    WEST_ORCL> commit;
    
    Commit complete.
    
    WEST_ORCL> select * from dms_sample.person where id=7055279;
    
            ID FULL_NAME    LAST_NAME     FIRST_NAME
    ---------- ------------ ------------- --------------
       7055279 Sean Adams   Adams         Sean
  2. On the target database, verify the replicated data:
    EAST_ORCL> select * from dms_sample.person where id=7055279;
    
            ID FULL_NAME    LAST_NAME     FIRST_NAME
    ---------- ------------ ------------- --------------
       7055279 Sean Adams   Adams         Sean

    Now we test the reverse replication and insert data at the target database and check if it gets replicated to the source.

  3. Connect to the target database and insert the following data:
    EAST_ORCL> insert into dms_sample.person values (7055280,'John Doe','Doe','John');
    
    1 row created.
    
    EAST_ORCL>commit;
    
    Commit complete.
  4. Verify the data on the source:
    WEST_ORCL> select * from dms_sample.person where id=7055280;
    
            ID FULL_NAME        LAST_NAME        FIRST_NAME
    ---------- ---------------- ---------------- ------------------
       7055280 John Doe         Doe              John

Conflict detection and resolution test

Use the dms_sample.sporting_event table for our conflict detection and resolution tests.

  1. Connect to the source database and update a record but don’t commit:
    WEST_ORCL> select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
            ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
    ---------- --------------- ------------ ------------ ----------- ------------------- ----------
             1 baseball                   1           11          28 04/03/2021 17:00:00          0
    
    WEST_ORCL> update SPORTING_EVENT set SOLD_OUT=1,START_DATE_TIME=sysdate where  HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
    1 row updated.
    
    WEST_ORCL> select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
            ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
    ---------- --------------- ------------ ------------ ----------- ------------------- ----------
             1 baseball                   1           11          28 01/05/2023 21:34:37          1
  2. Connect to the target database and update the same record but don’t commit:
    EAST_ORCL> select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
            ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
    ---------- --------------- ------------ ------------ ----------- ------------------- ----------
             1 baseball                   1           11          28 04/03/2021 17:00:00          0
    
    EAST_ORCL> update SPORTING_EVENT set SOLD_OUT=1,START_DATE_TIME=sysdate where  HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
    1 row updated.
    
    EAST_ORCL>  select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;
    
            ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
    ---------- --------------- ------------ ------------ ----------- ------------------- ----------
             1 baseball                   1           11          28 01/05/2023 21:35:13          1

    The Extract, Pump, and Replicat processes are in the RUNNING state in the source and target environments. The UPDATE statement has the same ID and will cause a conflict. For a resolution mechanism, we used USEMAX(start_date_time). Because the start_date_time is most recent in the EAST_ORCL Region, the Replicat process applies this change to the database in the WEST_ORCL Region. USEMAX will force it to use the most recent start_date_time for an insert or update.

  3. Run COMMIT in both the databases.

The row in the WEST_ORCL Region is updated with the latest change:

WEST_ORCL>  select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;

        ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
---------- --------------- ------------ ------------ ----------- ------------------- ----------
         1 baseball                   1           11          28 01/05/2023 21:35:13          1

EAST_ORCL>  select * from SPORTING_EVENT where HOME_TEAM_ID=1 and AWAY_TEAM_ID=11;

        ID SPORT_TYPE_NAME HOME_TEAM_ID AWAY_TEAM_ID LOCATION_ID START_DATE_TIME       SOLD_OUT
---------- --------------- ------------ ------------ ----------- ------------------- ----------
1	baseball                   1           11          28 01/05/2023 21:35:13          1

The following code block shows the status of the CDR in OGG:

GGSCI (ip-10-10-5-109.us-west-1.compute.internal) 13> stats rsrc latest reportcdr

Sending STATS request to Replicat group RSRC ...

Start of statistics at 2023-01-05 21:41:19.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         0.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

Replicating from DMS_SAMPLE.SPORTING_EVENT to DMS_SAMPLE.SPORTING_EVENT:

*** Latest statistics since 2023-01-05 21:35:50 ***
    Total inserts                              0.00
    Total updates                              1.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           1.00
    Total CDR conflicts                        1.00
    CDR resolutions succeeded                  1.00
    CDR UPDATEROWEXISTS conflicts              1.00

End of statistics.

Monitor with Amazon CloudWatch

To set up Amazon CloudWatch monitoring, use the following code:

  1. Install the CloudWatch agent:
    [ec2-user@ip-10-10-5-109 ~]$ sudo yum install amazon-cloudwatch-agent
    Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
    amzn2-core                                                                                                                                                            | 3.7 kB  00:00:00
    1 packages excluded due to repository priority protections
    Package matching amazon-cloudwatch-agent-1.247354.0b251981-1.amzn2.x86_64 already installed. Checking for update.
    Nothing to do
    [ec2-user@ip-10-10-5-109 ~]$ rpm -qa |grep cloudwatch
    amazon-cloudwatch-agent-1.247355.0b252062-1.x86_64
    [ec2-user@ip-10-10-5-109 ~]$
  2.  Use the following CloudWatch config file for ggserr.log monitoring:
    cat /opt/aws/amazon-cloudwatch-agent/bin/config.json
    {
            "agent": {
                    "metrics_collection_interval": 60,
                    "run_as_user": "cwagent"
            },
            "logs": {
                    "logs_collected": {
                            "files": {
                                    "collect_list": [
                                            {
                                                    "file_path": "/u01/app/ogg/ggserr.log",
                                                    "log_group_name": "GoldenGateLogs",
                                                    "log_stream_name": "{instance_id}"
                                            }
                                    ]
                            }
                    }
            }
    }
  3. Start CloudWatch monitoring for the ggserr.log file:
    sudo /opt/aws/amazon-cloudwatch-agent/bin/amazon-cloudwatch-agent-ctl -a fetch-config -m ec2 -s -c file:/opt/aws/amazon-cloudwatch-agent/bin/config.json

The following screenshot shows the ggserr.log output in the CloudWatch log group.

Clean up

We recommend that you clean up all the resources that aren’t in use by deleting the RDS instances and deleting the EC2 instances.

Conclusion

In this post, we covered how to implement bidirectional replication for Amazon RDS for Oracle databases running in two different Regions with Oracle GoldenGate 19c.

If you have questions or suggestions, leave a comment.


About the authors

Jay Singh is a Database Consultant with the Professional Services Team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environments to AWS Cloud database solutions.

Chandan Acharya is a Database Consultant with the Professional Services Team at Amazon Web Services. He works as database migration specialist to help Amazon customers move their on-premises database environment to AWS cloud database solutions.

Wajid Ali Mir is a Database Consultant with AWS ProServe, Global Competency Centre. His background spans a wide depth and breadth of expertise and experience in SQL and NoSQL database technologies. He is an AWS Database Migration Service expert and has developed many successful database solutions addressing challenging business requirements for moving databases from on premises to Amazon RDS and Amazon Aurora PostgreSQL/MySQL.

Kishore Kumar Balasundaram is a Sr. Solutions Architect with WWPS. He has passion to help customers design modern cloud architecture and recommend the right services for their requirements. He understands business use cases and translates them to secured, scalable, and resilient IT solutions.