X

Best Practices from Oracle Development's A‑Team

GoldenGate Cloud Service (GGCS): Replication from On-Premises to Oracle Public Cloud (OPC) via Oracle Cloud Gateway VPN

Mike Papio
Principal Solution Architect

Introduction

This document will walk you through how to configure Oracle GoldenGate (OGG) replication between On-Premises Oracle Database to an Oracle Database Cloud Service (DBCS) on Oracle Public Cloud (OPC) via Oracle Cloud Gateway Virtual Private Network (VPN) using Corente Services Gateway.

Installation of Oracle GoldenGate for Oracle Database on the On-Premises and the provisioning of Oracle GGCS and DBCS are not discussed in this article. It is assumed that the OGG software on the On-Premises has been installed and the instances for GGCS and DBCS on the OPC already exist.

The concepts, scripts and information presented 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. Before applying any changes presented in this article to your environment, you should thoroughly test to assess functionality and performance implications.

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

Main Article

The GoldenGate Cloud Service (GGCS), is a cloud based real-time data integration and replication service, which 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.

 

GoldenGate Cloud Service (GGCS) Architecture Diagram:

GGCS_Architecture_v2

In a typical implementation of On-Premises to GGCS, the connectivity is accomplished through the use of SSH, since this is the only port opened by default on the cloud. The On-Premises server communicates directly to the GGCS server through the use of SOCKS proxy.

However, in cases where the security policy dictates or the client doesn’t want to use SSH, as an alternative a VPN connection between On-Premises and the OPC can be used.

During the writing of this article, there are currently four general ways you can configure and setup VPN connection between On-Premises and OPC based on the Oracle Cloud subscription type (multi-tenant or dedicated) and those are as follows:

(01) Configure VPN connection via Oracle Cloud Gateway VPN using Corente Services Gateway (CSG) - can be used for both multi-tenant and dedicated compute

(a) CSG OPC via On-Premises Corente Services Gateway VPN server

(b) CSG OPC via On-PremisesThird-Party VPN device

(02) Configure VPN connection via VPNaaS (VPN as a Service) - can be used for both multi-tenant and dedicated compute

(03) Configure VPN Connection via VPN Dedicated Compute - Used for only dedicated compute

(04) Configure VPN connection via FastConnect - can be used for both multi-tenant and dedicated compute

In this article, we will just focus and demonstrate on how to configure VPN connection with GGCS between On-Premises and OPC using the first configuration type --- VPN connection via Corente Services Gateway (CSG) using On-Premises Corente Services Gateway VPN server.

For configuring VPN connection via CSG using On-Premises Third-Party VPN device, check the following Oracle documentation link:

Setting Up CSG VPN Using a Third-Party VPN Device

For configuring VPN connection via VPNaaS, check the following Oracle documentation link:

Setting Up a VPN Connection Using VPNaaS

For configuring VPN connection via VPN dedicated compute, check the following Oracle documentation link:

Setting up VPN Connection via VPN Dedicated Compute Gateway

For configuring VPN connection via FastConnect Service, check the following Oracle documentation link:

Getting Started and Provisioning FastConnect Service

 

Corente VPN Service Architecture Diagram:

corente_architecture_v4

 

GGCS Corente VPN Deployment Architecture diagram depicted in this article:

mask_appnet_mgr_04

GoldenGate Connectivity Flow:

  • On-Premises Network to OPC Network: GGCS Instance can be reached via GRE IP address 172.16.201.3
  • OPC Network to On-Premises Network: On-Premise OGG VM Server can be reached via IP address 192.168.201.51

On-Premises Corente Services Gateway (CSG) Setup

In this example, we will use and configure a ViirtualBox VM to run the Corente Services Gateway for our On-Premises network. For setting up Corente Services Gateway on a virtual machine other than VirtualBox, such as KVM (Kernel-based Virtual Machine) for Linux on x86 hardware, check the following Oracle documentation link:

Setting Up Corente Services Gateway in Your Data Center

We will need to download the Corente ISO image file to be used to boot and configure our VirtualBox VM. The Corente ISO image file that was used in this example has been downloaded from this link - http://download.oracle.com/otn/java/cloud-service/gateway9.4.141a.zip . For an updated version of the image file, you can check the following link - http://www.oracle.com/technetwork/topics/cloud/downloads/network-cloud-service-2952583.html.

Corente Services Gateway (CSG) VM VirtualBox Configuration

We need to configure the On-Premise CSG VM Server with the following VirtualBox setting:

mpapio-ggcs-corente-setup-03-CSG_VM__Setup_final_csg_vm_setup_06

Network Adapter 1 - First Network Adapter (WAN) Setting: Make a note of the MAC address for this adapter, this will be needed for the Corente Zero Touch configuration. Write down the MAC address in the following format: XX:XX:XX:XX:XX:XX

mpapio-ggcs-corente-setup-03-CSG_VM__Setup_final_csg_vm_setup_04

Second Network Adapter (LAN) Setting: Make a note of the Internal Network Name you used – this needs to match with the internal network name of the other VirtualBox machines that you want to associate with the same network.

mpapio-ggcs-corente-setup-03-CSG_VM__Setup_final_csg_vm_setup_05

Storage Setting: Make sure the local hard disk is at least 50GB and the ISO image that was downloaded has been attached as a Virtual CD.

mpapio-ggcs-corente-setup-03-CSG_VM__Setup_final_csg_vm_setup_03

DO NOT START the VIrtualbox yet, we will start it later. We will need to define the Corente Gateway configuration for this VIrtualBox using the App Net Manager as the On-Premises CSG gateway.

 

App Net Manager CSG Configuration - On-Premises CSG Gateway

In defining or managing the configuration of Corente Gateways, we use the App Net Manager Application. The App Net manager application can be downloaded or started from this link - https://www.corente.com/appnet

The App Net Manager application requires Java Web Start application. If you don’t have Java Web Start installed on your system you can download it from this link - http://java.sun.com/products/javawebstart/download.jsp

There are two ways you can start the App Net Manager, you can either click the URL directly that points to the App Net Manager application (https://www.corente.com/appnet) from your web browser or you can start it from a command line via the javaws command passing the URL that contains the App Net Manager.

To start App Net Manager from command line, type: javaws https://www.corente.com/appnet

From the App Net Manage Login Screen, provide your Corente VPN Credentials, then click “Login” to sign-in:

mask_appnet_mgr_01

At this point, you should have an empty configuration, to start configuring or adding the first CSG gateway for the On-Premises start the Location Wizard by clicking "File", then "Wizard", then "Location"

mask_appnet_mgr_02

Before continuing, you should have the following info handy or answers to the following questions; since this information will be needed along the way:

Name of the Location Gateway – This can only contain digits, letters, underscores and hyphens

Location Address – Street, City, State, Zip Code, Country & Time Zone

MAC Address of the On-Premises Corente Gateway VM (1st Network Adapter (WAN) in this example - 08:00:27:C2:F5:21)

LAN Interface IP Address (2nd Network Adapter Setting in this example) - This will be the Gateway IP address that the other machines on your On-Premise network will use to connect and use the Corente VPN connection to the OPC network.

 

From the Location Wizard, do the following:

(01) Click Next in the Welcome to the Add Location Wizard screen

(02) Enter the Name of the Location Gateway for your On-Premises Gateway in the Location Name screen - in this example will use OnPrem-Net-192-168-201-0

(03) Fill out the Location Address and Time Zone in the Location Address and Time Zone screen

(04) Click Next in the Automatic Reboot screen

(05) Click Next in the Software Upgrade Window screen

(06) Check the "Enable Zero Touch Configuration" box and enter the MAC Address of the On-Premises CSG VM in the "Unique Identifier" box in the Zero Touch Configuration screen, then click Next - in this example will use 08:00:27:C2:F5:21

(07) Select "Inline Configuration" in the Location configuration screen, then click Next

(08) Select "DHCP" in the WAN Interface Connection Type screen, then click Next

(09) Click Next in the DHCP Client Name screen

(10) Enter the LAN Interface IP Address of the On-Premises Gateway in the LAN Interface IP Address screen, then click Next - in this example this will be the 2nd Network adapter setting of the On-Premises CSG gateway 192.168.201.1

(11) Click Yes to add the whole subnet to the Default User Group in the Add Address Range screen

(12) Check the "Get DNS Dynamically" box in the Dynamic DNS Retrieval screen, then click Next

(13) Click Next in the Generic Routing Encapsulation (GRE) Tunnels screen

(14) Click Next in the Default User Group screen

(15) Click Next in the Firewall Policy screen

(16) Click Next in the Address Ranges screen

(17) Click Finish in the New Location Complete screen

(18) Click the Save button icon to apply the changes and then click Start button to commit all changes

(19) Click Finished in the Save All Changes screen

At this point, the new configuration will appear on the map and under the location section. It will have a “Down Arrow” Icon which means configuration for this gateway location is available for download.

mask_appnet_mgr_03

Once we initialized the On-Premises CSG VM and boots up, it will automatically download this location via the Zero Touch configuration using the MAC address of the VM that we supplied in this configuration.

CSG VM VirtualBox System Initialization and Startup

Go to your VirtualBox and boot-up the CSG VM that has been configured to start initializing the Corente VM:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_01

The CSG VirtualBox VM will start and since we attached the ISO image to the Virtual CD, it will boot up from the Virtual CD using the ISO image we have downloaded. Once the VM boots up from the CD, it will ask if you want to continue on with the Corente Installation from the CD. Type “yes” to continue to initialize the VM and install the Corente Software:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_03

It will continue on with the boot process, and will unpack the images form the CD Rom. It will start the installation process by installing series of packages on the system and will end by running a post installation scripts before it reboots:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_06

Once the post-installation script has completed, it will put out a completion message and will ask you to click reboot to continue. DO NOT CLICK REBOOT yet, since the CD Rom image has to be ejected or detached in this case:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_08

Eject or Detached the CD Rom from the CSG VM, once the CD has been detached/ejected, then you can continue on with the Reboot process:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_09

When CSG VM reboots, it will try to connect to the Corente Service Control Point (SCP), and will download available configuration file via the Zero Touch configuration using the MAC address of the WAN interface that we have supplied earlier in the process:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_10

When the CSG reboots and finds the available configuration file, the orange “Down Arrow” icon in the App Net Manager application will change into a gray icon which indicates that the config file has been downloaded and the CSG VM will continue on with its configuration and will do a series of reboots:

mask_appnet_mgr_06

 

A series of reboot will take place on the CSG VM and the last reboot during the CSG initialization process will start a series of services:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_13

Once you see the Login Screen Message on the CSG VM, this indicates that the Corente system initialization and configuration completed:

mpapio-ggcs-corente-setup-05-CSG_VirtualBox_final_csg_vm_boot_15

At this point, the CSG has been initialized, configured and it's now up and running. On the App Net Manager, the CSG icon has now been turned from Gray into Green indicating that the CSG is up and running and in active state:

mask_appnet_mgr_07

 

Oracle Public Cloud (OPC) Corente Services Gateway Setup

On the OPC side, you have to create a Cloud Gateway via the VPN Gateway functionality from the Cloud console menu, this will create the Corente Services instance on the OPC.

To setup the Cloud Gateway do the following: (Note: User must have the Compute_Operations role to complete this task)

(01) Sign in to the Compute Classic console.

(02) Click the Network tab.

(03) Click the VPN tab in the left pane, then click Corente and then click VPN Gateways.

(04) Click Create VPN Gateway.

(05) Enter the Required information - for our example will enter the following info:

(a) Name: shared-net-vpn-csg

(b) IP Reservation: shared-net-vpn-csg-ip (This is the public IP address of the OPC VPN Gateway)

(c) Image: vpnServiceGateway_corente_9.4.3035 (Select the most recent Corente Gateway image)

(d) Interface Type:  Single-homed (This is normally use for Shared Network and uses GRE tunnels)

(e) Subnets: 172.16.201.0/24 (These are for the OPC GRE subnet - 172.16.201.0/24)

A Corente Services Gateway instance is created. The required orchestrations are created and started automatically. In our example, since  the name we specified for the Corente Gateway instance is shared-net-vpn-csg, then the following orchestrations are created:

  • vpn-shared-net-vpn-csg-bootvol: This orchestration creates the persistent bootable storage volume.
  • vpn-shared-net-vpn-csg-secrules: This orchestration creates the required security list, security applications, and security rules.
  • vpn-shared-net-vpn-csg-master: This orchestration specifies relationships between each of the nested orchestrations and starts each orchestration in the appropriate sequence.

While the Corente Services Gateway instance is being created, the instance status displayed in the Instance column on the VPN Gateways page is Starting. When the instance is created, its status changes to Ready.

At this point, the Corente Services Gateway (CSG) for both On-Premises (OnPrem-Net-192-168-201-0) and OPC Cloud Gateway (shared-net-vpn-csg) in the App Net Manager should both be in active state and both of the icons should be green:

appnet_mgr_09

 

Establishing Partnership Between On-Premises Gateway and Cloud Gateway

After verifying that your on-premises gateway and cloud gateway are running, you must add partnership between the two gateways.

Do the following:

(01) Log in to App Net Manager.
(02) In App Net Manager, in the Domains pane, click Locations to expand and show all of your gateways.
(03) Select your Corente Services Gateway cloud instance (shared-net-vpn-csg) and click to expand.
(04) Click the Partner option under your Corente Services Gateway cloud instance in App Net Manager.
(05) Click New at the top of the App Net Manager screen.
(06) Select Intranet in the Connection to Partner panel, and then select your corporate gateway (On-Prem-Net-192-168-201-0) in the drop-down (right side of your selection).
(07) Click Add at the bottom of the Tubes pane at the bottom of the Add Partner screen.
(08) In the Local Side of Tube pane in the Add Tube screen, select Default User Group in the User Group selector.
(09) In the Remote Side of Tube pane in the Add Tube screen, select Default User Group in the User Group selector.
(10) Leave all other settings at the defaults.
(11) Click OK in the Add Tube screen.
(12) Click OK in the Add Partner screen.
(13) Select your corporate Corente Services Gateway (On-Prem-Net-192-168-201-0) in the Locations in the Domains pane of App Net Manager and click to expand.
(14) Select Partners under your corporate Corente Services Gateway.
(15) Click New at the top of the App Net Manager screen.
(16) Select Intranet in the Connection to Partner panel, and then select your cloud gateway (shared-net-vpn-csg) in the drop-down next to your selection.
(17) Click Add at the bottom of the Tubes pane at the bottom of the Add Partner screen
(18) In the Local Side of Tube pane in the Add Tube screen, select Default User Group in the User Group selector.
(19) In the Remote Side of Tube pane in the Add Tube screen, select Default User Group in the User Group selector.
(20) Leave all other settings at the defaults.
(21) Click OK in the Add Tube screen.
(22) Click OK in the Add Partner screen.
(23) Click Save at the top of the App Net Manager screen.
(24) Click Start in the Save screen.
(25) Click Finished in the Save screen.

You should now see a connection line appear between the gateways in App Net Manager. You’ll see a yellow line first, then the line turns green as the tunnel becomes active.

appnet_mgr_12

 

CSG GRE Tunnel Setup between GGCS server and On-Premises OGG server

Having the tunnel configured for both On-Premises and OPC Cloud gateways, we can start adding instances on the OPC side that wants to connect to your On-Premise network via the established partnership tunnel that was defined. In this example, will add the GGCS instance (shared-net-ggcs-mp) already provisioned in the domain. To add  the GGCS instance shared-net-ggcs-mp to start using the established partnership tunnel, the following needs to be done:

(01) Associate the OPC Cloud Gateway CSG default csg-internal Security Rule to the Instance you want to add. In our example the default csg-internal security rule name is vpn-shared-net-vpn-csg-csg-internal and the instance that needs to be added is the GGCS instance shared-net-ggcs-mp.

(02) Create the GRE tunnel from the instance that needs to be added to the OPC Cloud Gateway CSG. In our example, it's the GGCS instance shared-net-ggcs-mp.

GGCS Instance CSG Security Rule Setup

To associate or setup the Cloud Gateway CSG Security rule csg-internal  to GGCS instance do the following: (Note: User must have the Compute_Operations role to complete this task)

(01) Sign in to the Compute Classic console.

(02) Click the Instance tab.

(03) Click the GGCS instance you want to add the CSG security rule, in our example it's shared-net-ggcs-mp.

(04) Click Add Security List on the right side under the Security Lists section.

(05) Select the correct csg-internal security rule to add, in our example it's vpn-shared-net-vpn-csg-csg-internal, then click Add.

To check if the secuirty rule for the Cloud Gateway CSG has been added and working correctly for the GGCS instance, ping the Cloud Gateway CSG VM host from the GGCS VM instance. In our example, the Cloud Gateway CSG VM host name is vpn-shared-net-vpn-csg-csg.

[opc@shared-net-ggcs-mp-ggcs-1 ~]$ ping vpn-shared-net-vpn-csg-csg
PING vpn-shared-net-vpn-csg-csg.compute-usoracle86702.oraclecloud.in (10.31.41.98) 56(84) bytes of data.
64 bytes from vpn-shared-net-vpn-csg-csg.compute-usoracle86702.oraclecloud.internal (10.31.41.98): icmp_seq=1 ttl=62 time=1051 ms
64 bytes from vpn-shared-net-vpn-csg-csg.compute-usoracle86702.oraclecloud.internal (10.31.41.98): icmp_seq=2 ttl=62 time=51.6 ms
64 bytes from vpn-shared-net-vpn-csg-csg.compute-usoracle86702.oraclecloud.internal (10.31.41.98): icmp_seq=3 ttl=62 time=0.376 ms

GGCS Instance GRE Tunnel Setup

Now that the correct “csg-internal” security list has been added to the GGCS compute instance, the last thing that needs to be done to complete the VPN setup between On-Premises and the OPC and start communicating from GGCS to the on-premises network is to configure a Generic Routing Encapsualtion (GRE) tunnel between GGCS instance and the Cloud Gateway CSG instance.

To create a GRE tunnel on the GGCS instance, do the following:

(01) SSH to the GGCS instance

(02) Create opc-compute directory in /var/log for Corente log files.

[opc@shared-net-ggcs-mp-ggcs-1 ~]$ cd /var/log
[opc@shared-net-ggcs-mp-ggcs-1 ~]$ sudo mkdir opc-compute

(03) Download the oc-config-corente-tunnel script onto the GGCS instance. This script is included in Greconf_orchsamples.zip file which is available at the following location:

http://www.oracle.com/technetwork/topics/cloud/downloads/network-cloud-service-2952583.html

(04) Extract the contents of the greconf_orchsamples.zip file.

(05) After extracting, copy the oc-config-corente-tunnel file from the Config and Orchestration directory to the /usr/bin directory.

Note: You'll need superuser privileges to copy to /usr/bin

(06) Make the oc-config-corente-tunnel script executable:

[opc@shared-net-ggcs-mp-ggcs-1 ~]$ sudo chmod 550 /usr/bin/oc-config-corente-tunnel

(07) Run the following commands:

[opc@shared-net-ggcs-mp-ggcs-1 ~]$ sudo bash
[opc@shared-net-ggcs-mp-ggcs-1 ~]# nohup /usr/bin/oc-config-corente-tunnel --local-tunnel-address=172.16.201.3 --csg-hostname=vpn-shared-net-vpn-csg-csg.compute-usoracle86702.oraclecloud.internal --csg-tunnel-address=172.16.254.1 --onprem-subnets=192.168.201.0/24 > /tmp/oc-config-corente-tunnel.log 2>&1 &

Note:You may have to wait up to 1 - 3 minutes before the GRE tunnel is up. For a description of the configuration parameters for the oc-config-corente-tunnel script, see About Configuration Script Arguments

(08) Verify that the GRE tunnel is functional by running the ping command to the OGG server IP address within your on-premises network directly. In our example, the On-Premises OGG server IP address is 192.168.201.51

[root@shared-net-ggcs-mp-ggcs-1 ~]# ping 192.168.201.51
PING 192.168.201.51 (192.168.201.51) 56(84) bytes of data.
64 bytes from 192.168.201.51: icmp_seq=1 ttl=62 time=62.9 ms
64 bytes from 192.168.201.51: icmp_seq=2 ttl=62 time=64.4 ms

(09) From the On-Premises side, verify that the GRE tunnel is functional by running the ping command from the On-Premises OGG server to the GGCS server GRE IP address in the OPC. In our example, the GGCS server GRE IP address is 172.16.201.3

[oracle@ogg-wkshp ~]$ ping 172.16.201.3
PING 172.16.201.3 (172.16.201.3) 56(84) bytes of data.
64 bytes from 172.16.201.3: icmp_seq=1 ttl=62 time=63.2 ms
64 bytes from 172.16.201.3: icmp_seq=2 ttl=62 time=63.1 ms

 

OGG Replication between On-Premises OGG Server and OPC GGCS Server via VPN

With VPN, the replication setup between On-Premises and OPC doesn't use SOCKS proxy anymore, it could be just configured similar with OGG On-Premises replication setup. The high level steps for OGG and GGCS replication between On-Premises (source) database and DBaaS/DBCS (target) database in the OPC via VPN are as follows:

  • Configure and Start GGCS Oracle GoldenGate Manager on the OPC side
  • Configure and Start On-Premises OGG Extract process
  • Configure and Start On-Premises OGG Extract Data Pump process
  • Configure and Start GGCS Replicat process on the OPC side to deliver data into the target DBaaS/DBCS

GGCS Oracle GoldenGate Manager

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

[opc@shared-net-ggcs-mp-ggcs-1 ~]$ sudo su - oracle
[oracle@shared-net-ggcs-mp-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@shared-net-ggcs-mp-ggcs-1 gghome]$ 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. GGSCI (shared-net-ggcs-mp-ggcs-1) 1> start mgr Manager started. GGSCI (shared-net-ggcs-mp-ggcs-1) 2> info mgr Manager is running (IP port shared-net-ggcs-mp-ggcs-1.7777, Process ID 109143). GGSCI (shared-net-ggcs-mp-ggcs-1) 3> exit [oracle@shared-net-ggcs-mp-ggcs-1 gghome]$

Note: By default, GoldenGate processes doesn’t accept any connection remotely. To enable connection from the On-Premises OGG server, we need to add an ACCESS RULE to the Manager parameter File (MGR.prm) to allow connectivity from the On_Premises OGG server IP address.

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

–###############################################################
–## MGR.prm
–## Manager Parameter Template
— Manager port number
— PORT
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 192.168.201.51, 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 , WAITMINUTES , RESETMINUTES
— 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
–LAGCRITICALMINUTES
–Reports down processes
–DOWNREPORTMINUTES
–DOWNCRITICAL

 

Configure On-Premises Oracle GoldenGate

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 On-Premises Oracle GoldenGate Manager

[oracle@ogg-wkshp db_1]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.8 21205662 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150620.0201_FBO Linux, x64, 64bit (optimized), Oracle 12c on Jun 20 2015 10:57:23 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (ogg-wkshp.us.oracle.com) 1> start mgr Manager started. GGSCI (ogg-wkshp.us.oracle.com) 2> info mgr Manager is running (IP port ogg-wkshp.us.oracle.com.7089, Process ID 19098).

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 (ogg-wkshp.us.oracle.com) 3> dblogin userid tpcadb password tpcadb Successfully logged into database.
GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 4> add schematrandata tpcadb 2017-11-07 23:24:50 INFO OGG-01788 SCHEMATRANDATA has been added on schema tpcadb. 2017-11-07 23:24:50 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 (ogg-wkshp.us.oracle.com) 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 (ogg-wkshp.us.oracle.com as tpcadb@oracle) 2> register extract etpcadb database Extract ETPCADB successfully registered with database at SCN 2478406.

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

GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 3> add extract etpcadb, integrated, tranlog, begin now EXTRACT added.

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

GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 4> 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
GGGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 5> add exttrail ./dirdat/ea, extract etpcadb EXTTRAIL added.
Start the Online Change Data Capture  Extract Group via GGSCI
GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 6> start extract etpcadb Sending START request to MANAGER ... EXTRACT ETPCADB starting
Check the Status of Online Change Data Capture  Extract Group via GGSCI
GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 7> info extract etpcadb detail</strong> EXTRACT ETPCADB Last Started 2017-11-07 23:29 Status RUNNING Checkpoint Lag 00:00:09 (updated 00:00:03 ago) Process ID 7200 Log Read Checkpoint Oracle Integrated Redo Logs  2017-11-07 23:30:52  SCN 0.2497654 (2497654)  Target Extract Trails:  Trail Name Seqno RBA Max MB Trail Type  ./dirdat/ea 0 1450 100 EXTTRAIL Integrated Extract outbound server first scn: 0.2478406 (2478406) Integrated Extract outbound server filtering start scn: 0.2478406 (2478406)  Extract Source Begin End  Not Available 2017-11-07 23:27 2017-11-07 23:30  Not Available * Initialized * 2017-11-07 23:27  Not Available * Initialized * 2017-11-07 23:27 Current directory /u01/app/oracle/product/12cOGG/db_1 Report file /u01/app/oracle/product/12cOGG/db_1/dirrpt/ETPCADB.rpt Parameter file /u01/app/oracle/product/12cOGG/db_1/dirprm/etpcadb.prm Checkpoint file /u01/app/oracle/product/12cOGG/db_1/dirchk/ETPCADB.cpe Process file /u01/app/oracle/product/12cOGG/db_1/dirpcs/ETPCADB.pce Error log /u01/app/oracle/product/12cOGG/db_1/ggserr.log GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 8> info all</strong> Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ETPCADB 00:00:09 00:00:07

Configure and Start Oracle GoldenGate Extract Data Pump process 

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 VPN using the GGCS GRE IP address.

GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 9> add extract ptpcadb, exttrailsource ./dirdat/ea EXTRACT added.

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

GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 10> edit param ptpcadb

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

EXTRACT PTPCADB
RMTHOST 172.16.201.3, MGRPORT 7777
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 (ogg-wkshp.us.oracle.com as tpcadb@oracle) 10> add rmttrail ./dirdat/pa, extract ptpcadb RMTTRAIL added.
Start the Extract Data Pump Group via GGSCI
GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 12> start extract ptpcadb Sending START request to MANAGER ... EXTRACT PTPCADB starting

Check the Status of Extract Data Pump Group via GGSCI 

GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 13> info extract ptpcadb, detail EXTRACT PTPCADB Last Started 2017-11-07 23:47 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Process ID 10922 Log Read Checkpoint File ./dirdat/ea000000  First Record RBA 1450  Target Extract Trails:  Trail Name Seqno RBA Max MB Trail Type  ./dirdat/pa 0 0 100 RMTTRAIL  Extract Source Begin End  ./dirdat/ea000000 * Initialized * First Record  ./dirdat/ea000000 * Initialized * First Record Current directory /u01/app/oracle/product/12cOGG/db_1 Report file /u01/app/oracle/product/12cOGG/db_1/dirrpt/PTPCADB.rpt Parameter file /u01/app/oracle/product/12cOGG/db_1/dirprm/ptpcadb.prm Checkpoint file /u01/app/oracle/product/12cOGG/db_1/dirchk/PTPCADB.cpe Process file /u01/app/oracle/product/12cOGG/db_1/dirpcs/PTPCADB.pce Error log /u01/app/oracle/product/12cOGG/db_1/ggserr.log GGSCI (ogg-wkshp.us.oracle.com as tpcadb@oracle) 14> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ETPCADB 00:00:09 00:00:02 EXTRACT RUNNING PTPCADB 00:00:00 00:00:05

Configure and Start GGCS Oracle GoldenGate Delivery Process

Connect to GGCS Instance through ssh and before configuring the GoldenGate delivery group as an Integrated delivery process, make sure that the GGSCI session is connected to the database via the GGSCI “dblogin” command.

GGSCI (shared-net-ggcs-mp-ggcs-1) 1> dblogin useridalias ggcsuser_alias Successfully logged into database.

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

GGSCI (shared-net-ggcs-mp-ggcs-1 as oggtrg@oracle) 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 ” via the GGSCI utility.

GGSCI (shared-net-ggcs-mp-ggcs-1 as oggtrg@oracle) 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 OGGTRG.ACCTN;
MAP TPCADB.ACCTS, TARGET OGGTRG.ACCTS;
MAP TPCADB.BRANCH, TARGET OGGTRG.BRANCH;
MAP TPCADB.TELLER, TARGET OGGTRG.TELLER;

Start the GGCS Replicat Online Delivery process via GGCSI 

GGSCI (shared-net-ggcs-mp-ggcs-1 as oggtrg@oracle) 4> start replicat rtpcadb Sending START request to MANAGER ... REPLICAT RTPCADB starting

Check the Status of GGCS Replicat Online Delivery process via GGSCI 

GGSCI (shared-net-ggcs-mp-ggcs-1 as oggtrg@oracle) 5> info replicat rtpcadb detail REPLICAT RTPCADB Last Started 2017-11-08 18:36 Status RUNNING INTEGRATED Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Process ID 112419 Log Read Checkpoint File ./dirdat/pa000000  2017-11-08 16:29:18.933998 RBA 12569 INTEGRATED Replicat DBLOGIN Provided, inbound server name is OGG$RTPCADB in ATTACHED state Current Log BSN value: (no data) Integrated Replicat low watermark: 2537143 (All source transactions prior to this scn have been applied) Integrated Replicat high watermark: 2537145 (Some source transactions between this scn and the low watermark may have been applied)  Extract Source Begin End  ./dirdat/pa000000 * Initialized * 2017-11-08 16:29  ./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

Test and Verify Data Replication

Generate source database transactions to test data replication and verify OGG replication statistics on both Extract/Pump (On-Premises Source) and Replicat (OPC Target).

OGG On-Premises (Source) Statistics:

GGSCI (ogg-wkshp.us.oracle.com) 8> stats extract etpcadb, total Sending STATS request to EXTRACT ETPCADB ... Start of Statistics at 2017-11-08 10:39:35. DDL replication statistics (for all trails): *** Total statistics since extract started ***  Operations 1.00 Output to ./dirdat/ea: Extracting from TPCADB.ACCTN to TPCADB.ACCTN: *** Total statistics since 2017-11-08 10:30:07 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Extracting from TPCADB.ACCTS to TPCADB.ACCTS: *** Total statistics since 2017-11-08 10:30:07 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Extracting from TPCADB.TELLER to TPCADB.TELLER: *** Total statistics since 2017-11-08 10:30:07 ***  Total inserts 9.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 11.00 Extracting from TPCADB.BRANCH to TPCADB.BRANCH: *** Total statistics since 2017-11-08 10:30:07 ***  Total inserts 10.00  Total updates 10.00  Total deletes 2.00  Total discards 0.00  Total operations 22.00 End of Statistics. GGSCI (ogg-wkshp.us.oracle.com) 9> stats extract ptpcadb, total Sending STATS request to EXTRACT PTPCADB ... Start of Statistics at 2017-11-08 10:39:44. Output to ./dirdat/pa: Extracting from TPCADB.ACCTN to TPCADB.ACCTN: *** Total statistics since 2017-11-08 10:30:09 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Extracting from TPCADB.ACCTS to TPCADB.ACCTS: *** Total statistics since 2017-11-08 10:30:09 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Extracting from TPCADB.TELLER to TPCADB.TELLER: *** Total statistics since 2017-11-08 10:30:09 ***  Total inserts 9.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 11.00 Extracting from TPCADB.BRANCH to TPCADB.BRANCH: *** Total statistics since 2017-11-08 10:30:09 ***  Total inserts 10.00  Total updates 10.00  Total deletes 2.00  Total discards 0.00  Total operations 22.00 End of Statistics.

GGCS Oracle Public Cloud (Target) Statistics:

GGSCI (shared-net-ggcs-mp-ggcs-1 as oggtrg@oracle) 6> stats replicat rtpcadb, total Sending STATS request to REPLICAT RTPCADB ... Start of Statistics at 2017-11-08 18:40:30. Integrated Replicat Statistics:  Total transactions 4.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 OGGTRG.ACCTN: *** Total statistics since 2017-11-08 18:37:03 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Replicating from TPCADB.ACCTS to OGGTRG.ACCTS: *** Total statistics since 2017-11-08 18:37:03 ***  Total inserts 10.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 12.00 Replicating from TPCADB.TELLER to OGGTRG.TELLER: *** Total statistics since 2017-11-08 18:37:03 ***  Total inserts 9.00  Total updates 0.00  Total deletes 2.00  Total discards 0.00  Total operations 11.00 Replicating from TPCADB.BRANCH to OGGTRG.BRANCH: *** Total statistics since 2017-11-08 18:37:03 ***  Total inserts 10.00  Total updates 10.00  Total deletes 2.00  Total discards 0.00  Total operations 22.00 End of Statistics.

Summary

Hopefully, this article has provided an overview on how to configure replication between OGG On-Premises and GGCS on Oracle Public Cloud (OPC) via Oracle Cloud Gateway VPN using Corente Services Gateway.

For more information on what other articles are available for Oracle GoldenGate please view our index page.

References

Reference the Corente Cloud Services Exchange (CSX) 9.4 Documentation for additional info on Corente Gateway

Reference the Oracle GoldenGate Cloud Service Documentation for additional information on GGCS.

Reference the Oracle Database Cloud Service Documentation for additional information on DBCS.

Reference the Oracle Compute Cloud Service Documentation for additional information on PaaS

Reference the Oracle GoldenGate 12c Reference and Administration Guide for additional information GoldenGate

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha