GoldenGate Cloud Service (GGCS): Replication from Amazon RDS MySQL Database to Oracle DBCS

Introduction

This document will walk you through how to configure Oracle GoldenGate (OGG) replication from Amazon Relational Database Service (RDS) MySQL Database to an Oracle Database Cloud Service (DBCS) on Oracle Public Cloud (OPC) via Amazon Web Services (AWS) Elastic Compute Cloud (EC2) and Oracle GoldenGate Cloud Service (GGCS).

The following topics are not included in the scope of this article:

  • Installation of MySQL Database on Amazon EC2 Linux instance
  • Installation of Oracle GoldenGate for MySQL Database on Amazon EC2 Linux instance
  • Provisioning of Oracle GGCS instance
  • Provisioning of Oracle DBCS instance
  • Provisioning of the Amazon EC2 Linux instance
  • Provisioning of Amazon RDS MySQL Database instance

It is assumed that Amazon EC2 and RDS, together with Oracle GGCS and DBCS have been provisioned and MySQL Database together with Oracle GoldenGate Software has been installed on the Amazon EC2 instance.

The scripts and information provided in this article are for educational purposes only. They are not supported by Oracle Development or Support, and come with no guarantee or warrant for functionality in any environment other than the test system used to prepare this article.

For details on OGG installation and provisioning of DBCS and GGCS, please check the following Oracle Documentation links:

For details on provisioning of Amazon EC2 and RDS, please check the following Amazon Web Services (AWS) Documentation links:

For details on MySQL installation on your Linux instance, please check the following MySQL Documentation links:

GoldenGate Cloud Service (GGCS)

The GoldenGate Cloud Service (GGCS), is a cloud based real-time data integration and replication service that resides in the Oracle Public Cloud (OPC).  It provides seamless and easy data movement from various On-Premises relational databases to databases in the cloud with sub-second latency while maintaining data consistency and offering fault tolerance and resiliency.

Figure 1: GoldenGate Cloud Service (GGCS) Architecture Diagram

ggcs_architecture_01

OGG Replication from Amazon RDS MySQL Database to Oracle DBCS via GGCS

Oracle GoldenGate version 12c  doesn’t currently support direct capture from Amazon RDS MySQL database, however it can operate concurrently with MySQL Native replication.

To capture data transactions from Amazon RDS MySQL database we would need to deploy a second MySQL database on EC2 instance and configure it with MySQL native replication having the RDS MySQL database as the master and the MySQL database on the EC2 instance as the slave; and then deploy GoldenGate on the EC2 instance and configure OGG to capture from the MySQL slave database.

Figure 2: GoldenGate Deployment Replication from Amazon RDS MySQL to Oracle DBaaS 

RDS_MySQL_GGCS_DBCS_Diagram_v2

Pre-requisites for Oracle GoldenGate on Amazon EC2

The following are the steps that needs to be done to prepare GoldenGate to capture transaction from  Amazon RDS MySQL database:

  • Set required MySQL Database Parameters on Amazon RDS MySQL Database
  • Configure MySQL Native Replication between RDS MySQL database (master) and EC2 MySQL database (slave)

Set required MySQL Database Parameter on Amazon RDS MySQL Database

Create a separate RDS Database parameter group with the following initialization parameter set:

  • binlog_format parameter set to ROW

For details on how to create or modify an Amazon RDS Database parameter group, please check the following Amazon Web Services (AWS) Documentation links:

Configure MySQL Native Replication between RDS MySQL (master) and EC2 MySQL (slave)

The following are the high level steps to configure MySQL Native replication between RDS MySQL (master) and EC2 MySQL (slave):

  • Set a longer retention time frame of the binary logs on RDS MySQL
  • Create replication user on RDS MySQL
  • Modify EC2 MySQL Configuration file (/etc/my.cnf) to support slave replication
  • Set the starting point of MySQL native replication on EC2 MySQL
  • Start EC2 MySQL native replication process

Set RDS MySQL binlog retention time frame

Since we don’t have direct access to the binary logs on the RDS MySQL database, we would need to modify the retention time frame of it’s binary logs to accommodate a time that is enough to make sure that changes have been applied to the slave database before the binlog file is deleted by Amazon RDS.

To set the time frame retention of the binary logs on RDS MySQL, you would need to connect to the RDS MySQL database via mysql from the EC2 instance and execute Amazon’s mysql  procedure “mysql.rds_set_configuration”.

To find the correct host to connect to for RDS MySQL database engine, you will need to locate the RDS MySQL Database Endpoint from the Amazon RDS dashboard and use that as the host string for your mysql command.

mysql_endpoint_01

 

Login to RDS MySQL via mysql and set it’s binlog retention time frame (example: 7 days  = 168 hours).

[ec2-user@ip-10-0-1-12 ~]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 147
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> CALL mysql.rds_set_configuration(‘binlog retention hours’, 168);

Query OK, 0 rows affected (0.19 sec)

Create MySQL Native Replication user on RDS MySQL

Now, we need create a database user that our native MySQL replication process from EC2 MySQL will use to connect to the RDS MySQL database and grant slave replication privileges.

Login to RDS MySQL via mysql to create the database slave replication user.

[ec2-user@ip-10-0-1-12 ~]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> create user ‘ggslave’@’%’ IDENTIFIED BY ‘ggpass’;

Query OK, 0 rows affected (0.04 sec)

Now, we grant slave replication privileges to the newly created user.

mysql> grant replication slave on *.* TO ‘ggslave’@’%’;

Query OK, 0 rows affected (0.00 sec)

Configure EC2 MySQL to support MySQL native replication

On Linux, /etc/my.cnf is the default MySQL configuration file. This file needs to be modified to configure MySQL native replication and once the configuration file has been modified the mysql database engine needs to be restarted for it to take into effect.

Here’s a sample /etc/my.cnf file that was used for this test:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id=987321
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100
binlog_format=row
log_slave_updates=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

After /etc/my.cnf file has been modified, we need to restart the MySQL database engine (mysqld) for the changes to take into effect via the service command as root or via sudo:

[ec2-user@ip-10-0-1-12 ~]$ sudo service mysqld restart

Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

Set the starting point of MySQL native replication on EC2 MySQL instance

Now, we are ready to set the starting point of the slave replication process on EC2 MySQL. We need to query the status of binary logs of the RDS MySQL which will be our master database via  mysql “show master status” command. We will use these information as the values to be passed as a starting point for our EC2 MySQL slave replication.

[ec2-user@ip-10-0-1-12 ~]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1154
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> show master status;

+—————————-+———-+————–+——————+——————-+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————————-+———-+————–+——————+——————-+
| mysql-bin-changelog.001144 | 422      |              |                  |                   |
+—————————-+———-+————–+——————+——————-+
1 row in set (0.01 sec)

Take a note of the value of the “File” (mysql-bin-changelog.001144) and the “Position” (422), we will use these values as a starting point for the EC2 MySQL replication slave when we configure and start the slave process.

Now we login to the EC2 MySQL database (slave – localhost) and set the slave replication process on EC2 MySQL to use RDS MySQL as it’s master and set its starting point.

[ec2-user@ip-10-0-1-12 ~]$ mysql -h localhost -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1154
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> change master to master_host=’ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com’, master_password=’ggpass’, master_user=’ggslave’, master_log_file=’mysql-bin-changelog.001144′, master_log_pos=422;

Start EC2 MySQL native replication process

Now, that we configured the EC2 MySQL replication to point to RSD MySQL as the master and set the starting point, we can now start the slave replication process.

[ec2-user@ip-10-0-1-12 ~]$ mysql -h localhost -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 398
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Now, let’s check the status of the slave process:

mysql> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com
Master_User: ggslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-changelog.001144
Read_Master_Log_Pos: 422
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 293
Relay_Master_Log_File: mysql-bin-changelog.001144
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 422
Relay_Log_Space: 467
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1476793473
Master_UUID: 737e38be-2958-11e7-a5cc-06eeef0cc0ba
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

Now let’s verify we have a working MySQL native replication between RDS MySQL (master) and EC2 MySQL (slave) databases by creating the database (tcustdb) and tables (TCUSTMER & TCUSTORD) will use for Oracle GoldenGate replication on RDS MySQL and make sure that it gets replicated to EC2 MySQL.

First, let’s verify that “tcustdb” schema/database doesn’t exist on both MySQL databases.

RDS MySQL database:

[ec2-user@ip-10-0-1-12 ~]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1244
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> show databases like ‘tcustdb’;

Empty set (0.00 sec)

EC2 MySQL database:

[ec2-user@ip-10-0-1-12 ~]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> show databases like ‘tcustdb’;

Empty set (0.00 sec)

Second, now that we verified ‘tcustdb’ doesn’t exist, let’s create it on RDS MySQL and create TCUSTMER & TCUSTORD tables on the tcustdb database and verify it gets replicated to the EC2 MySQL database.

RDS MySQL database:

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1250
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> create database tcustdb;

Query OK, 1 row affected (0.01 sec)

mysql> use tcustdb;

Database changed

Now, let’s create the tables (TCUSTMER & TCUSTORD) via the create table script “demo_mysql_create.sql” that can be found on OGG installation directory.

mysql> source demo_mysql_create.sql;

Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+——————-+
| Tables_in_tcustdb |
+——————-+
| TCUSTMER          |
| TCUSTORD          |
+——————-+
2 rows in set (0.00 sec)

Now, let’s verify if the “tcustdb” database together with TCUSTMER & TCUSTORD tables have been populated onto EC2 MySQL database.

EC2 MySQL database:

[ec2-user@ip-10-0-1-12 ~]$  mysql -h localhost -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

Let’s check first the process status of the slave replication via “show processlist” command:

mysql> show processlist;

+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| 6  | system user |           | NULL | Connect | 3489 | Waiting for master to send event                                             | NULL             |
| 7  | system user |           | NULL | Connect | 254  | Slave has read all relay log; waiting for the slave I/O thread to update it   | NULL             |
| 10 | root        | localhost | NULL | Query   | 0    | init                                                                        | show processlist |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+

Since the status states all relay log has been read, let’s verify if “tcustdb” and TCUSTMER & TCUSTORD have been created.

mysql> show databases like ‘tcustdb’;

+——————–+
| Database (tcustdb) |
+——————–+
| tcustdb            |
+——————–+
1 row in set (0.00 sec)

mysql> use tcustdb;

Database changed

mysql> show tables;

+——————-+
| Tables_in_tcustdb |
+——————-+
| TCUSTMER          |
| TCUSTORD          |
+——————-+
2 rows in set (0.00 sec)

Now, we have a working MySQL Native replication between RDS MySQL (Master) and EC2 MySQL (slave) and we can move on to configuring Oracle GoldenGate software to capture data from Amazon RDS MySQL using the slave database that we configure on EC2 MySQL as it’s source database.

OGG Replication from Amazon RDS MySQL Database to Oracle DBCS via GGCS

We will use the slave replicated database that we configured for MySQL native replication on EC2 MySQL as the source database for Oracle GoldenGate. In essence, we are capturing the data that were replicated by MySQL native replication from RDS MySQL (master) to EC2 MySQL (slave).

Since, we have already a configured running MySQL Native replication between RDS MySQL (master) and EC2 MySQL (slave), we just need to configure OGG to use EC2 MySQL database as it’s source for the capture process.

The high level steps for OGG replication from EC2 MySQL (slave/ogg source) to DBaaS/DBCS (target) database in the Oracle Public Cloud (OPC) are as follows:

  • Configure and Start GGCS Oracle GoldenGate Manager on the OPC side
  • Configure and Start SSH proxy server process on the  Amazon EC2 instance
  • Configure and Start OGG Extract process on the Amazon EC2 instance
  • Configure and Start OGG Extract Data Pump process on the Amazon EC2 instance
  • Configure and Start GGCS Replicat process on the OPC side to deliver data into the target DBaaS/DBCS

GGCS Oracle GoldenGate Manager

To start configuring Oracle GoldenGate on the GGCS instance, the manager process must be running. Manager is the controller process that instantiates the other Oracle GoldenGate processes such as Extract, Extract Data Pump, Collector and Replicat processes.

Connect to GGCS Instance through ssh and start the Manager process via the GoldenGate Software Command Interface (GGSCI).

mpapio@borg:~$ ssh -i ateam_opc_ssh_key opc@129.144.0.216

Note: SSH connection to the Cloud servers on both Oracle Public Cloud (OPC) and Amazon Web Services (AWS) is secured via public & private key pair files. The public key file exist on the Cloud server instance and the private key file is passed as argument and used for authentication. In this use case, we are using “mp_opc_ssh_key” as the private key file. For more details on SSH key pair, check this documentation link – Generating an SSH Key Pair for OPC and this documentation link – Amazon EC2 Key Pairs for AWS.

[opc@ateam-ggcs-aa-ggcs-1 ~]$ sudo su – oracle
[oracle@ateam-ggcs-aa-ggcs-1 ~]$ cd $GGHOME

Note: By default, “opc” user is the only one allowed to ssh to GGCS instance. We need to switch user to “oracle” via “su” command to manage the GoldenGate processes. The environment variable $GGHOME is  pre-defined in the GGCS instance and it’s the directory where GoldenGate was installled.

[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160517 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160711.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 12 2016 02:21:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (ateam-ggcs-aa-ggcs-1) 1> start mgr

Manager started.

GGSCI (ateam-ggcs-aa-ggcs-1) 2> info mgr

Manager is running (IP port ateam-ggcs-aa-ggcs-1.7777, Process ID 124024).

Note: By default, GoldenGate processes doesn’t accept any connection remotely. To enable connection from other hosts via the SSH proxy we need to add an ACCESS RULE to the Manager parameter File (MGR.prm) to allow connectivity through the public IP Address of the GGCS Instance.

Here’s the MGR.prm file used in this example:

–###############################################################
–## MGR.prm
–## Manager Parameter Template
— Manager port number
— PORT <port number>
PORT 7777
— For allocate dynamicportlist. Here the range is starting from
— port n1 through n2.
Dynamicportlist 7740-7760
— Enable secrule for collector
ACCESSRULE, PROG COLLECTOR, IPADDR 129.144.0.216, ALLOW
— Purge extract trail files
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24
— Start one or more Extract and Replicat processes automatically
— after they fail. –AUTORESTART provides fault tolerance when
— something temporary interferes with a process, such as
— intermittent network outages or programs that interrupt access
— to transaction logs.
— AUTORESTART ER *, RETRIES <x>, WAITMINUTES <y>, RESETMINUTES <z>
— This is to specify a lag threshold that is considered
— critical, and to force a warning message to the error log.
— Lagreport parameter specifies the interval at which manager
— checks for extract / replicat –lag.
–LAGREPORTMINUTES <x>
–LAGCRITICALMINUTES <y>
–Reports down processes
–DOWNREPORTMINUTES <n>
–DOWNCRITICAL

Start SSH Proxy Server on the Amazon EC2 instance

By default, the only access allowed to GGCS instance is via ssh, so to allow communication of GoldenGate processes from the Amazon cloud to GGCS instance we would need to run SSH proxy server on the Amazon side to communicate to GoldenGate processes on the GGCS instance.

Connect to Amazon EC2 Instance through ssh and start the proxy server.

mpapio@borg:~$ ssh -i mp_opc_ssh_key ec2-user@aws-ateam-gg-hub-mysql-aa

Last login: Sat Apr 29 01:06:30 2017
__|  __|_  )
_|   (    / Amazon Linux AMI
___|\___|___|
https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/

Start the SSH proxy server process via the following ssh command (all in one line):

[ec2-user@ip-10-0-1-12 ~]$ cd ogg12c_mysql56
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ ssh -i ateam_opc_ssh_key -v -N -f -D 127.0.0.1:8888 opc@129.144.0.216 > ./dirrpt/socks.log 2>&1

Command Syntax: ssh –i {private_key_file} -v –N –f –D {istening_ip_address:listening_tcp_port_address} {user}@{GGCS_Instance_IP_address} > {output_file} 2>&1

SSH Command Options Explained:

  1. -i = Private Key file
  2. -v = Verbose Mode
  3. -N = Do no execute remote command; mainly used for port forwarding 
  4. -f = Run ssh process in the background
  5. -D Specifies to run as local dynamic application level forwarding; act as a SOCKS proxy server on a specified interface and port
  6. listening_ip_address = Host Name or Host IP Address where this SOCKS proxy will listen (127.0.0.1 is the loopback address)
  7. listening_tcp_port_address = TCP/IP Port Number to listen on
  8. 2>&1 = Redirect Stdout and Stderr to the output file
  9. Verify the SSH Socks Proxy server process has started successfully.

    1. Check the socks proxy output file via the “cat” utility and look for the messages “Local connections to forwarded…” and “Local forwarding listening on port ”.  Make sure it’s connected to GGCS instance and listening on the right IP and port address.

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ cat ./dirrpt/socks.log

OpenSSH_6.6.1, OpenSSL 1.0.1k-fips 8 Jan 2015
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 56: Applying options for *
debug1: Connecting to 129.144.0.216 [129.144.0.216] port 22.
debug1: Connection established.
debug1: identity file ateam_opc_ssh_key type -1
debug1: identity file ateam_opc_ssh_key-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_6.6.1
debug1: Remote protocol version 2.0, remote software version OpenSSH_5.3
debug1: match: OpenSSH_5.3 pat OpenSSH_5* compat 0x0c000000
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-md5 none
debug1: kex: client->server aes128-ctr hmac-md5 none
debug1: kex: diffie-hellman-group-exchange-sha256 need=16 dh_need=16
debug1: kex: diffie-hellman-group-exchange-sha256 need=16 dh_need=16
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<3072<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Server host key: RSA c6:af:b3:f4:49:b0:4d:2b:8f:52:92:e8:e2:60:23:16
debug1: Host ‘129.144.0.216’ is known and matches the RSA host key.

debug1: Authentication succeeded (publickey).
Authenticated to 129.144.0.216 ([129.144.0.216]:22).
debug1: Local connections to 127.0.0.1:8888 forwarded to remote address socks:0
debug1: Local forwarding listening on 127.0.0.1 port 8888.
debug1: channel 0: new [port listener]
debug1: Requesting no-more-sessions@openssh.com
debug1: forking to background
debug1: Entering interactive session.

Configure Oracle GoldenGate on Amazon EC2 instance

For our test, we shall use the TCUSTMER and TCUSTORD tables for MySQL source and Oracle target database.

MySQL (Source) Table Create SQL statement:

CREATE TABLE TCUSTMER
(
CUST_CODE    VARCHAR(4) NOT NULL,
NAME         VARCHAR(30),
CITY         VARCHAR(20),
STATE        CHAR(2),
PRIMARY KEY (CUST_CODE)
);
CREATE TABLE TCUSTORD
(
CUST_CODE      VARCHAR(4) NOT NULL,
ORDER_DATE     DATETIME NOT NULL,
PRODUCT_CODE   VARCHAR(8) NOT NULL,
ORDER_ID       INTEGER NOT NULL,
PRODUCT_PRICE  DECIMAL(8,2),
PRODUCT_AMOUNT INTEGER,
TRANSACTION_ID FLOAT,
PRIMARY KEY (CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID)
);

Oracle (Target) Table Create SQL statement:

CREATE TABLE tcustmer
(
cust_code       VARCHAR2(4),
name            VARCHAR2(30),
city            VARCHAR2(20),
state           CHAR(2),
PRIMARY KEY (cust_code)
USING INDEX
);
CREATE TABLE tcustord
(
cust_code       VARCHAR2(4),
order_date      DATE,
product_code    VARCHAR2(8),
order_id        NUMBER,
product_price   NUMBER(8,2),
product_amount  NUMBER(6),
transaction_id  NUMBER,
PRIMARY KEY (cust_code, order_date, product_code, order_id)
USING INDEX
);

Start Oracle GoldenGate Manager on Amazon EC2 instance

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ ./ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

Note: To edit/create the GoldenGate MGR Configuration/Parameter file, you need to execute “edit param mgr” via the GGSCI utility.

GGSCI (ip-10-0-1-12) 1> edit param mgr

Here’s the MGR.prm file used in this example:

— Manager port number
PORT 7777
— For allocate dynamicportlist. Here the range is starting from port n1 through n2.
Dynamicportlist 7740-7760
— Purge extract trail files
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24

GGSCI (ip-10-0-1-12) 2> start mgr

Manager started.

GGSCI (ip-10-0-1-12) 3> info mgr

Manager is running (IP port ip-10-0-1-12.7777, Process ID 10129).

Configure and Start Oracle GoldenGate Extract Online Change Capture process 

Create an Online Change Data Capture Extract Group

For this test, we will name our Online Change Data Capture group process to ETCUSTDB.

-> Create/Add the Extract Group in GoldenGate via GGSCI:

GGSCI (ip-10-0-1-12) 1> add extract etcustdb, vam, begin now

EXTRACT added.

Note: To edit/create the Extract Configuration/Parameter file, you need to execute “edit param <group_name>” via the GGSCI utility.

GGSCI (ip-10-0-1-12) 2> edit param etcustdb

Here’s the Online Change Capture Parameter (etcustdb.prm) file used in this example:

EXTRACT etcustdb
SETENV(MYSQL_HOME=’/var/lib/mysql’)
SETENV(MYSQL_UNIX_PORT=’/var/lib/mysql/mysql.sock’)
EXTTRAIL ./dirdat/ea
SOURCEDB tcustdb@127.0.0.1, USERIDALIAS ggmysql_alias
TRANLOGOPTIONS ALTLOGDEST “/var/lib/mysql/bin-log.index”
TABLE tcustdb.TCUSTMER;
TABLE tcustdb.TCUSTORD;

Add a local extract trail to the Online Change Data Capture  Extract Group via GGSCI

GGSCI (ip-10-0-1-12) 3> add exttrail ./dirdat/ea, extract etcustdb

EXTTRAIL added.

Start the Online Change Data Capture  Extract Group via GGSCI

GGSCI (ip-10-0-1-12) 4> start extract etcustdb

Sending START request to MANAGER …
EXTRACT ETCUSTDB starting

Check the Status of Online Change Data Capture  Extract Group via GGSCI

GGSCI (ip-10-0-1-12) 1> info extract etcustdb detail

EXTRACT ETCUSTDB Last Started 2017-04-29 03:45 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 10971
VAM Read Checkpoint 2017-04-29 03:44:56.239138
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/ea 0 1324 500 EXTTRAIL
Extract Source Begin End
Not Available 2017-04-29 03:44 2017-04-29 03:44
Not Available * Initialized * 2017-04-29 03:44
Current directory /home/ec2-user/ogg12c_mysql56
Report file /home/ec2-user/ogg12c_mysql56/dirrpt/ETCUSTDB.rpt
Parameter file /home/ec2-user/ogg12c_mysql56/dirprm/etcustdb.prm
Checkpoint file /home/ec2-user/ogg12c_mysql56/dirchk/ETCUSTDB.cpe
Process file /home/ec2-user/ogg12c_mysql56/dirpcs/ETCUSTDB.pce
Error log /home/ec2-user/ogg12c_mysql56/ggserr.log

GGSCI (ip-10-0-1-12) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETCUSTDB 00:00:00 00:00:00

Configure and Start Oracle GoldenGate Extract Data Pump process on the Amazon EC2 instance

For this test, we will name our GoldenGate Extract Data Pump group process to PTCUSTDB.

Create the Extract Data Pump Group (Process) via GGSCI

The Extract Data Pump group process will read the trail created by the Online Change Data Capture Extract (ETCUSTDB) process and sends the data to the GoldenGate process running on the GGCS instance via the SSH Socks Proxy server.

GGSCI (ip-10-0-1-12) 1> add extract ptcustdb, exttrailsource ./dirdat/ea

EXTRACT added.

Note: To edit/create the Extract Configuration/Parameter file, you need to execute “edit param <group_name>” via the GGSCI utility.

GGSCI (ip-10-0-1-12) 2> edit param ptcustdb

Here’s the Extract Data Pump Parameter (ptcustdb.prm) file used in this example:

EXTRACT ptcustdb
RMTHOST 129.144.0.216, MGRPORT 7777, SOCKSPROXY 127.0.0.1:8888
discardfile ./dirrpt/ptcustdb.dsc, append
rmttrail ./dirdat/pa
passthru
table tcustdb.TCUSTMER;
table tcustdb.TCUSTORD;

Add the remote trail to the Extract Data Pump Group via GGSCI

The remote trail is the location output file on the remote side (GGCS instance) used by the Extract Data Pump to write data to be read by the Replicat Delivery process and apply to the target database on the Oracle Database Cloud Service (DBCS) instance.

GGSCI (ip-10-0-1-12) 3> add rmttrail ./dirdat/pa, extract ptcustdb

RMTTRAIL added.

Start the Extract Data Pump Group via GGSCI

GGSCI (ip-10-0-1-12) 4> start extract ptcustdb

Sending START request to MANAGER …
EXTRACT PTCUSTDB starting

Check the Status of Extract Data Pump Group via GGSCI 

GGSCI (ip-10-0-1-12) 5> info extract ptcustdb detail

EXTRACT PTCUSTDB Last Started 2017-04-29 03:58 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Process ID 11028
Log Read Checkpoint File ./dirdat/ea000000000
First Record RBA 1324
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/pa 0 0 500 RMTTRAIL
Extract Source Begin End
./dirdat/ea000000000 * Initialized * First Record
./dirdat/ea000000000 * Initialized * First Record
Current directory /home/ec2-user/ogg12c_mysql56
Report file /home/ec2-user/ogg12c_mysql56/dirrpt/PTCUSTDB.rpt
Parameter file /home/ec2-user/ogg12c_mysql56/dirprm/ptcustdb.prm
Checkpoint file /home/ec2-user/ogg12c_mysql56/dirchk/PTCUSTDB.cpe
Process file /home/ec2-user/ogg12c_mysql56/dirpcs/PTCUSTDB.pce
Error log /home/ec2-user/ogg12c_mysql56/ggserr.log

GGSCI (ip-10-0-1-12) 6> info all

MANAGER RUNNING
EXTRACT RUNNING ETCUSTDB 00:00:00 00:00:00
EXTRACT RUNNING PTCUSTDB 00:00:00 00:00:03

Configure and Start GGCS Oracle GoldenGate Delivery Process

Connect to GGCS Instance through ssh and go GoldenGate Software Command Interface (GGSCI) utility to configure GoldenGate Delivery process.

mpapio@borg:~$ ssh -i ateam_opc_ssh_key opc@ateam-ggcs-aa

[opc@ateam-ggcs-aa-ggcs-1 ~]$ sudo su – oracle
[oracle@ateam-ggcs-aa-ggcs-1 ~]$ cd $GGHOME

Note: By default, “opc” user is the only one allowed to ssh to GGCS instance. We need to switch user to “oracle” via “su” command to manage the GoldenGate processes. The environment variable $GGHOME is  pre-defined in the GGCS instance and it’s the directory where GoldenGate was installled.

[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160517 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160711.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 12 2016 02:21:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

Configure GGCS Oracle GoldenGate Replicat Online Delivery group via Integrated process

Configure the Replicat Online Delivery group that reads the trail file that the Data Pump writes to and deliver the changes into the Oracle DBCS.

Before configuring the delivery group as an Integrated delivery process, make sure that the GGSCI session is connected to the database via the GGSCI “dblogin” command.

GGSCI (ateam-ggcs-aa-ggcs-1) 1> dblogin useridalias ggcsuser_alias

Successfully logged into database GGPDBAA.

Create / Add the Replicat Delivery group as an Integrated process  and in this example we will name our Replicat Delivery group to RTCUSTDB.

GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 2> add replicat rtcustdb, integrated, exttrail ./dirdat/pa

REPLICAT (Integrated) added.

Note: To edit/create the Replicat Delivery Configuration/Parameter file, you need to execute “edit param <group_name>” via the GGSCI utility.

GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 3> edit param rtcustdb

Here’s the GGCS Replicat Online Delivery Parameter (rtcustdb.prm) file used in this example:

REPLICAT RTCUSTDB
useridalias ggcsuser_alias
— Integrated parameter
DBOPTIONS INTEGRATEDPARAMS (parallelism 2)
DISCARDFILE ./dirrpt/rtcustdb.dsc, APPEND Megabytes 25
ASSUMETARGETDEFS
MAP tcustdb.TCUSTMER, TARGET MP_TCUSTDB.TCUSTMER;
MAP tcustdb.TCUSTORD, TARGET MP_TCUSTDB.TCUSTORD;

Start the GGCS Replicat Online Delivery process via GGCSI 

GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 4> start replicat rtcustdb

Sending START request to MANAGER …
REPLICAT RTCUSTDB starting

Check the Status of GGCS Replicat Online Delivery process via GGSCI 

GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 5> info replicat rtcustdb detail

REPLICAT RTCUSTDB Last Started 2017-04-29 01:20 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 20686
Log Read Checkpoint File ./dirdat/pa000000000
2017-04-29 01:20:38.892962 RBA 0
INTEGRATED Replicat
DBLOGIN Provided, inbound server name is OGG$RTCUSTDB in ATTACHED state
Current Log BSN value: (no data)
Integrated Replicat low watermark: (no data)
(All source transactions prior to this scn have been applied)
Integrated Replicat high watermark: (no data)
(Some source transactions between this scn and the low watermark may have been applied)
Extract Source Begin End
./dirdat/pa000000000 * Initialized * 2017-04-29 01:20
./dirdat/pa000000000 * Initialized * First Record
./dirdat/pa000000000 * Initialized * First Record
Current directory /u02/data/gghome
Report file /u02/data/gghome/dirrpt/RTCUSTDB.rpt
Parameter file /u02/data/gghome/dirprm/rtcustdb.prm
Checkpoint file /u02/data/gghome/dirchk/RTCUSTDB.cpr
Process file /u02/data/gghome/dirpcs/RTCUSTDB.pcr
Error log /u02/data/gghome/ggserr.log

At this point, we now have a complete replication processes between Amazon RDS MySQL database on AWS to Oracle DBaaS on OPC. We have MySQL Native replication from Amazon MySQL RDS (master-source) to EC2 MySQL (slave), then Oracle GoldenGate replication from EC2 MySQL (slave) to Oracle DBaaS (target).

Run Test Transactions

Now we are ready to run some transactions on the Amazon RDS MySQL database (Master Source) and to be replicated by MySQL Native replication into Amazon EC2 MySQL (Intermediate Source) slave database, then OGG replication from EC2 MySQL slave database to  the Oracle DBaaS (Target) on the OPC.

In this example, we start with empty tables on both source and target.

Check of Source Tables (Amazon RDS MySQL Database) via mysql on Amazon EC2 instance

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1303
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;

+———-+
| count(*) |
+———-+
| 0        |
+———-+
1 row in set (0.00 sec)
+———-+
| count(*) |
+———-+
| 0        |
+———-+
1 row in set (0.00 sec)

Check of Intermediate Source Tables (Amazon EC2 MySQL Database) via mysql on Amazon EC2 instance

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h localhost -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;

+———-+
| count(*) |
+———-+
| 0        |
+———-+
1 row in set (0.00 sec)
+———-+
| count(*) |
+———-+
| 0        |
+———-+
1 row in set (0.00 sec)

Check of Target Tables from GGCS Instance

[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ sqlplus mp_tcustdb@target/mp_tcustdb <<EOF
select count(*) from TCUSTMER;
select count(*) from TCUSTORD;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 01:54:25 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Apr 29 2017 01:37:20 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
COUNT(*)
———-
0
SQL>
COUNT(*)
———-
0
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Note: When GGCS instance is provisioned, a default TNS net service name gets created in the tnsnames.ora of the GGCS instance and that is the “target” net service. This is the connection net service name that contains the connection information for the database that was associated to the GGCS instance when it was provisioned. The location of the tnsnames.ora can be found under the /u01/app/oracle/oci/network/admin directory.

Here’s a sample of the tnsnames.ora file that gets generated after the GGCS instance has been provisioned:

#GGCS generated file
target =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ateam-dbcs-aa)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GGPDBAA.a228251.oraclecloud.internal)
)
)

Run Test Transactions on the Source Tables (Amazon RDS MySQL) via mysql on Amazon EC2

Let’s start with insert transactions into the tables – inserting 5 records on each table a total of 10 operations since we have 2 tables.

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1303
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TCUSTMER VALUES (‘WILL’,’BG SOFTWARE CO.’,’SEATTLE’,’WA’);

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO TCUSTMER VALUES (‘JANE’,’ROCKY FLYER INC.’,’DENVER’,’CO’);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TCUSTORD VALUES (‘WILL’,’1994-09-30 15:33:00′,’CAR’,144,17520,3,100);

Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO TCUSTORD VALUES (‘JANE’,’1995-11-11 13:52:00′,’PLANE’,256,133300,1,100);

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO TCUSTMER VALUES (‘DAVE’,’DAVE”S PLANES INC.’,’TALLAHASSEE’,’FL’);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TCUSTMER VALUES (‘BILL’,’BILL”S USED CARS’,’DENVER’,’CO’);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TCUSTMER VALUES (‘ANN’,’ANN”S BOATS’,’SEATTLE’,’WA’);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TCUSTORD VALUES (‘BILL’,’1995-12-31 15:00:00′,’CAR’,765,15000,3,100);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TCUSTORD values (‘BILL’,’1996-01-01 00:00:00′,’TRUCK’,333,26000,15,100);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO TCUSTORD values (‘DAVE’,’1993-11-03 07:51:35′,’PLANE’,600,135000,2,200);

Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;

Query OK, 1 row affected (0.01 sec)

Now, will do an update transactions into the tables – updating 3 records on TCUSTORD table and 1 record on TCUSTMER table, a total of 4 operations.

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1303
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 14000.00 WHERE CUST_CODE = ‘BILL’ AND ORDER_DATE = ‘1995-12-31 15:00:00’ AND PRODUCT_CODE = ‘CAR’ AND ORDER_ID = 765;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 25000.00 WHERE CUST_CODE = ‘BILL’ AND ORDER_DATE = ‘1996-01-01 00:00:00’ AND PRODUCT_CODE = ‘TRUCK’ AND ORDER_ID = 333;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 16520.00 WHERE CUST_CODE = ‘WILL’ AND ORDER_DATE = ‘1994-09-30 15:33:00’ AND PRODUCT_CODE = ‘CAR’ AND ORDER_ID = 144;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE TCUSTMER SET CITY  = ‘NEW YORK’, STATE = ‘NY’ WHERE CUST_CODE = ‘ANN’;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Now, will do a delete transactions from the tables – deleting 2 records from TCUSTORD table,  a total of 2 delete operations.

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1305
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM TCUSTORD WHERE CUST_CODE = ‘DAVE’ AND ORDER_DATE = ‘1993-11-03 07:51:35’ AND PRODUCT_CODE = ‘PLANE’ AND ORDER_ID = 600;

Query OK, 1 row affected (0.00 sec)

mysql> DELETE from TCUSTORD WHERE CUST_CODE = ‘JANE’ AND ORDER_DATE = ‘1995-11-11 13:52:00’ AND PRODUCT_CODE = ‘PLANE’ AND ORDER_ID = 256;

Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Now, let’s just do a simple count via mysql of the final total number of records in our source database (Amazon RDS MySQL).

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1326
Server version: 5.6.34-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;

+———-+
| count(*) |
+———-+
| 5        |
+———-+
1 row in set (0.00 sec)
+———-+
| count(*) |
+———-+
| 3        |
+———-+
1 row in set (0.00 sec)

Now, let’s just see if we have the same number of records in our intermediate source database (EC2 MySQL) by doing a simple count via mysql.

[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h localhost -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

mysql> use tcustdb;

Database changed

mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;

+———-+
| count(*) |
+———-+
| 5        |
+———-+
1 row in set (0.00 sec)
+———-+
| count(*) |
+———-+
| 3        |
+———-+
1 row in set (0.00 sec)

At this point, we have the database in RDS MySQL (Master) and EC2 MySQL (Slave / OGG Source) in-sync via MySQL native replication and we have executed the following operations:

Table Name Insert Update Delete Total Operations Final # of Rows/Records
TCUSTDB.TCUSTMER 5 1 0 6 5
TCUSTDB.TCUSTORD 5 3 2 10 3

A total of 10 inserts, 4 updates, and 2 deletes.

Check Online Change Data Capture Extract process ETCUSTDB Statistics (OGG on Amazon EC2)

Now, let’s check the statistics for our Extract process ETCUSTDB via GGCSI “STATS” command, this should capture and reflect the operations we have just executed on the source tables.

mpapio@borg:~$ ssh -i mp_opc_ssh_key ec2-user@aws-ateam-gg-hub-mysql-aa

Last login: Sat Apr 29 03:13:30 2017
__|  __|_  )
_|   (    / Amazon Linux AMI
___|\___|___|
https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/

[ec2-user@ip-10-0-1-12 ~]$ cd ogg12c_mysql56
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ ./ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (ip-10-0-1-12) 1> stats extract etcustdb, total, table *.*

Sending STATS request to EXTRACT ETCUSTDB …
Start of Statistics at 2017-04-29 17:51:38.
Output to ./dirdat/ea:

Extracting from tcustdb.TCUSTMER to tcustdb.TCUSTMER:
*** Total statistics since 2017-04-29 06:07:53 ***
Total inserts                                  5.00
Total updates                                  1.00
Total deletes                                  0.00
Total discards                                 0.00
Total operations                               6.00

Extracting from tcustdb.TCUSTORD to tcustdb.TCUSTORD:
*** Total statistics since 2017-04-29 06:07:53 ***
Total inserts                                  5.00
Total updates                                  3.00
Total deletes                                  2.00
Total discards                                 0.00
Total operations                              10.00

Check Extract Datapump process PTCUSTDB Statistics (OGG on Amazon EC2)

Now, let’s check the statistics for our Extract Datapump process PTCUSTDB via the same GGCSI “STATS” command, this should also reflect the same number of operations we have just executed on the source tables.

GGSCI (ip-10-0-1-12) 2> stats extract ptcustdb, total, table *.*

Sending STATS request to EXTRACT PTCUSTDB …
Start of Statistics at 2017-04-29 17:57:48.
Output to ./dirdat/pa:

Extracting from tcustdb.TCUSTMER to tcustdb.TCUSTMER:
*** Total statistics since 2017-04-29 06:16:08 ***
Total inserts                                  5.00
Total updates                                  1.00
Total deletes                                  0.00
Total discards                                 0.00
Total operations                               6.00

Extracting from tcustdb.TCUSTORD to tcustdb.TCUSTORD:
*** Total statistics since 2017-04-29 06:16:08 ***
Total inserts                                  5.00
Total updates                                  3.00
Total deletes                                  2.00
Total discards                                 0.00
Total operations                              10.00

Check Online Change Delivery Replicat process RTCUSTDB Statistics (GGCS Instance on the OPC)

Now, let’s check the statistics for our Online Delivery Replicat process RTCUSTDB via the same GGCSI “STATS” command we did for our Extract processes. This should also reflect the same number of operations we have just executed on the source tables and captured by the Extract (ETCUSTDB) process and was sent by Extract Datapump (PTCUSTDB) process.

mpapio@borg:~$ ssh -i ateam_opc_ssh_key opc@ateam-ggcs-aa

[opc@ateam-ggcs-aa-ggcs-1 ~]$ sudo su – oracle
[oracle@ateam-ggcs-aa-ggcs-1 ~]$ cd $GGHOME
[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ ggsci

GGSCI (ateam-ggcs-aa-ggcs-1) 1> dblogin useridalias ggcsuser_alias

Successfully logged into database GGPDBAA.

GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 2> stats replicat rtcustdb, total, table *.*

Sending STATS request to REPLICAT RTCUSTDB …
Start of Statistics at 2017-04-29 14:05:14.
Integrated Replicat Statistics:
Total transactions                      13.00
Redirected                               0.00
DDL operations                           0.00
Stored procedures                        0.00
Datatype functionality                   0.00
Event actions                            0.00
Direct transactions ratio                0.00%

Replicating from tcustdb.TCUSTMER to GGPDBAA.MP_TCUSTDB.TCUSTMER:
*** Total statistics since 2017-04-29 02:17:22 ***
Total inserts                                  5.00
Total updates                                  1.00
Total deletes                                  0.00
Total discards                                 0.00
Total operations                               6.00

Replicating from tcustdb.TCUSTORD to GGPDBAA.MP_TCUSTDB.TCUSTORD:
*** Total statistics since 2017-04-29 02:17:22 ***
Total inserts                                  5.00
Total updates                                  3.00
Total deletes                                  2.00
Total discards                                 0.00
Total operations                              10.00

Now, for the final step, let’s just do a simple count via sqlplus of the final total number of records in our target database on Oracle DBCS and make sure that the result matches the total number of final records in our source database on the Amazon RDS.

[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ sqlplus mp_tcustdb@target/mp_tcustdb <<EOF
> select count(*) from TCUSTMER;
> select count(*) from TCUSTORD;
> EOF

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 14:12:34 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Apr 29 2017 03:22:41 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
COUNT(*)
———-
5
SQL>
COUNT(*)
———-
3
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

Summary

This article walked through the steps to on how to configure Oracle GoldenGate (OGG) replication from Amazon Relational Database Service (RDS) MySQL Database to an Oracle Database Cloud Service (DBCS) on Oracle Public Cloud (OPC) in combination with MySQL Native replication.

 

Special thanks to Chris Esposito for his notes on Amazon Aurora replication which helped on this subject since Aurora database is a MySQL compatible database.

 

Additional Resources:

Oracle Database Cloud Service (DBCS) 

Oracle GoldenGate Cloud Service (GGCS)

GGCS User Guide Documentation

GGCS Tutorial Section

Add Your Comment