GoldenGate Cloud Service (GGCS): How to configure Oracle GoldenGate (OGG) SOCKS Proxy Server via HTTP Proxy Server

Introduction

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

This approach is very similar to configuring OGG Replication between On-Premises and GGCS via DMZ server, the only main difference is how the GoldenGate uses the mid-tier server. For details on configuring OGG Replication via DMZ server between On-Premises and GGCS, check the following link on this topic:

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

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.

In this article, the following assumptions were made:

  • HTTP Proxy Server is running on a mid-tier or on another server/host
  • HTTP Proxy Server software supports HTTP connect method
  • Netcat “nc” utility is installed or available on the On-Premises server
  • Oracle GoldenGate software has been installed on the On-Premises server
  • A GGCS instance and a Database Cloud Service (DBCS) instance has been provisioned on Oracle Public Cloud (OPC)

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, and the only allowed outgoing connection must go through an HTTP proxy port running on a mid-tier server, then OGG connection must be configured to use HTTP proxy server.

Here’s a diagram of On-Prem to GGCS via a mid-tier HTTP Proxy Server :

OGG_Socks_HTTP_Proxy_01

In this scenario, you will be running the OGG SOCKS proxy server connecting first or tunneling through the HTTP proxy server port.

To accomplish connection to the HTTP proxy port from OGG SOCKS proxy server, the HTTP proxy server must support the HTTP “CONNECT” method. This is required since the OGG SSH SOCKSPROXY will need to use this method to tunnel through the HTTP server via netcat “nc” utiilty.

OGG Replication Configuration from On-Premises to GGCS via HTTP Proxy Server

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

  • Start the OGG SSH Proxy Server on the On-Premises connecting and tunneling through the HTTP Proxy port
  • 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
  • Configure and start the Online Change Delivery Process (Replicat) on the GGCS server

1. Sample OGG SSH Proxy Server tunneling via HTTP Proxy Server

    1. -> Check the HTTP Proxy server is running and listening for connection on the HTTP Proxy port

        1. In this example, the HTTP proxy server is listening on port 8888 and for the purpose of this exercise we are using an Open Source lightweight HTTP/SSL proxy daemon called “tinyproxy” as the HTTP proxy software.
        2. OGG_Socks_HTTP_Proxy_04
    1. -> Start the OGG SSH SOCKS Proxy Server on the On-Premises.

        1. Start the OGG ssh client in proxy server mode connecting to the GGCS OPC via the HTTP proxy server :
          1. $ ssh -i ./auth_keys/mp_opc_ssh_key -o ServerAliveInterval=120 -v -N -f -D 9999 opc@east-ggcs-vm-mp -o “ProxyCommand=nc -X connect -x enterprise:8888 %h %p” > ./logs/http_socksproxy.log 2>&1

          1. Command Syntax: ssh –i <private_key file> -o ServerAliveInterval <seconds> -v –N –f –D <listening IP port> <GGCS Oracle User>@<GGCS IP Address> -o “ProxyCommand=nc -X connect -x <http_server>:<http_port> %h %p” > <socksproxy output file> 2>&1
          2. SSH Command Options Explained:
          3. ServerAliveInterval = enable keep alive session
          4. -i = Private Key file
          5. -v = Verbose Mode
          6. -N = Do no execute remote command; mainly used for port forwarding 
          7. -f = Run ssh process in the background
          8. -D Specifies to run as local dynamic application level forwarding; act as a SOCKS proxy server
          9. listening port = TCP/IP Port Number on the loopback interface (127.0.0.1)
          10. ProxyCommand = option to let ssh session tunnel through another host – in our case it’s the HTTP server via the netcat “nc” utility using the HTTP connect method
          11. 2>&1 = Redirect Stdout and Stderr to the output file
          12. -> 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 <locahost or loopback address:port> forwarded…” and “Local forwarding listening on <loopback IP address> port <port #>”.
  1. OGG_Socks_HTTP_Proxy_02
  2. You can also check the HTTP server and make sure that a connections were established from the On-Premises server to the HTTP Proxy Port and to the GGCS server via the HTTP proxy software. You can do this via the netstat command.
  3. OGG_Socks_HTTP_Proxy_09
  4. In the above output, we have the SOCKSPROXY (192.168.106.138:50865) connection to HTTP Proxy (192.168.106.50:8888) and in the same Process ID (3513) we have the tunnel connection from HTTP Proxy (192.168.106.50:47304) to the GGCS instance SSH Server daemon (129.158.64.74:22).

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 50

  1. GGSCI> start extract etpcadb

  1. GGSCI> info extract etpcadb detail

 

OGG_Socks_HTTP_Proxy_07

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;

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 50

  1. GGSCI> start extract ptpcadb

  1. GGSCI> info extract ptpcadb detail

 

OGG_Socks_HTTP_Proxy_08

You can also check the the socksproxy log and make sure that a connection from the SOCKSPROXY port has been successfully forwarded to the GGCS instance MGR Port:

OGG_Socks_HTTP_Proxy_06

 

Sample DataPump Extract Parameter File (ptpcadb.prm):

EXTRACT ptpcadb
RMTHOST east-ggcs-vm-mp-ggcs-1, MGRPORT 7744, SOCKSPROXY 127.0.0.1:9999
DISCARDFILE ./dirrpt/ptpcadb.dsc, purge
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE TPCADB.ACCTN;
TABLE TPCADB.ACCTS;
TABLE TPCADB.BRANCH;
TABLE TPCADB.HISTORY;
TABLE TPCADB.TELLER;

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

OGG_Socks_HTTP_Proxy_10

 

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

REPLICAT rtpcadb
useridalias ggcsuser_alias
DBOPTIONS INTEGRATEDPARAMS (parallelism 3)
DISCARDFILE ./dirrpt/rtpcadb.dsc, APPEND Megabytes 50
REPORTCOUNT EVERY 1 MINUTES, RATE
ASSUMETARGETDEFS
MAP TPCADB.ACCTN, TARGET TPCADB.ACCTN;
MAP TPCADB.ACCTS, TARGET TPCADB.ACCTS;
MAP TPCADB.BRANCH, TARGET TPCADB.BRANCH;
MAP TPCADB.HISTORY, TARGET TPCADB.HISTORY;
MAP TPCADB.TELLER, TARGET TPCADB.TELLER;

Summary

In this article, we showed an alternative way of configuring OGG replication between the On-Premises server and GoldenGate Cloud Service (GGCS) server via tunneling the connection through an HTTP Proxy 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

Add Your Comment