Configuring Heterogeneous Data Replication between MySQL and Oracle Autonomous Data Warehouse using Oracle GoldenGate

Peter Song — Cloud Engineer

Introduction

This post will go over how to set up a unidirectional pipeline using Oracle GoldenGate Classic Architecture on the source and Microservices Architecture on the target. GoldenGate is a software package for real-time data integration and replication, and is used for solutions that require high availability, real-time data integration, transaction change data capture, data replication, and transformations. You can use GoldenGate to keep your database online during migrations. NOTE: This should only be used for testing/development and not for production.

Environment Details

• The source database is MySQL Client Version 8.0.18
• The Target Database is Autonomous Data Warehouse
• The source GoldenGate is GG Classic Edition for MySQL 19.1.0.0.201013 (Marketplace)
• The target GoldenGate is GG Microservices Edition for Oracle 19.1.0.0.201013 (Marketplace)

This blog will cover end to end, starting from the creation of the ADW Instance

1. Create the ADW Instance

To create an ADW instance you need an OCI account.

After logging into your tenancy with your OCI account, click the hamburger icon and go to Autonomous Data Warehouse

Select Data Warehouse and Shared infrastructure.

2. Create mySQL Instance on OCI Using OCI Marketplace

a. Create the MySQL Instance using OCI Marketplace

b. Select the Default version. In this case it is 8.0.23. On the next screen, select an existing VCN & subnet or create one yourself. You may upload your own SSH key or let Oracle generate one for you.

Create 2 GoldenGate Instances

a.Choose GoldenGate for Non-Oracle (classic)

b. Choose the correct version (in environment details above)

c. Choose Goldengate for Oracle (microservices)

d. Choose the correct version (in environment details above)

3. Connect to MySQL DB

Upon creating your mySQL database using the marketplace image, you should have a VCN created for you. Go into the route table rules and add port 3306/TCP to ingress rule for your VCN.

Connect to the compute instance remotely:

ssh -i <private-key-filename> opc@<gg_public-id-address>

Run the commands to download mySQL and connect to it

Create GoldenGate User

Login as root and enter password when prompted. If you didn’t create a password during the creation of the database, you can get the temporary password with the following command

sudo grep 'temporary password' /var/log/mysqld.log

Then log in with the command below using the temporary password

mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY '<MY_NEW_PASS>'

Create the database and table to use to pull data from

create database GGTEST; use GGTEST; <create table here>

Create the GoldenGate User

create user 'ggsrc'@'<GG_IP>' identified by '<PASSWORD'

Grant necessary privileges

grant all privileges on *.* to 'ggsrc'@'<GG_IP>';

Login to goldengate (non-oracle) instance.

ssh -i <private-key> opc@<goldengateIP>

Start the Extract

./ggsci

Edit the Extract parameter parameter file

edit param extmysql
*** param file begins here***
EXTRACT extmysql
sourcedb GGTEST@<mySQLIPADDRESS>, USERID ggsrc, password <PASSWORD>
TRANLOGOPTIONS ALTLOGDEST REMOTE
EXTTRAIL ./dirdat/et TABLE GGTEST.<TABLENAME>;

Add the extract

ADD extract extmysql, TRANLOG, BEGIN NOW

Add the extract trail

ADD EXTTRAIL ./dirdat/et, EXTRACT extmysql

Login to the mySQL database

dblogin sourcedb GGTEST@<db_ip_address>:3306, userid ggsrc, password <password>

Start the manager

start mgr

Start the extract

START EXTRACT extmysql

4. Creating the Pump Extract

Create the Pump Extract. This is what will send the extract file to the target GoldenGate manager

edit param PMPSQL
*** START OF PMPSQL FILE NOW ***
EXTRACT PMPSQL
RMTHOST <<GoldenGate for Oracle IP address>, PORT <receiver_port>
RMTTRAIL aa PASSTHRU TABLE GGTEST.<TABLE_NAME>; 

Then add the extract

add extract pmpsql, exttrailsource ./dirdat/et

Add the rmttrail. This is where the trail file will be sent on the target side.

Add rmttrail aa, extract PMPSQL

***POSSIBLE ERRORS***

Error 1: process abends, ‘tcp/ip’ error no route to host.

This error is caused usually by firewall issues with the target side. To fix:

a. Check which ports are open:

Go into your target goldengate instance and check which ports are open

sudo firewall-cmd --list-ports

b. If you see that the receiver port you specified isn’t there add it with these commands:

sudo firewall-cmd --permanent --add-port=<receiver port number>/tcp
sudo firewall-cmd --reload

Error 2: OGG-01224 command ‘start server’ is not supported by OGG admin Server. This error is because you did not list the receiver server as the port and the admin server. This is a microservice architecture, so you must specify the receiver server port number. Edit the param file and specify the RECEIVER PORT.

Configuring the Microservices Target Side

1. Logging into the target ADW

Download the wallet file from the OCI console if you have not already done so. Click DB Connection & click download wallet.

Open SQL Developer and connect to the Database with the wallet file and database admin credentials. you can get this info here too.

Then issue the following commands to select the ggadmin user. When using GoldenGate with ADW a GoldenGate user is created for you.

SQL> select * from dba_users order by username;
SQL> alter user ggadmin identified by <password> account unlock;

Check whether the parameter enable_goldengate_replication is set to true. If not, then modify the parameter

SQL> select * from v$parameter where name = 'enable_goldengate_replication';
SQL> alter system set enable_goldengate_replication = true scope = both;

2. Create the target schema

SQL> create user appadmin identified by <*****>;
SQL> grant create session, resource, create view, create table to appadmin;
SQL> alter user appadmin quota unlimited on data;

Move Client Credentials to Oracle GoldenGate compute node

ssh -i <private_key> opc@<public_ip_address> 

Create a staging directory and grant the essential permissions and then exit the session.

$ mkdir stage
$ exit

Copy the credentials zip file to the compute node (outside the goldengate node)

$ scp -i <private_key_file> ./<credential_file> opc@<public_ip_address>:~/stage

SSH back into the GoldenGate node

ssh -i <private_key> opc@<public_ip_address>

Verify that the credentials zip file is in the stage location

$ cd ~/stage
$ ls -ltr

4. Configure the Goldengate compute node with Autonomous Client Credentials

In the compute node, unzip the client credentials file

unzip ./<credential_file> -d ./client_credentials

Copy the sqlnet.ora and tnsnames.ora files to the location of your TNS_ADMIN

$ cd ~/stage/client_credentials
$ cp ./sqlnet.ora /u02/deployments/<deployment>/etc
$ cp ./tnsnames.ora /u02/deployments/<deployment>/etc

Edit the sqlnet.ora file and replace the directory parameter with the locaiton of the information pointing to the location where the client credentials were unzipped

$ cd /u02/deployments/<deployment>/etc 
$ vi ./sqlnet.ora

Within the sqlnet.ora file do the following

$ export ORACLE_HOME=/u01/app/client/<oracle version>
$ export TNS_ADMIN=/u02/deployments/<deployment>/etc

Test the connection to the ADW by connecting to one of the entries

$ cd $ORACLE_HOME/bin
$ ./sqlplus appadmin/**********@orcladw_low

5. Configure the Microservice side for replication

a. Log in to the Service Manager using password for oggadmin (in the ogg-credentials.json file in the goldengate node). There are known issues with Google Chrome. It works with Mozilla. You can access the service manager with https://<public_ip_address>

b. From the service manager main page, select the hyperlink for the port number associated with the administration service

c. Open the context menu in the top left corner of the Overview page.

d. From the context menu, select Configuration.

e. From the Database tab, click the plus ( + ) icon, to add a new credential.

f. Provide the following information and click Submit

Credential Domain: [Defaults to OracleGoldenGate]

Credential Alias: [Name of the Alias]

User ID: ggadmin@<adw_tnsnames_reference>

Password: [Password for ggadmin]

Verify Password: [Password for ggadmin]

g. Test the connection to the Autonomous Data Warehouse by clicking the Log in database icon after the credential has been added.

6. Create the checkpoint table on the target side

Add the checkpoint table

Add the transaction information in the form of schemaname.table_name

Then go back to the service manager and to the administration server and click the + button in the replicat page

Once you click the replicat page, choose non-integrated replicat. Click Next. Fill it out like so. Make sure the trail name is the same trail you specify from the pump extract on the source side.

Next create the parameter file.

replicat rep2
useridalias ggadmin domain OracleGoldenGate
MAP <DB_NAME>.<TABLE_NAME>, TARGET <DB_NAME>.<TABLE_NAME>;

You are done setting up the target side. The only thing to do now is make sure it works! Insert something on the source mySQL database and see if it shows up on the target ADW. Another easy way to check is to go to statistics and see if the DML operation shows up.

Originally published at http://github.com.