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:
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.
$ ssh -i ./auth_keys/mp_opc_ssh_key -v -N -f -D 127.0.0.1:8888 email@example.com > ./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
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 22.214.171.124 [126.96.36.199] 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 ‘188.8.131.52’ 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.
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/
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.
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.
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.
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.
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:
Click the "Add" button on the right-hand side.
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".
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.
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".
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.
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:
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.
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”.
You 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”.
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:
RMTHOST 184.108.40.206, MGRPORT 7744, SOCKSPROXY 127.0.0.1:8888
DISCARDFILE ./dirrpt/ptpcadb.dsc, purge
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.
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