Note:

Set up Bidirectional Replication Between Two Oracle Heatwave MySQL managed instances using OCI GoldenGate

Introduction

Oracle Cloud Infrastructure GoldenGate (OCI GoldenGate) is a fully managed service that helps data engineers move data in real-time, at scale, from one or more data management systems to OCI databases. Design, run, orchestrate, and monitor data replication tasks in a single interface without having to allocate or manage any compute environments. OCI GoldenGate supports several sources and targets, including MySQL and Oracle HeatWave MySQL database service.

In this tutorial, we will guide you how to set up bidirectional replication using OCI GoldenGate between two Oracle HeatWave MySQL instances in OCI.

Objectives

Prerequisites

Task 1: Deploy OCI GoldenGate

  1. Log in to the OCI Console, search for GoldenGate, select GoldenGate service and click Create Deployment.

  2. Enter the following information and click Create.

    • Name: Enter MySQLggdeployment1.
    • Deployment Type: Select Data replication.
    • Select Technology: Select MySQL.
    • Select Version: Enter 21.15.
    • Hardware configuration: Enter # of OCPUs.
    • Subnet selection: Select subnet.
    • License Type Select license type.
    • Instance name: Enter GGInstance1.
    • Credential Store: Select GoldenGate (create a new password secret or select an existing one).

Task 2: Create Users in Oracle HeatWave MySQL Instances

  1. Use OCI Bastion host to connect to source and target MySQL instances and create users for OCI GoldenGate Extract and Replicat processes. Run the following query:

    • On source MySQL instance.

      > create user 'ggsuser_S'@'%' identified by "<password>";
> grant all privileges on airportdb.* to 'ggsuser_S'@'%' with grant option;
> Grant select,  process, replication slave, replication client on *.* to 'ggsuser_S'@'%';

    • On target MySQL instance.

      > create user 'ggsuser_T'@'%' identified by "<password>";
> grant all privileges on airportdb.* to 'ggsuser_T'@'%' with grant option;
> Grant select,  process, replication slave, replication client on *.* to 'ggsuser_T'@'%';

Task 3: Set up Connections in OCI GoldenGate Deployment

  1. Go to the OCI GoldenGate deployment page and click Connections to set up the connection.

    Image showing how to create a connection

  2. Enter the connection information.

    Image showing GoldenGate Connections

  3. Repeat the above steps to add both source and target connections.

  4. Select Deployments and click Assigned Connections to assign the connections to the deployment.

    Image showing how to assign connections to deployment

Task 4: Set up Ingress Rules and Security Lists for OCI GoldenGate Console

  1. Set up ingress rules and update security lists to allow communication between the Oracle HeatWave MySQL instances, OCI GoldenGate deployment and OCI Bastion compute.

    Note: If you are using VPN then you can skip this step.

    Follow the steps listed here: Option B: Use your own bastion on OCI Compute.

    ssh -i <private-ssh-key-of-bastion-compute> opc@<bastion-compute-public-ip> -L 443:<GoldenGategate-deployment-hostname>:443 -N

  2. Test the connections for both source and target databases in OCI Console and through OCI GoldenGate Console.

    1. Validate the MySQL instance connections within OCI Console.

      Image showing how to test a connection

    2. Validate the same connections from the OCI GoldenGate Console.

      Image showing how to test a connection in GG console

Task 5: Create the Extract and Replicat Processes

  1. Create a primary Extract (EXT1).

    1. Log in to the OCI GoldenGate Console.

    2. Go to Overview and click + in the Extracts section.

      Image showing how to add EXTRACT

    3. Enter the Extract information.

      Image showing EXTRACT details

    4. Edit the parameter file as needed. The following parameter file captures all changes in classicmodels database including DDL changes.

      EXTRACT ext1
USERIDALIAS  MySQLpoc1, DOMAIN OracleGoldenGate
EXTTRAIL e1
DDL INCLUDE MAPPED
TRANLOGOPTIONS FETCHPARTIALJSON
TABLE classicmodels.*;

      Note: The MySQLpoc1 is a source instance.

    5. Start the Extract and note down the Global Transaction Identifier (GTID) on the source.

      MySQL>  select @@gtid_executed, @@gtid_purged\G
*************************** 1. row ***************************
@@gtid_executed: 3b631a96-6aa7-11ef-95c0-02001701769c:1-94    <--- make a note of this GTID
@@gtid_purged: 3b631a96-6aa7-11ef-95c0-02001701769c:1-72
1 row in set (0.00 sec)
MySQL>

      Start the Extract and keep it running all the time, even when you are still working on setting up the target database to ensure all changes are captured.

  2. Create a primary Replicat (REP1).

    1. Log in to the OCI GoldenGate Console.

    2. Go to Overview and click + in the Replicat section.

      Image showing REPLICAT details

      Note: The checkpoint table name should be prefixed with the database/schema name in lowercase. Failure to do so will cause checkpoint table creation to fail.

      For example, classicmodels.OCIGG_CHECKPOINT_REP1.

      It is recommended to create a separate dedicated database/schema (for example, ggadmin schema) for checkpoint table.

      MySQL> create database ggadmin;
Query OK, 1 row affected (0.01 sec)
MySQL> grant all privileges on ggadmin.* to 'ggsuser_T'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
MySQL> grant all privileges on ggadmin.* to 'ggsuser_S'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

    3. Edit the parameter file as needed. The following parameter file replicates all objects under the classicmodels database along with DDL changes.

      REPLICAT rep1
USERIDALIAS  MySQLpoc2, DOMAIN OracleGoldenGate
DDL INCLUDE MAPPED
MAP classicmodels.*, TARGET classicmodels.*;

      Note: The MySQLpoc2 is a target instance.

    4. Since this is the primary Replicat and it was started for the first time, it will start applying from trail file 0. However if you want to alter the Replicat to start from a particular GTID then follow the steps:

      1. Go to Replicat section in the OCI GoldenGate Console.

      2. Select the replicat, click Alter, Edit, BEGIN, select GTID and enter the GTID.

  3. So far we have set up the unidirectional for both Data Manipulation Language (DML) and DDL replication. Once the unidirectional replication is in sync, we can proceed with bidirectional replication.

    1. Repeat step 5.1 and step 5.2, but this time we reverse the source and target details. The source database instance will now act as target instance and target instance will act as source instance.

      • Source: Enter MySQLpoc2.
      • Target: Enter MySQLpoc1.
      • For this use case, we have used a second database airportdb. You can setup bidirectional replication for the same database.

    2. Add primary Extract (EXT2).

      EXTRACT ext2
USERIDALIAS  MySQLpoc2, DOMAIN OracleGoldenGate
EXTTRAIL e2
DDL INCLUDE MAPPED
TRANLOGOPTIONS FILTERTABLE ggadmin.OCIGG_CHECKPOINT_REP*  -- from 23ai GG use EXCLUDEFILTERTABLE
TABLE airportdb.*;

    3. Manually create checkpoint table as this is a Classic Replicat. Only Classic and Coordinated Replicats are supported for bidirectional replication.

      For example: ggadmin.OCIGG*CHECKPOINT_REP`- Add primary REPLICAT REP2:` REPLICAT rep2
USERIDALIAS MySQLpoc1, DOMAIN OracleGoldenGate
DDL INCLUDE MAPPED
MAP airportdb.*, TARGET airportdb.\_;

Task 6: Run DDL and DML Tests

Now that the bidirectional setup is completed, it is time to run simple DML and DDL tests.

--DML test from classicmodels database on source MySQLpoc1
MySQLpoc1> select count(*) from weatherdata;
+----------+
| count(*) |
+----------+
|  4626432 |
+----------+
1 row in set (0.19 sec)

MySQLpoc1> insert into weatherdata values ('2005-01-02','04:50:00',-8.1,3,57.0,990.00,38.00,'Nebel-Schneefall',61);
Query OK, 1 row affected (0.00 sec)

MySQLpoc1> select count(*) from weatherdata;
+----------+
| count(*) |
+----------+
|  4626433 |
+----------+
1 row in set (0.23 sec)

-DDL replication test from classicmodels database on source MySQLpoc1
MySQLpoc1> create table test (name char(5));
Query OK, 0 rows affected (0.02 sec)

MySQL> insert into test values ('cj');
Query OK, 1 row affected (0.00 sec)

MySQL>

--Now Let's check if the above DML and DDLs got replicated to target MySQLpoc2
MySQLpoc2> select count(*) from weatherdata;
+----------+
| count(*) |
+----------+
|  4626433 | <--- row count matches to source
+----------+
1 row in set (0.37 sec)

MySQLpoc2>
MySQLpoc2> select * from test;
+------+
| name |
+------+
| cj   |          <-- table CJ got replicated
+------+
1 row in set (0.00 sec)

MySQL>


-- Now testing Bi directional
-- on target (MySQLpoc2)
MySQLpoc2> select * from test;
+------+
| name |
+------+
| cj   |
+------+
1 row in set (0.00 sec)

MySQLpoc2> insert into test values ('cj2');
Query OK, 1 row affected (0.01 sec)

MySQLpoc2> select * from test;
+------+
| name |
+------+
| cj   |
| cj2  |
+------+
2 rows in set (0.00 sec)

MySQLpoc2> insert into test values ('cjs3');
Query OK, 1 row affected (0.00 sec)

MySQLpoc2> select * from test;
+------+
| name |
+------+
| cj   |
| cj2  |
| cjs3 |
+------+
3 rows in set (0.00 sec)

MySQLpoc2>

--On source (MySQLpoc1):
--DML on the target got REPLICATed on the source
MySQLpoc1> select * from test;
+------+
| name |
+------+
| cj   |
| cj2  |
| cjs3 |
+------+
3 rows in set (0.00 sec)

MySQL>

Task 7: Set up Initial Load Extract

  1. Set up Initial load Extract if you want to leverage OCI GoldenGate to perform the initial loading of data into the target database.

    1. Log in to the OCI GoldenGate Console.

    2. Go to Overview and click + in the Extract section. This is very similar to creating a primary Extract, the only difference is to select Extract type as Initial Load when creating a Extract.

      Parameter file for initial load Extract.

      Parameter file for initial load EXTRACT:
EXTRACT EXTIL
USERIDALIAS  MySQLpoc1, DOMAIN OracleGoldenGate
EXTFILE il ,  PURGE
TABLE airportdb.*;
MAP_PARALLELISM 4
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
SPLIT_TRANS_RECS 1000
CHUNK_SIZE 1 GB

  2. Similarly set up a Replicat that will read the trail files generated by initial load Extract. This Replicat and the initial load Extract will both be dropped once the initial load is complete.

    1. On target database, make sure all tables are empty. Drop/Disable all the Foreign Keys on target. Disable Triggers and Indexes on target to improve initial load performance.

      Note: Take a backup for the schema objects DDLs before dropping any.

    2. The following is the order in which Extract and Replicat processes should be created if using initial load Extract for initial data load.

      1. Create a primary Extract for source (do not start the process yet).

      2. Create an initial load Extract for source (do not start the process yet) and capture the GTID on source.

      3. Create a Replicat to process the trail files generated by initial load Extract for target (do not start the process yet).

        Note: There is no separate Replicat type for initial load.

      4. Create a primary Replicat for target (do not start the process yet).

      5. Use the same checkpoint table for both initial load Replicat and the primary Replicat. Initial load Replicat points to initial Extract trail files and primary Replicat points to primary Extract trail files (both trail files are different).

    3. The following is the order in which the processes should be started when using initial load Extract.

      1. Start the primary Extract and make a note of the GTID that it registers with.

      2. Start the Replicat that was created for initial load Extract.

      3. Edit the initial load Extract and alter it to start with GTID that you got when starting primary Extract.

      4. Start the primary Replicat.

        Note:

        • Start the primary Replicat only after both initial load Extract and Replicat for initial load are in sync (LAG 0).
        • Create or enable the foreign keys before starting the primary Replicat.

        Create indexes (if you dropped them earlier to improve initial load performance) before starting the primary Replicat.

Limitations

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.