GoldenGate Cloud Service (GGCS): How to run SSH Socks Proxy Server for Oracle GoldenGate (OGG)

Introduction

In this article we shall discuss how to configure and run Secure Shell (SSH) Socks Proxy server needed for Oracle GoldenGate (OGG) replication between On-Premises and GoldenGate Cloud Service (GGCS) in the Oracle Public Cloud (OPC).

The SSH Socks Proxy server is required in GoldenGate replication between On-Premises and OPC, only if there’s no VPN connectivity between the two sites, and the only connection allowed to the replication target on the OPC is via SSH.

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:

  • SSH Client/Server package is installed or available on the Linux/UNIX On-Premises server
  • PuTTY Open Source software is installed or available on the Windows On-Premises server
  • Reader is familiar with configuration/administration of Oracle GoldenGate software both On-Premises and GGCS

Main Article

By default, once GGCS has been provisioned the only communication port that is open for connectivity is the SSH port.

A VPN can be configured between On-Premises and GGCS. However, in the instance that VPN is not available, replication connectivity between On-Premises and GGCS can be done using SOCKS Proxy support via SSH.

Here’s a diagram of On-Premises to GGCS replication via SSH SOCKS Proxy:

In this article, we will illustrate on how to start/run the SSH Socks Proxy server on a Linux/UNIX and Windows OS environment.

Starting and Verifying OGG SSH Proxy Server on Linux/UNIX platform

-> Start ssh socks proxy on Linux/UNIX platform

$ ssh -i ./auth_keys/mp_opc_ssh_key -v -N -f -D 127.0.0.1:8888 opc@129.33.44.55  > ./dirrpt/ogg_socksproxy.log 2>&1

Command Syntax: ssh –i <private_key file> -v –N –f –D <host:port> <GGCS Oracle User>@<GGCS IP Address>  > <socksproxy output file> 2>&1

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 SOCKS proxy server

host = Host Name or Host IP Address where this SOCKS proxy will listen (127.0.0.1 is the loopback address)

port = TCP/IP Port Number to listen on

2>&1 = Redirect Stdout and Stderr to the output file

-> Verify the SSH Socks Proxy server process has started successfully.

Check the socks proxy output file via the “cat” utility and look for the messages “Local connections to forwarded…” and “Local forwarding listening on port ”.  Make sure it’s connected to GGCS instance and listening on the right IP and port address.

[oracle@ogg-wkshp db_1]$ cat ./dirrpt/ogg_socksproxy.log

OpenSSH_4.3p2, OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: Applying options for *
debug1: Connecting to 129.33.44.55 [129.33.44.55] port 22.
debug1: Connection established.
debug1: identity file keys/mp_opc_ssh_key type 1
debug1: loaded 1 keys
debug1: Remote protocol version 2.0, remote software version OpenSSH_5.3
debug1: match: OpenSSH_5.3 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_4.3
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-md5 none
debug1: kex: client->server aes128-ctr hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host ‘129.145.1.180’ is known and matches the RSA host key.

debug1: Authentication succeeded (publickey).
debug1: Local connections to 127.0.0.1:8888 forwarded to remote address socks:0
debug1: Local forwarding listening on 127.0.0.1 port 8888.
debug1: channel 0: new [port listener]
debug1: Entering interactive session.

Starting and Verifying OGG SSH Proxy Server on Windows OS platform

SSH is not available by default on the Windows OS platform, you will need to install PuTTY open source software to provide ssh functionality. For more details on Open Source PuTTY software, you can go to the PuTTY website – https://www.putty.org/

-> Start ssh socks proxy on Windows OS via PuTTY

To start SSH socks proxy using PuTTY you will need to run and execute the PuTTY executable (PuTTY.exe) and need to create/configure a PuTTY session with the correct connection information and with the Dynamic Port Forwarding functionality enabled.

Here are the steps to configure and start PuTTY with SOCKS functionality enabled for connectivity to GGCS:

Step 1: Start PuTTY.exe and on the PuTTY configuration screen dialog box on the left-handside, click “Data” under the “Connection” section.

GGCS_SocksProxy_00

Step 2: Under the “Data” section, configure the “Auto-login username” to be used to connect to the GGCS instance. In our example it is “opc”, then click “SSH” on the left-hand side of the PuTTY dialog box.

GGCS_SocksProxy_01

Step 3: Under the “SSH” section, check/enable “Don’t start a shell or command at all”, then click “Auth” on the left-hand side.

GGCS_SocksProxy_02

Step 4: Under the “Auth” section, for “Private key file for authentication:”, enter/configure the private key file to be used to connect to the GGCS instance. You can also click “Browse…” to search for the private key file on the system, then click “Tunnels” on the left-hand side.

GGCS_SocksProxy_03

Step 5: Under the “Tunnels” section, check/enable “Local ports accept connections from other hosts” this will enable other OGG On-Premises server in case you have it to use this SOCKS proxy server to connect to your GGCS instance. Then, fill in the following data:

  1. Source Port: Fill in the TCP Listening port, in our example it is 8888
  2. Destination: Fill in the GGCS instance private IP address, in our example it is 172.16.21.5
  3. Dynamic: check/Enable this Radio Button – This turns on the SOCKS functionality

Click the Add” button on the right-hand side.

GGCS_SocksProxy_04

Step 6: Once the Add is successful, you will see a character string appear in the Forwarded Ports field, then click “Logging” on the left-hand side under the “Session”.

GGCS_SocksProxy_05

Step 7: Under the “Logging” section, check/enable “SSH Packets” and then for “Log file name:”, enter/configure the name you want to use for your SSH logfile to be used. You can also click “Browse…” to search for existing log file on the system you want to use, then click “Session” on the left-hand side.

GGCS_SocskProxy_06

Step 8: Under the “Session” section, for the “Host Name (or IP address)” enter the IP address of the GGCS instance, check/enable “SSH” and then for “Saved Sessions”, enter the name you want to use for your GGCS SSH Socks Proxy session to be saved, then click “Save”.

GGCS_SocskProxy_07

Step 9: Once the Save is successful, you will see your saved session name you entered in the Sessions box, then click “Open” to open the PuTTY session and start the SSH SOCKS Proxy server.

GGCS_SocskProxy_08

Step 10: Once PuTTY is successful, it will start a new PuTTY window session and connect to the GGCS instance. It will have no prompt, you will just need to leave it open as shown in this sample screenshot:

GGCS_SocskProxy_09

-> Verify the SSH Socks Proxy server process has started successfully.

Now, to verify that the PuTTY SSH Socks Proxy server process started successfully, you will need to right-click the PuTTY Title Bar, then select and click ““Event Log” to open up the PuTTY event log file.

GGCS_SocskProxy_10

and look for the messages “Local connections to forwarded…” and “Local forwarding listening on port ”.  Make sure it’s connected to GGCS instance and listening on the right IP and port address.

Once the Event Log File dialog box has been opened, check and look for the messages “Access Granted and “Local port nnnn SOCKS dynamic forwarding.

GGCS_SocskProxy_11You can also open the SSH log file you have defined in the SSH PuTTY session file during configuration. In our example, it is “putty_mp_no_ssh.log”. Once the file is open, look for the same messages “Access Granted and “Local port nnnn SOCKS dynamic forwarding.

OGG Extract Data Pump Parameter

Once you have the successful message, then the SSH proxy server is running and ready to accept data for OGG replication and forward it to the GGCS instance. Now, all is needed is to modify the Extract Parameter file with the correct IP address and port for the SOCKS Proxy. The parameter to use in the OGG Extract Pump file is “RMTHOST …. SOCKSPROXY ….”.

Here’s a sample datapump (extract) parameter file pointing to the correct SOCKS Proxy IP address and Port that was used in this example:

Sample DataPump Extract Parameter File:

EXTRACT ptpcadb
RMTHOST 129.33.44.55, MGRPORT 7744, SOCKSPROXY 127.0.0.1:8888
DISCARDFILE ./dirrpt/ptpcadb.dsc, purge
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE TPCADB.ACCTN;
TABLE TPCADB.ACCTS;
TABLE TPCADB.BRANCH;
TABLE TPCADB.HISTORY;
TABLE TPCADB.TELLER;

Summary

In this article, we have illustrated the steps necessary to start SSH Socks Proxy on a Linux/UNIX and Windows OS platform needed for OGG replication between On-Premises and GGCS on Oracle Public Cloud.

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