X

Best Practices from Oracle Development's A‑Team

Oracle GoldenGate: How to configure On-Premises to GoldenGate Cloud Service (GGCS) via DMZ Server

Mike Papio
Principal Solution Architect

Introduction

A DMZ (demilitarized zone) server is a public-facing computer host placed on a separate or isolated network segment. The intention of this DMZ server is to provide an additional layer of network security between servers in the trusted network and servers in the public network.

In this article we shall discuss how to configure Oracle GoldenGate (OGG) replication between On-Premises and GoldenGate Cloud Service (GGCS) via DMZ Server. This discussion will include a sample configuration setup.

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.

Here's an architecture diagram of Oracle GoldenGate Cloud Services (GGCS):

ggcs_architecture_01

In a typical simple implementation of on-premises on-premises to GGCS, there’s a direct secure connection between the on-premises to the GGCS server. The on-premises server communicates directly to the GGCS server through the use of SOCKS proxy.

 

Here’s a diagram of a typical on-premises to GGCS replication:

 

However, in case the security policy dictates that a direct secure connection is not allowed between on-premises and the GoldenGate Cloud Service (GGCS) server, or the on-premises server needs to stay in the trusted network and can't be located or accessible from the public network, then the use of a DMZ server in between could be an alternative.

Here's a diagram of On-Prem to GGCS via a mid-tier public facing server or DMZ server:

In this scenario, you will be running the SOCKS proxy server on the DMZ server and there's no need to install OGG software on the DMZ server since the only thing needed on the DMZ server is the SSH proxy.

 

OGG Replication Configuration from On-Premises to GGCS via DMZ Server

Here are the four high level steps for configuring OGG Replication from On-Premises to GGCS via DMZ server:

  • Start the SSH Proxy Server on the DMZ Server
  • Configure and start the Online Change Capture Process (Extract) on the on-premises server
  • Configure and start the Datapump Extract on the on-premises Server (SOCSKPROXY pointing to DMZ Server)
  • Configure and start the Online Change Delivery Process (Replicat) on the GGCS server

 

1. Sample SSH Proxy Tunnel Setup on the DMZ Server

  1. -> Start the SSH SOCKS Proxy Server on the DMZ Server.

      1. Start the ssh client in proxy server mode in the DMZ Server:
    1. ggcs_08
    2. Command Syntax: ssh –i <private_key file> -v –N –f –D <listening IP Address>:<listening IP port> <GGCS Oracle User>@<GGCS IP Address> > <socksproxy output file> 2>&1
    3. SSH Command Options Explained:-i = Private Key file-v = Verbose Mode-N = Do no execute remote command; mainly used for port forwarding -f = Run ssh process in the background-D Specifies to run as local dynamic application level forwarding; act as a SOKCS proxy server on a specified interface and port2>&1 = Redirect Stdout and Stderr to the output filelistening IP address = DMZ Server IP Addresslistening port = TCP/IP Port Number
  1. -> Verify the SSH Socks Proxy server has started successfully.

    1. Check the socks proxy output file via the “cat” utility and look for the messages “Local connections to <dmz-server:port> forwarded…” and “Local forwarding listening on <ip_address> port <port #>”.  Make sure it’s pointing to the right dmz server address and port.
    2. ggcs_09
    3. .
    4. .
    5. ggcs_10

2. Sample Online Change Capture (Extract) on the On-Premises Server

On the source/on-premises server, create the online change capture (extract) process using the following GGCS commands:

  1. GGCSI> add extract etpcadb, tranlog, begin now

  1. GGSCI> add exttrail ./dirdat/ea, extract etpcadb, megabytes 100

  1. GGSCI> start extract etpcadb

  1. GGSCI> info extract etpcadb detail

etpcadb_start

Sample Change Capture (Extract) Parameter File (etpcadb.prm):

EXTRACT etpcadb
userid TPCADB, password TPCADB
DISCARDFILE ./dirrpt/etpcadb.dsc, purge
EXTTRAIL ./dirdat/ea
TABLE TPCADB.ACCTN;
TABLE TPCADB.ACCTS;
TABLE TPCADB.BRANCH;
TABLE TPCADB.HISTORY;
TABLE TPCADB.TELLER;
TABLE TPCADB.SUSPECT;

3. Sample Datapump Extract on the On-Premises Server

On the source/on-premises server, create the datapump (extract) process using the following GGCS commands:

  1. GGCSI> add extract ptpcadb, exttrailsource ./dirdat/ea

  1. GGSCI> add rmttrail ./dirdat/pa, extract ptpcadb, megabytes 100

  1. GGSCI> start extract ptpcadb

  1. GGSCI> info extract ptpcadb detail

ptpcadb_start

Sample DataPump Extract Parameter File (ptpcadb.prm):

EXTRACT ptpcadb
RMTHOST opc-ggcs-server, COMPRESS, MGRPORT 7744, SOCKSPROXY dmz-server:1080
DISCARDFILE ./dirrpt/ptpcadb.dsc, purge
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE TPCADB.ACCTN;
TABLE TPCADB.ACCTS;
TABLE TPCADB.BRANCH;
TABLE TPCADB.HISTORY;
TABLE TPCADB.TELLER;
TABLE TPCADB.SUSPECT;

4. Sample Online Change Delivery Process (Replicat) on the On-Premises Server

On the GoldenGate Cloud Service (GGCS) server, create the Change Delivery process (Replicat) using the following GGCS commands:

  1. GGCSI> dblogin useridaalias ggcsuser_alias

  1. GGSCI> add replicat rtpcadb integrated, exttrail ./dirdat/pa

  1. GGSCI> start replicat rtpcadb

  1. GGSCI> info replicat rtpcadb detail

rtpcadb_start

Sample Change Delivery (Replicat) parameter file (rtpcadb.prm):

REPLICAT rtpcadb
SETENV (ORACLE_HOME = '/u02/data/oci')
SETENV (LD_LIBRARY_PATH = '/u02/data/oci')
useridalias ggcsuser_alias
DBOPTIONS INTEGRATEDPARAMS (parallelism 3)
DISCARDFILE ./dirrpt/rtpcadb.dsc, APPEND Megabytes 50
REPORTCOUNT EVERY 1 MINUTES, RATE
ASSUMETARGETDEFS
MAP TPCADB.ACCTN, TARGET MPTPCADB.ACCTN;
MAP TPCADB.ACCTS, TARGET MPTPCADB.ACCTS;
MAP TPCADB.BRANCH, TARGET MPTPCADB.BRANCH;
MAP TPCADB.HISTORY, TARGET MPTPCADB.HISTORY;
MAP TPCADB.TELLER, TARGET MPTPCADB.TELLER;
MAP TPCADB.SUSPECT, TARGET MPTPCADB.SUSPECT;

Summary

In this article, we showed an alternative way of configuring OGG replication between the on-premises server and GoldenGate Cloud Service (GGCS) server with the use of a DMZ server as an additional layer of network security. Additionally, we have illustrated the steps necessary for its configuration.

Additional Resources:

Oracle GoldenGate Cloud Service (GGCS) : https://cloud.oracle.com/goldengate

GGCS User Guide Documentation Link: http://docs.oracle.com/cloud/latest/goldengate-cloud/index.html

GGCS Tutorial Link: http://docs.oracle.com/cloud/latest/goldengate-cloud/goldengate-cloud-tutorials.html

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