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

Introduction

This document will walk you through how to configure Oracle GoldenGate (OGG) replication from Amazon Relational Database Service (RDS) Oracle 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 Oracle GoldenGate for Oracle 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 Oracle Database instance

It is assumed that Amazon EC2 and RDS, together with Oracle GGCS and DBCS have been provisioned and 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:

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 Oracle Database to Oracle DBCS via GGCS

To use Oracle GoldenGate with Amazon RDS for Oracle database replication, you would need to install Oracle GoldenGate software on an Amazon EC2 instance and deploy and configure Oracle GoldenGate Software as a hub. For GoldenGate hub deployment, the database engine and GoldenGate software are both installed on each separate server or instance.

Figure 2: GoldenGate Hub Deployment Replication from Amazon RDS to Oracle DBaaS via GGCS Architecture Diagram

AWS-RDS-DBCS-GGCS_v2

Pre-requisites for Oracle GoldenGate Hub on Amazon EC2

The following are the steps that needs to be done to enable GoldenGate to function as a hub for Amazon RDS Oracle database:

  • Set required Oracle Database Parameters on Amazon RDS Oracle Database
  • Install Oracle Client Software on Amazon EC2
  • Set GoldenGate Variables on Amazon EC2

Set required Oracle Database Paramater on Amazon RDS Oracle Database

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

  • COMPATIBLE parameter set to 11.0.4 or later
  • ENABLE_GOLDENGATE_REPLICATION parameter set to TRUE

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

Install Oracle Client Software on Amazon EC2

For connectivity to Oracle Database on Amazon RDS from Amazon EC2, you can either deploy a full install of the Oracle Database software or install a free lightweight Oracle client named as the Oracle Instant Client.

The Oracle Instant Client software can be downloaded from Oracle Technology Network website, for downloading and installing the client software, see Oracle Instant Client.

Example Installation Session of the Oracle Instant Client Software (Basic Client & SQLPlus Pacakage) once the client install file has been downloaded:

Instant Client Basic Package:

[ec2-user@ip-172-30-3-170 ]cd Downloads
[ec2-user@ip-172-30-3-170 Downloads]$ sudo yum install oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

Loaded plugins: priorities, update-motd, upgrade-helper
Examining oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64
Marking oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm to be installed
Resolving Dependencies
amzn-main/latest | 2.1 kB 00:00
amzn-updates/latest | 2.3 kB 00:00
–> Running transaction check
—> Package oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
=====================================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================================
Installing:
oracle-instantclient12.1-basic x86_64 12.1.0.2.0-1 /oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 185 M
Transaction Summary
=====================================================================================================================================================================
Install 1 Package
Total size: 185 M
Installed size: 185 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 1/1
Verifying : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 1/1
Installed:
oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1
Complete!

Instant Client SQLPlus Pacakage:

[ec2-user@ip-172-30-3-170 Downloads]$ sudo yum install oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

Loaded plugins: priorities, update-motd, upgrade-helper
Examining oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64
Marking oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package oracle-instantclient12.1-sqlplus.x86_64 0:12.1.0.2.0-1 will be installed
–> Finished Dependency Resolution
Dependencies Resolved
=====================================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================================
Installing:
oracle-instantclient12.1-sqlplus x86_64 12.1.0.2.0-1 /oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64 3.0 M
Transaction Summary
=====================================================================================================================================================================
Install 1 Package
Total size: 3.0 M
Installed size: 3.0 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64 1/1
Verifying : oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64 1/1
Installed:
oracle-instantclient12.1-sqlplus.x86_64 0:12.1.0.2.0-1
Complete!

Set GoldenGate Environment Variables on Amazon EC2

  • The following environment variables needs to be set before starting any GoldenGate processes:
  • $GGHOME – directory location where Oracle GoldenGate Software was installed (Optional variable – just good practice to have this variable in place for ease of use)
  • $ORACLE_HOME – Directory location where Oracle Instant Client has been installed
  • $LD_LIBRARY_PATH – Whatever existing value plus the lib directory location of the Oracle Instant Client
  • $PATH – Whatever existing value plus the bin directory location of the Oracle Instant Client
  • $TWO_TASK – Oracle EZConnect String (This is found on Amazon RDS Oracle Endpoint plus the Database Name)

Example Session of setting the Environment Varaibles for GoldenGate:

[ec2-user@ip-172-30-3-170 ~]$ export GGHOME=/home/ec2-user/ogg12c_ora12c
[ec2-user@ip-172-30-3-170 ~]$ export ORACLE_HOME=/usr/lib/oracle/12.1/client64
[ec2-user@ip-172-30-3-170 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[ec2-user@ip-172-30-3-170 ~]$ export PATH=$PATH:$ORACLE_HOME/bin:.
[ec2-user@ip-172-30-3-170 ~]$ export TWO_TASK=mp-gg-rds-aa.cserhpfzdaax.us-east-1.rds.amazonaws.com:1521/MPGGDBAA

For Oracle GoldenGate hub deployment, ORACLE_SID variable is not used since the database is remote, instead we use the variable TWO_TASK to specify a default Oracle Remote service and it uses the EZConnect string.

To find the correct EZConnect string on Amazon RDS Oracle database engine, you will need to locate the RDS Oracle Database Endpoint and the Database name from the Amazon RDS dashboard.

ords_oracle_endpoint_01

 

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

To use Oracle GoldenGate with Amazon RDS for Oracle database replication, you would need to install Oracle GoldenGate software on an Amazon EC2 instance and deploy and configure the software as GoldenGate Hub

The high level steps for OGG replication between Amazon RDS Oracle database (source) and 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 side
  • Configure and Start OGG Extract process on the Amazon side
  • Configure and Start OGG Extract Data Pump process on the Amazon side
  • 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 mp_opc_ssh_key opc@129.145.1.180

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@bics-gg-ggcs-1 ~]$ sudo su – oracle
[oracle@bics-gg-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@bics-gg-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 (bics-gg-ggcs-1) 1> start mgr

Manager started.

GGSCI (bics-gg-ggcs-1) 2> info mgr

Manager is running (IP port bics-gg-ggcs-1.7777, Process ID 4447).

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.145.1.180, 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-mp-gg-hub-aa

Last login: Thu Mar 9 22:38:56 2017
__|  __|_  )
_|   (    / Amazon Linux AMI
___|\___|___|
https://aws.amazon.com/amazon-linux-ami/2016.09-release-notes/

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

[ec2-user@ip-172-30-3-170 ~]$ cd $GGHOME
[ec2-user@ip-172-30-3-170 ogg12c_ora12c]$ ssh -i mp_opc_ssh_key -v -N -f -D 127.0.0.1:8888 opc@129.145.1.180 > ./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-172-30-3-170 ogg12c_ora12c]$ 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.145.1.180 [129.145.1.180] port 22.
debug1: Connection established.
debug1: identity file mp_opc_ssh_key type -1
debug1: identity file mp_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 4f:e7:68:14:1b:59:15:b3:38:0c:fb:b7:17:48:aa:cf
debug1: Host ‘129.145.1.180’ is known and matches the RSA host key.

debug1: Authentication succeeded (publickey).
Authenticated to 129.145.1.180 ([129.145.1.180]: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 following tables for source and target database:

CREATE TABLE ACCTN
(
ACCOUNT_NO NUMBER (10,0) NOT NULL
, BALANCE NUMBER (8,2) NULL
, PREVIOUS_BAL NUMBER (8,2) NULL
, LAST_CREDIT_AMT NUMBER (8,2) NULL
, LAST_DEBIT_AMT NUMBER (8,2) NULL
, LAST_CREDIT_TS TIMESTAMP NULL
, LAST_DEBIT_TS TIMESTAMP NULL
, ACCOUNT_BRANCH NUMBER (10,0) NULL
, CONSTRAINT PK_ACCTN
PRIMARY KEY
(
ACCOUNT_NO
)
USING INDEX
)
;
CREATE TABLE ACCTS
(
ACCOUNT_NO NUMBER (10,0) NOT NULL
, FIRST_NAME VARCHAR2 (25) NULL
, LAST_NAME VARCHAR2 (25) NULL
, ADDRESS_1 VARCHAR2 (25) NULL
, ADDRESS_2 VARCHAR2 (25) NULL
, CITY VARCHAR2 (20) NULL
, STATE VARCHAR2 (2) NULL
, ZIP_CODE NUMBER (10,0) NULL
, CUSTOMER_SINCE DATE NULL
, COMMENTS VARCHAR2 (30) NULL
, CONSTRAINT PK_ACCTS
PRIMARY KEY
(
ACCOUNT_NO
)
USING INDEX
)
;
CREATE TABLE BRANCH
(
BRANCH_NO NUMBER (10,0) NOT NULL
, OPENING_BALANCE NUMBER (8,2) NULL
, CURRENT_BALANCE NUMBER (8,2) NULL
, CREDITS NUMBER (8,2) NULL
, DEBITS NUMBER (8,2) NULL
, TOTAL_ACCTS NUMBER (10,0) NULL
, ADDRESS_1 VARCHAR2 (25) NULL
, ADDRESS_2 VARCHAR2 (25) NULL
, CITY VARCHAR2 (20) NULL
, STATE VARCHAR2 (2) NULL
, ZIP_CODE NUMBER (10,0) NULL
, CONSTRAINT PK_BRANCH
PRIMARY KEY
(
BRANCH_NO
)
USING INDEX
)
;
CREATE TABLE TELLER
(
TELLER_NO NUMBER (10,0) NOT NULL
, BRANCH_NO NUMBER (10,0) NOT NULL
, OPENING_BALANCE NUMBER (8,2) NULL
, CURRENT_BALANCE NUMBER (8,2) NULL
, CREDITS NUMBER (8,2) NULL
, DEBITS NUMBER (8,2) NULL
, CONSTRAINT PK_TELLER
PRIMARY KEY
(
TELLER_NO
)
USING INDEX
)
;

Start Oracle GoldenGate Manager on Amazon EC2 instance

[ec2-user@ip-172-30-3-170 ogg12c_ora12c]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jan 23 2017 23:22:14
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, 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-172-30-3-170) 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-172-30-3-170) 2> start mgr

Manager started.

GGSCI (ip-172-30-3-170) 3> info mgr

Manager is running (IP port ip-172-30-3-170.7777, Process ID 22495).

 

Configure and Start Oracle GoldenGate Extract Online Change Capture process 

Before we can configure the Oracle GoldenGate Extract Online Change process, we need to enable supplemental logging for the schema/tables we need to capture on the source database via the GGCSI utility.

Enable Table Supplemental Logging via GGCSI:

GGSCI (ip-172-30-3-170) 1> dblogin userid tpcadb password tpcadb

Successfully logged into database.

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 2> add schematrandata tpcadb

2017-03-09 23:24:57 INFO OGG-01788 SCHEMATRANDATA has been added on schema tpcadb.
2017-03-09 23:24:57 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema tpcadb.

Note: The GGSCI “dblogin” command let’s the GGSCI session logged into the database. Your GGSCI session needs to be connected to the database before you can execute the “add schematrandata” command.

Create an Online Change Data Capture Extract Group via Integrated Extract process

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

-> Register the Extract group with the database via GGSCI:

GGSCI (ip-172-30-3-170) 1> dblogin userid tpcadb password tpcadb

Successfully logged into database.

Note: When creating/adding/managing an Extract group as an Integrated Extract process, your GGSCI session needs to be connected to the database via the “dblogin” command.

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 2> register extract etpcadb database

2017-03-09 23:41:08 INFO OGG-02003 Extract ETPCADB successfully registered with database at SCN 916618.

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

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 3> add extract etpcadb, integrated, tranlog, begin now

EXTRACT (Integrated) added.

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

GGSCI (ip-172-30-3-170) 1> edit param etpcadb

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

EXTRACT ETPCADB
userid tpcadb, password tpcadb
EXTTRAIL ./dirdat/ea
discardfile ./dirrpt/etpcadb.dsc, append
TABLE TPCADB.ACCTN;
TABLE TPCADB.ACCTS;
TABLE TPCADB.BRANCH;
TABLE TPCADB.TELLER;

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

GGSCI (ip-172-30-3-170) 1> add exttrail ./dirdat/ea, extract etpcadb

EXTTRAIL added.

Start the Online Change Data Capture  Extract Group via GGSCI

GGSCI (ip-172-30-3-170) 2> start extract etpcadb

Sending START request to MANAGER …
EXTRACT ETPCADB starting

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

GGSCI (ip-172-30-3-170) 1> dblogin userid tpcadb password tpcadb

Successfully logged into database.

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 2> info extract etpcadb detail

EXTRACT ETPCADB Last Started 2017-03-09 23:43 Status RUNNING
Checkpoint Lag 00:00:09 (updated 00:00:07 ago)
Process ID 23213
Log Read Checkpoint Oracle Integrated Redo Logs
2017-03-09 23:45:41
SCN 0.935468 (935468)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/ea 0 1434 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.916618 (916618)
Integrated Extract outbound server filtering start scn: 0.916618 (916618)
Extract Source Begin End
Not Available 2017-03-09 23:41 2017-03-09 23:45
Not Available * Initialized * 2017-03-09 23:41
Not Available * Initialized * 2017-03-09 23:41
Current directory /home/ec2-user/ogg12c_ora12c
Report file /home/ec2-user/ogg12c_ora12c/dirrpt/ETPCADB.rpt
Parameter file /home/ec2-user/ogg12c_ora12c/dirprm/etpcadb.prm
Checkpoint file /home/ec2-user/ogg12c_ora12c/dirchk/ETPCADB.cpe
Process file /home/ec2-user/ogg12c_ora12c/dirpcs/ETPCADB.pce
Error log /home/ec2-user/ogg12c_ora12c/ggserr.log

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETPCADB 00:00:09 00:00:01

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 PTPCADB.

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 (ETPCADB) process and sends the data to the GoldenGate process running on the GGCS instance via the SSH Socks Proxy server.

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 4> add extract ptpcadb, 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-172-30-3-170 as tpcadb@MPGGDBAA) 5> edit param ptpcadb

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

EXTRACT PTPCADB
RMTHOST 129.145.1.180, MGRPORT 7777, SOCKSPROXY 127.0.0.1:8888
discardfile ./dirrpt/ptpcadb.dsc, append
rmttrail ./dirdat/pa
passthru
table TPCADB.ACCTN;
table TPCADB.ACCTS;
table TPCADB.BRANCH;
table TPCADB.TELLER;

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-172-30-3-170 as tpcadb@MPGGDBAA) 6> add rmttrail ./dirdat/pa, extract ptpcadb

RMTTRAIL added.

Start the Extract Data Pump Group via GGSCI

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 7> start extract ptpcadb

Sending START request to MANAGER …
EXTRACT PTPCADB starting

Check the Status of Extract Data Pump Group via GGSCI 

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 8> info extract ptpcadb detail

EXTRACT PTPCADB Last Started 2017-03-09 23:57 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 23289
Log Read Checkpoint File ./dirdat/ea000000000
First Record RBA 1434
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_ora12c
Report file /home/ec2-user/ogg12c_ora12c/dirrpt/PTPCADB.rpt
Parameter file /home/ec2-user/ogg12c_ora12c/dirprm/ptpcadb.prm
Checkpoint file /home/ec2-user/ogg12c_ora12c/dirchk/PTPCADB.cpe
Process file /home/ec2-user/ogg12c_ora12c/dirpcs/PTPCADB.pce
Error log /home/ec2-user/ogg12c_ora12c/ggserr.log

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ETPCADB 00:00:09 00:00:07
EXTRACT RUNNING PTPCADB 00:00:00 00:00:01

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 mp_opc_ssh_key opc@129.145.1.180

[opc@bics-gg-ggcs-1 ~]$ sudo su – oracle
[oracle@bics-gg-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@bics-gg-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 BICS 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 (bics-gg-ggcs-1) 1> dblogin useridalias ggcsuser_alias

Successfully logged into database BICSPDB1.

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

GGSCI (bics-gg-ggcs-1 as c##ggadmin@BICS/BICSPDB1) 2> add replicat rtpcadb, 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 (bics-gg-ggcs-1 as c##ggadmin@BICS/BICSPDB1) 3> edit param rtpcadb

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

REPLICAT RTPCADB
useridalias ggcsuser_alias
–Integrated parameter
DBOPTIONS INTEGRATEDPARAMS (parallelism 2)
DISCARDFILE ./dirrpt/rtpcadb.dsc, APPEND Megabytes 25
ASSUMETARGETDEFS
MAP TPCADB.ACCTN, TARGET GGCSBICS.ACCTN;
MAP TPCADB.ACCTS, TARGET GGCSBICS.ACCTS;
MAP TPCADB.BRANCH, TARGET GGCSBICS.BRANCH;
MAP TPCADB.TELLER, TARGET GGCSBICS.TELLER;

Start the GGCS Replicat Online Delivery process via GGCSI 

GGSCI (bics-gg-ggcs-1 as c##ggadmin@BICS/BICSPDB1) 3> start replicat rtpcadb

Sending START request to MANAGER …
REPLICAT RTPCADB starting

Check the Status of GGCS Replicat Online Delivery process via GGSCI 

GGSCI (bics-gg-ggcs-1 as c##ggadmin@BICS/BICSPDB1) 4> info replicat rtpcadb detail

INTEGRATED Replicat
DBLOGIN Provided, inbound server name is OGG$RTPCADB 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-03-09 19:04
./dirdat/pa000000000 * Initialized * First Record
./dirdat/pa000000000 * Initialized * First Record
Current directory /u02/data/gghome
Report file /u02/data/gghome/dirrpt/RTPCADB.rpt
Parameter file /u02/data/gghome/dirprm/rtpcadb.prm
Checkpoint file /u02/data/gghome/dirchk/RTPCADB.cpr
Process file /u02/data/gghome/dirpcs/RTPCADB.pcr
Error log /u02/data/gghome/ggserr.log

At this point, we now have a complete OGG replication processes between the source Oracle database on the Amazon RDS via GoldenGate on Amazon EC2 to the target Oracle database on the OPC via GGCS.

Run Test Transactions

Now we are ready to run some transactions on the Oracle database on the Amazon RDS and to be replicated by the GGCS onto the target database running on the DBCS instance on the OPC.

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

Check of Source Tables (Amazon RDS Oracle Database) via sqlplus on Amazon EC2

[ec2-user@ip-172-30-3-170 ~]$ sqlplus tpcadb/tpcadb <<EOF
select count(*) from ACCTN;
select count(*) from ACCTS;
select count(*) from BRANCH;
select count(*) from TELLER;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 10 00:09:37 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Mar 09 2017 23:45:38 +00: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>
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

Check of Target Tables from GGCS Instance

[oracle@bics-gg-ggcs-1 ~]$ sqlplus ggcsbics@target/ggcsbics <<EOF
select count(*) from ACCTN;
select count(*) from ACCTS;
select count(*) from BRANCH;
select count(*) from TELLER;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 19:10:42 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Mar 09 2017 18:43:27 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL>
COUNT(*)
———-
0
SQL>
COUNT(*)
———-
0
SQL>
COUNT(*)
———-
0
SQL>
COUNT(*)
———-
0
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

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 = BICS-DB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = BICSPDB1.usoracle55293.oraclecloud.internal)
)
)

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

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

[ec2-user@ip-172-30-3-170 ~]$ sqlplus tpcadb/tpcadb <<EOF
INSERT INTO ACCTN (ACCOUNT_NO, BALANCE, PREVIOUS_BAL, LAST_CREDIT_AMT, LAST_CREDIT_TS, ACCOUNT_BRANCH) VALUES ( 83915, 1000, 0, 1000, TO_TIMESTAMP (‘2005-08-18:15:11:37.123456’, ‘YYYY-MM-DD:HH24:MI:SS.FF’), 82);
INSERT INTO ACCTN (ACCOUNT_NO, BALANCE, PREVIOUS_BAL, LAST_CREDIT_AMT, LAST_CREDIT_TS, ACCOUNT_BRANCH) VALUES ( 83916, 1000, 0, 1000, TO_TIMESTAMP (‘2005-08-18:15:11:37.123456’, ‘YYYY-MM-DD:HH24:MI:SS.FF’), 82);
COMMIT WORK;
INSERT INTO ACCTS (ACCOUNT_NO, FIRST_NAME, LAST_NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP_CODE, CUSTOMER_SINCE) VALUES ( 83915, ‘Margarete’, ‘Smith’, ‘222 8th Ave’, ‘ ‘, ‘San Diego’, ‘CA’, 97827, to_date (‘1992-08-18’, ‘YYYY-MM-DD’));
INSERT INTO ACCTS (ACCOUNT_NO, FIRST_NAME, LAST_NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP_CODE, CUSTOMER_SINCE) VALUES ( 83916, ‘Margarete’, ‘Howsler’, ‘1615 Ramona Ave’, ‘ ‘, ‘Fresno’, ‘CA’, 91111, to_date (‘1985-08-18’, ‘YYYY-MM-DD’));
COMMIT WORK;
INSERT INTO TELLER (TELLER_NO, BRANCH_NO, OPENING_BALANCE) VALUES ( 9815, 82, 10000 );
INSERT INTO TELLER (TELLER_NO, BRANCH_NO, OPENING_BALANCE) VALUES ( 9816, 83, 10000 );
COMMIT WORK;
INSERT INTO BRANCH (BRANCH_NO, OPENING_BALANCE, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP_CODE) VALUES ( 82, 100000, ‘7 Market St’, ‘ ‘, ‘Los Angeles’, ‘CA’, 90001);
INSERT INTO BRANCH (BRANCH_NO, OPENING_BALANCE, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP_CODE) VALUES ( 83, 100000, ‘222 8th Ave’, ‘ ‘, ‘Salinas’, ‘CA’, 95899);
COMMIT WORK;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 10 00:15:56 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Mar 10 2017 00:09:37 +00: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>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
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

Now, will do an update transactions into the tables – updating 2 records on each table a total of 8 update operations since we have 4 tables.

[ec2-user@ip-172-30-3-170 ~]$ sqlplus tpcadb/tpcadb <<EOF
UPDATE ACCTN SET BALANCE=25000, PREVIOUS_BAL=1000 WHERE ACCOUNT_NO=83915;
UPDATE ACCTN SET BALANCE=55789, PREVIOUS_BAL=1000 WHERE ACCOUNT_NO=83916;
COMMIT WORK;
UPDATE ACCTS SET FIRST_NAME = ‘Margie’ WHERE ACCOUNT_NO=83915;
UPDATE ACCTS SET FIRST_NAME = ‘Mandela’ WHERE ACCOUNT_NO=83916;
COMMIT WORK;
UPDATE TELLER SET OPENING_BALANCE=99900 WHERE TELLER_NO=9815;
UPDATE TELLER SET OPENING_BALANCE=77777 WHERE TELLER_NO=9816;
COMMIT WORK;
UPDATE BRANCH SET TOTAL_ACCTS = 25000 WHERE BRANCH_NO = 82;
UPDATE BRANCH SET TOTAL_ACCTS = 55789 WHERE BRANCH_NO = 83;
COMMIT WORK;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 10 00:17:52 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Mar 10 2017 00:15:56 +00: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>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
SQL>
1 row updated.
SQL>
1 row updated.
SQL>
Commit complete.
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

Now, will do a delete transactions into the tables – deleting 1 record on each table a total of 4 delete operations since we have 4 tables.

[ec2-user@ip-172-30-3-170 ~]$ sqlplus tpcadb/tpcadb <<EOF
DELETE FROM ACCTN WHERE ACCOUNT_NO = 83916;
DELETE FROM ACCTS WHERE ACCOUNT_NO = 83916;
DELETE FROM TELLER WHERE TELLER_NO = 9816;
DELETE FROM BRANCH where BRANCH_NO = 83;
COMMIT WORK;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 10 00:20:03 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Mar 10 2017 00:17:52 +00: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>
1 row deleted.
SQL>
1 row deleted.
SQL>
1 row deleted.
SQL>
1 row deleted.
SQL>
Commit complete.
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

Now, let’s just do a simple count via sqlplus of the final total number of records in our source database.

[ec2-user@ip-172-30-3-170 ~]$ sqlplus tpcadb/tpcadb <<EOF
select count(*) from ACCTN;
select count(*) from ACCTS;
select count(*) from BRANCH;
select count(*) from TELLER;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 10 00:21:33 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Mar 10 2017 00:20:03 +00: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(*)
———-
1
SQL>
COUNT(*)
———-
1
SQL>
COUNT(*)
———-
1
SQL>
COUNT(*)
———-
1
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

At this point, we have executed the following operations:

Table Name Insert Update Delete Total Operations Final # of Rows/Records
TPCADB.ACCTN 2 2 1 5 1
TPCADB.ACCTS 2 2 1 5 1
TPCADB.TELLER 2 2 1 5 1
TPCADB.BRANCH 2 2 1 5 1

A total of 8 inserts, 8 updates, and 4 deletes.

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

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

GGSCI (ip-172-30-3-170) 1> dblogin userid tpcadb password tpcadb

Successfully logged into database.

GGSCI (ip-172-30-3-170 as tpcadb@MPGGDBAA) 2> stats extract etpcadb, total, table *.*

Sending STATS request to EXTRACT ETPCADB …
Start of Statistics at 2017-03-10 00:24:20.
Output to ./dirdat/ea:

Extracting from TPCADB.ACCTN to TPCADB.ACCTN:
*** Total statistics since 2017-03-09 23:45:02 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.ACCTS to TPCADB.ACCTS:
*** Total statistics since 2017-03-09 23:45:02 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.TELLER to TPCADB.BRANCH:
*** Total statistics since 2017-03-09 23:45:02 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.BRANCH to TPCADB.TELLER:
*** Total statistics since 2017-03-09 23:45:02 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00
End of Statistics.

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

Now, let’s check the statistics for our Extract Datapump process PTPCADB 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-172-30-3-170 as tpcadb@MPGGDBAA) 3> stats extract ptpcadb, total, table *.*

Sending STATS request to EXTRACT PTPCADB …
Start of Statistics at 2017-03-10 00:28:25.
Output to ./dirdat/pa:

Extracting from TPCADB.ACCTN to TPCADB.ACCTN:
*** Total statistics since 2017-03-10 00:16:00 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.ACCTS to TPCADB.ACCTS:
*** Total statistics since 2017-03-10 00:16:00 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.TELLER to TPCADB.TELLER:
*** Total statistics since 2017-03-10 00:16:00 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00

Extracting from TPCADB.BRANCH to TPCADB.BRANCH:
*** Total statistics since 2017-03-10 00:16:00 ***
Total inserts                                 2.00
Total updates                                 2.00
Total deletes                                 1.00
Total discards                                0.00
Total operations                              5.00
End of Statistics.

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

Now, let’s check the statistics for our Online Delivery Replicat process RTPCADB 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 (ETPCADB) process and was sent by Extract Datapump (PTPCADB) process.

GGSCI (bics-gg-ggcs-1) 1> dblogin useridalias ggcsuser_alias

Successfully logged into database BICSPDB1.

GGSCI (bics-gg-ggcs-1 as c##ggadmin@BICS/BICSPDB1) 2> stats replicat rtpcadb, total, table *.*

Sending STATS request to REPLICAT RTPCADB …
Start of Statistics at 2017-03-09 19:31:04.
Integrated Replicat Statistics:
Total transactions                             9.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 TPCADB.ACCTN to BICSPDB1.GGCSBICS.ACCTN:
*** Total statistics since 2017-03-09 19:15:40 ***
Total inserts                                  2.00
Total updates                                  2.00
Total deletes                                  1.00
Total discards                                 0.00
Total operations                               5.00

Replicating from TPCADB.ACCTS to BICSPDB1.GGCSBICS.ACCTS:
*** Total statistics since 2017-03-09 19:15:40 ***
Total inserts                                  2.00
Total updates                                  2.00
Total deletes                                  1.00
Total discards                                 0.00
Total operations                               5.00

Replicating from TPCADB.TELLER to BICSPDB1.GGCSBICS.TELLER:
*** Total statistics since 2017-03-09 19:15:40 ***
Total inserts                                  2.00
Total updates                                  2.00
Total deletes                                  1.00
Total discards                                 0.00
Total operations                               5.00

Replicating from TPCADB.BRANCH to BICSPDB1.GGCSBICS.BRANCH:
*** Total statistics since 2017-03-09 19:15:40 ***
Total inserts                                  2.00
Total updates                                  2.00
Total deletes                                  1.00
Total discards                                 0.00
Total operations                               5.00
End of Statistics.

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@bics-gg-ggcs-1 ~]$ sqlplus ggcsbics@target/ggcsbics <<EOF
select count(*) from ACCTN;
select count(*) from ACCTS;
select count(*) from BRANCH;
select count(*) from TELLER;
EOF

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 9 19:34:14 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Mar 09 2017 19:13:09 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL>
COUNT(*)
———-
1
SQL>
COUNT(*)
———-
1
SQL>
COUNT(*)
———-
1
SQL>
COUNT(*)
———-
1
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.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) Oracle Database to an Oracle Database Cloud Service (DBCS) on Oracle Public Cloud (OPC) via Oracle GoldenGate Cloud Service (GGCS).

Additional Resources:

Oracle Database Cloud Service (DBCS) 

Oracle GoldenGate Cloud Service (GGCS)

GGCS User Guide Documentation

GGCS Tutorial Section

Add Your Comment