GoldenGate Cloud Service (GGCS): Using VPN as a Service (VPNaaS) for Replication from On-Premises to Oracle Public Cloud (OPC)

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) using Virtual Private Network as a Service (VPNaaS).

The VPNaaS is another way Oracle provides VPN connectivity between On-Premises and OPC. For a list of available options for VPN GGCS connectivity between On-Premises and OPC, you can check the previous article entitled GoldenGate Cloud Service (GGCS): Replication from On-Premises to Oracle Public Cloud (OPC) via Oracle Cloud Gateway VPN“, in that article I have discussed different VPN options available and go through a sample configuration via Corente VPN for Shared Network.

One major requirement for GGCS to be configured with VPNaaS is; GGCS must be provisioned to use IP Network instead of a Shared Network. Managing and configuration of OPC’s IP Networks are not discussed in this article. It is assumed that an IP Network has been created. For more details on OPC’s Shared and IP Network, please check the following Oracle documentation link:

Installation of Oracle GoldenGate for Oracle Database on the On-Premises and the provisioning of Oracle GGCS and DBCS are also 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 and was provisioned on the IP network.

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

GGCS replication without the use of VPN between On-Premises to OPC is by default supported via the SOCKS Proxy functionality of the GoldenGate software. Communication of OGG software between On-Premise and GGCS is done through the SSH port 22 by creating an additonal process for SOCKS Proxy client on the ON-Premises to tunnel the connection via SSH to the GGCS instance.

Here’s an architecture diagram of Oracle GoldenGate Cloud Services (GGCS) without VPN implementation.

ggcs_architecture_01

GGCS replication between On-Premises and OPC with the use of VPN adds another layer of security and ease of management of OGG replication processes, since there’s no additional process (SOCKS Proxy Client) that needs to be configured and run.

Here’s a diagram of GGCS Replication between On-Premises and OPC with VPN depicted in this article:

GGCS_VPNaaS_Diagram_02

VPNaaS communicates to the On-Premises network via a third-party device which acts as a VPN gateway. For this article, we used a pfSense appliance as the On-Premises VPN Gateway Device. For a list of supported third-party device and configuration, check the following Oracle Documentation link:

On-Premises Environment

  • On-Premises Network: 192.168.37.0/24
  • VPN Gateway Device: pfSense Appliance
  • VPN Gateway Public Facing IP (WAN): 209.56.134.179
  • VPN Gateway Private IP (LAN): 192.168.37.1
  • OGG Server IP: 192.168.37.101
  • Pre-Shared Key (PSK):MyPreSharedKey2018
  • Encryption Algorithm: AES256
  • Hash Algorithm: SHA256
  • Phase 1 Diffie-Hellman (DH) Group: 14
  • Phase 2 Diffie-Hellman (DH) Group: 14

Oracle Public Cloud (OPC) Environment

  • IP Network: 10.0.37.0/24
  • GGCS Private IP Address: 10.0.37.2
  • DBCS Private IP Address: 10.0.37.3
  • VPNaaS Gateway Name: OPC-OGG-VPN-MPAPIO-NET
  • VPNaaS Gateway Public IP: 129.158.76.230
  • VPNaaS Gateway Private IP: 10.0.37.253
  • Pre-Shared Key (PSK):MyPreSharedKey2018

High Level Steps of VPN Connection Configuration via VPNaaS

The following are the high-level steps for configuring the VPN Connection between On-Premises OGG and OPC GGCS servers via VPNaaS :

  • OPC – Verify and Check the IP Network where the GGCS was provisioned
  • OPC – Provision and Configure the VPN Connection for the VPNaaS
  • On-Premises – Create and Configure the VPN Connection on the third-party VPN device (pfSense appliance)
  • OPC and On-Premises – Verify  Tunnel Status is Up
  • OPC and On-Premises – Verify connectivity is up between On-Premises OGG Server and OPC GGCS server

OPC – IP Network Check

Verify and check the IP Network where GGCS was provisioned and double check GGCS that it was provisioned on the correct IP network. You can do this from the Oracle Cloud Web UI.

GGCS_VPNaaS_001

Figure 1

Figure 1 shows an example on where to look and check for the IP Network. In our example, the IP Network Name is A-Team-MP-IP-Net-37 and the IP Network Address is 10.0.37.0/24.

GGCS_VPNaaS_003

Figure 2

Figure 2 shows an example on where to set the IP Network during GGCS provisioning. In our example the GGCS instance was set to use IP network A-Team-MP-IP-Net-37.

OPC – Provision the VPN Connection under the VPNaaS

Create the VPN connection via the Oracle Cloud Web console under the VPN Menu selection.

(01) In the web console, click the Network tab.
(02) Under the IP Network tab in the left pane, click VPN .
(02) Click VPN Connections.
(03) Click Create VPN Connection.
(04) Select or enter the required information:

  • Name: Enter a name for the VPN connection. In our configuration Example it is set to OPC-OGG-VPN-MPAPIO-NET
  • IP Network: Select the IP network where GGCS was provisioned, in this example it is set to A-Team-MP-IP-Net-37 (10.0.37.0/24).
  • Connected IP Networks: This field displays the IP networks that will be reachable over this VPN connection. The VPN connection allows you to access all IP networks that are added to the same IP network exchange as the specified IP networks. In our example there’s only one IP Network and there’s no IP Exchanged attached to that network, so this field is blank.
  • vNICsets: Select the vNICset that is attached to the GGCS instance, in this case it is the GGCSRepSecList for east-ggcs-vm-mp-ipnet instance.
  • Customer Gateway: Enter the WAN IP or public facing IP address of the VPN device in the On-Premises network, in our example it is set to 209.56.134.179. Sometimes, the WAN IP is different from the public IP address, especially if the VPN device is NAT’d, so make sure you enter the public facing IP address of the On-Premises Gateway device.
  • Customer Reachable Routes: Enter (in CIDR format) the subnet for the ON-Premises where OGG server is running, in our case it is set to 192.168.37.0/24.
  • Pre-shared Key: Enter the pre-shared key (PSK), the value will be masked as you type it. In our example it is set to MyPreSharedKey2018. The key here must match the key entered on the On-Premises VPN gateway device.
  • IKE ID: By default, the public IP address of the cloud gateway will be used, if not set. In our example we are using the default value which will be our cloud gateway’s public IP address, so leave this blank. The public IP will be set during provisioning process.
  • Phase 1 IKE Proposal Options: Specify Phase 1 IKE options. Leaving this blank tells the Gateway to let all possible values to be permitted. So, will leave this blank.
  • Phase 2 ESP Proposal Options: Specify Phase 2 Encapsulating Security Payload (ESP) options.Leaving this blank tells the Gateway to let all possible values to be permitted.
  • Require Perfect Forward Secrecy: This option is selected by default.

GGCS_VPNaaS_002_v2

Figure 3

Figure 3 shows the selection we used in the creation/provisioning of the VPNaaS depicted in this article.

(05) Click Create. You will get message that the VPN connection has been added and its status on the web console would be Pending/Provisioning.

GGCS_VPNaaS_004

Figure 4

Figure 4 shows the message and the status of the VPN connection as soon as it was added.

The provisioning process of the VPNaaS will take anywhere from 20 minutes to 30 minutes and it’s status will be changing and during the process it will also show you the public IP address that will be assigned and it’s corresponding private IP address.

GGCS_VPNaaS_005

Figure 5

Figure 5 shows the public IP address being assigned to the VPNaaS gateway. Make a note of this IP address. In this article the VPNaaS Gateway public IP address was set to 129.158.76.230. This IP address is needed during the configuration of the On-Premises VPN Gateway device. To get current updated status, you might need to hit the refresh button as highlighted on Figure 5.

GGCS_VPNaaS_009

Figure 6

Figure 6 now shows the Private IP address that was set and it’s status is still being provisioned. In this example, the private IP address was set to 10.0.37.253.

On-Premises – Create and Configure the On-Premises third-party VPN device

As a third-party VPN device for the  On-Premises, we have used a pfSense VPN appliance for the purpose of this article. Installation and configuration of pfSense is not covered in this document, however the VPN configuration needed for the VPN appliance is documented in this article for the purposes of connecting to OPC’s VPNaaS.

Here’s what the third-party VPN device configuration looks like for the pfSense for it’s Phase 1 settings:

GGCS_VPNaaS_007_A GGCS_VPNaaS_007_B GGCS_VPNaaS_007_C

Here’s what the third-party VPN device configuration looks like for the pfSense for it’s Phase 2 settings:

GGCS_VPNaaS_008_A GGCS_VPNaaS_008_B

Note: Make sure that the Shared Key for both VPN devices on On-Premises and OPC matches.

 

OPC and On-Premises – Verify Tunnel Status is Up

Once the On-Premises third-party VPN device has been configured and the provisioning of the VPNaaS completed, we need to verify that the tunnel status on both end is Up. On the OPC side, you can do this by checking the provisioning status of the VPNaaS on the Oracle Web console.

GGCS_VPNaaS_010

As you can see on the above image for the VPNaaS on the OPC side, the Tunnel status is “Up” .

For the On-Premises side, here’s what it looks like from the pfSense VPN Status page and you should see “Established” message:

GGCS_VPNaaS_006

Once you have verified that both status is up which means a tunnel session has been established for On-Premises and OPC, you can now proceed for checking the connectivity between On-Premises OGG server and OPC GGCS server.

OPC and On-Premises – Verify VPN connection

Checking connectivity between On-Premises OGG server and OPC GGCS server can be done by a simple ping command or logging on from the On-Premises OGG server to GGCS server using it’s private IP address.

01. Ping from On-Premises OGG Server to OPC VPNaaS Private Gateway IP (10.0.37.253) and GGCS server (10.0.37.2).
[oracle@ogg-wkshp ~]$ ifconfig eth0
eth0 Link encap:Ethernet HWaddr 08:00:27:C7:D2:57 
 inet addr:192.168.37.101 Bcast:192.168.37.255 Mask:255.255.255.0
 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
 RX packets:11412 errors:0 dropped:0 overruns:0 frame:0
 TX packets:9899 errors:0 dropped:0 overruns:0 carrier:0
 collisions:0 txqueuelen:1000 
 RX bytes:4895240 (4.6 MiB) TX bytes:868783 (848.4 KiB)

[oracle@ogg-wkshp ~]$ ping 10.0.37.253
PING 10.0.37.253 (10.0.37.253) 56(84) bytes of data.
64 bytes from 10.0.37.253: icmp_seq=1 ttl=63 time=75.7 ms
64 bytes from 10.0.37.253: icmp_seq=2 ttl=63 time=75.5 ms
^C
--- 10.0.37.253 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1002ms
rtt min/avg/max/mdev = 75.536/75.645/75.755/0.296 ms
[oracle@ogg-wkshp ~]$ ping 10.0.37.2
PING 10.0.37.2 (10.0.37.2) 56(84) bytes of data.
64 bytes from 10.0.37.2: icmp_seq=1 ttl=62 time=77.8 ms
64 bytes from 10.0.37.2: icmp_seq=2 ttl=62 time=76.3 ms
64 bytes from 10.0.37.2: icmp_seq=3 ttl=62 time=76.0 ms
^C
--- 10.0.37.2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 76.048/76.740/77.847/0.790 ms
[oracle@ogg-wkshp ~]$
02. Login from On-Premises OGG Server to OPC GGCS server (10.0.37.2).
[oracle@ogg-wkshp ~]$ ssh -i auth_keys/mp_opc_ssh_key opc@10.0.37.2
The authenticity of host '10.0.37.2 (10.0.37.2)' can't be established.
RSA key fingerprint is 82:a7:f9:fa:0a:8d:1e:c9:5b:31:41:78:0b:4d:64:a2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.37.2' (RSA) to the list of known hosts.
[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ 
[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ hostname
east-ggcs-vm-mp-ipnet-ggcs-1
[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ ifconfig eth0
eth0 Link encap:Ethernet HWaddr 02:33:60:2F:24:06 
 inet addr:10.0.37.2 Bcast:10.0.37.255 Mask:255.255.255.0
 inet6 addr: fe80::33:60ff:fe2f:2406/64 Scope:Link
 UP BROADCAST RUNNING MULTICAST MTU:8900 Metric:1
 RX packets:7144476 errors:0 dropped:0 overruns:0 frame:0
 TX packets:2274060 errors:0 dropped:0 overruns:0 carrier:0
 collisions:0 txqueuelen:1000 
 RX bytes:9443301378 (8.7 GiB) TX bytes:178410711 (170.1 MiB)

[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ exit
logout
Connection to 10.0.37.2 closed.
You have new mail in /var/spool/mail/oracle
[oracle@ogg-wkshp ~]$
03. Login from OPC GGCS server to On-Premises OGG server (192.168.37.101).
[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ ssh oracle@192.168.37.101
The authenticity of host '192.168.37.101 (192.168.37.101)' can't be established.
RSA key fingerprint is 0e:d1:25:f8:e0:b2:af:c6:94:a9:84:1b:e7:58:c9:5d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.37.101' (RSA) to the list of known hosts.
oracle@192.168.37.101's password: 
Last login: Fri Feb 16 12:07:56 2018
[oracle@ogg-wkshp ~]$ 
[oracle@ogg-wkshp ~]$ hostname
ogg-wkshp.us.oracle.com
[oracle@ogg-wkshp ~]$ ifconfig eth0
eth0 Link encap:Ethernet HWaddr 08:00:27:C7:D2:57 
 inet addr:192.168.37.101 Bcast:192.168.37.255 Mask:255.255.255.0
 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
 RX packets:12507 errors:0 dropped:0 overruns:0 frame:0
 TX packets:11014 errors:0 dropped:0 overruns:0 carrier:0
 collisions:0 txqueuelen:1000 
 RX bytes:5061839 (4.8 MiB) TX bytes:968164 (945.4 KiB)

[oracle@ogg-wkshp ~]$ exit
logout
Connection to 192.168.37.101 closed.
[opc@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$

Once you have verified data connectivity flow between On-Premises OGG server and OPC GGCS server, now you are ready to do the OGG replication between two sites.

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@east-ggcs-vm-mp-ipnet-ggcs-1 ~]$ sudo su – oracle
[oracle@east-ggcs-vm-mp-ipnet-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@east-ggcs-vm-mp-ipnet-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 (east-ggcs-vm-mp-ipnet-ggcs-1) 1> start mgr
Manager started.


GGSCI (east-ggcs-vm-mp-ipnet-ggcs-1) 2> info mgr

Manager is running (IP port east-ggcs-vm-mp-ipnet-ggcs-1.7777, Process ID 109143).


GGSCI (east-ggcs-vm-mp-ipnet-ggcs-1) 3> exit
[oracle@east-ggcs-vm-mp-ipnet-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.37.101, 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

2018-02-15 23:24:50 INFO OGG-01788 SCHEMATRANDATA has been added on schema tpcadb.

2018-02-15 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 2018-02-15 23:29 Status RUNNING
Checkpoint Lag 00:00:09 (updated 00:00:03 ago)
Process ID 7200
Log Read Checkpoint Oracle Integrated Redo Logs
 2018-02-15 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 2018-02-15 23:27 2018-02-15 23:30
 Not Available * Initialized * 2018-02-15 23:27
 Not Available * Initialized * 2018-02-15 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 10.0.37.2, 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 2018-02-15 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 (east-ggcs-vm-mp-ipnet-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 (east-ggcs-vm-mp-ipnet-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 (east-ggcs-vm-mp-ipnet-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 (east-ggcs-vm-mp-ipnet-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 (east-ggcs-vm-mp-ipnet-ggcs-1 as oggtrg@oracle) 5> info replicat rtpcadb detail

REPLICAT RTPCADB Last Started 2018-02-16 18:36 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 112419
Log Read Checkpoint File ./dirdat/pa000000
 2018-02-16 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 * 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 10:39:44.

Output to ./dirdat/pa:

Extracting from TPCADB.ACCTN to TPCADB.ACCTN:

*** Total statistics since 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 (east-ggcs-vm-mp-ipnet-ggcs-1 as oggtrg@oracle) 6> stats replicat rtpcadb, total

Sending STATS request to REPLICAT RTPCADB ...

Start of Statistics at 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 2018-02-16 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 and example on how to configure VPN connection via VPNaaS between OGG On-Premises and OPC GGCS with OGG replication configured between two sites.

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

References

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

Add Your Comment