SSH Tunnel to AWS RDS in Private Subnet

SSH Tunnel to RDS

We needed to connect to an RDS DB in the Amazon cloud that is not publicly accessible. Normally we  would connect through an ec2 instance but as this is time consuming and gets quite annoying we looked  at setting this up so we could simply connect via

sqlplus user/pass@tns

How to implement

1. Set up the SSH Tunnel

ssh -N -L 1521:your.rds.endpoint.rds.amazonaws.com:1521 sshuser@yourserver.com

 -N   
only set up the tunnel

 -L   
set up the forwarding

 1521
that first number is the port on your local machine

your.rds.endpoint.amazonaws.com
The name of the rds endpoint

1521
the port on the remote computer

sshuser@yourserver.com
how you log in to your ec2 instance

2. Set up a new tns entry for the tunnel connection

ORACLE-AWS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

2. Use the SSH Tunnel

sqlplus user/pass@ORACLE-AWS

This lets you connect to the remote rds instance. Note that you have to use the host here 127.0.0.1 explicitly and that it is not the host you set up earlier. This is because it is now forwarding all of the requests. That’s all.

To be clear on how the ports work, here is another example

ssh -N -L 1234:your.rds.endpoint.rds.amazonaws.com:1521 sshuser@yourserver.com

ORACLE-AWS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

This states that forward from port 1234 on my computer to port 1521 on the remote instance. 

Install SQLPLUS on Debian based systems

SQLPLUS install on Debian

I have  often had to install the sqlplus instant client on debian based systems. Below I am sharing how I last went about this on Ubuntu 13.04 but the guide should be solid on other debian based distros.

SQLPLUS install Ubuntu 13.04 64 Bit

First download the package alien from the ubuntu repos. This will allow you to install rpm packages on debian based systems.

sudo apt-get install alien

Next head on over to the Oracle site and download the instant client packages listed below, you can download then all if you want but these 3 will give you the basics.

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Once that is done, go to your download folder and execute the following:

sudo alien -i oracle-instantclient12.1-basic*.rpm

sudo alien -i oracle-instantclient12.1-sqlplus*.rpm

sudo alien -i oracle-instantclient12.1-devel*.rpm

sudo apt-get install libaio1

sudo touch /etc/ld.so.conf.d/oracle.conf

sudo ldconfig

Now make sure everything is working by trying to establish an sqlplus session by building the TNS entry as below.

Notice that the command for using sqlplus is sqlplus64 as this is a 64bit install. You can of course alias this if you wish but out of the box it will work as below.

sqlplus64 username/password@//dbhost:1521/SID



Thats it, you should be done.

But that's never the case is it  :)

You will now find that you get the error

sqlplus64: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

This is due to you having to set the LD_LIBRARY path variable to point at the required lib folder below.

/usr/lib/oracle/12.1/client64/lib/libsqlplus.so

To fix this issue add these system variables with the following commands:

touch ~/.bash_profile
echo export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/ >> ~/.bash_profile
. ~/.bash_profile

Now when you run sqlplus64 you should be good to go.

If you want to create a tnsnames.ora that oracle will use to store TNS entries then create your tnsnames.ora file where you want it and reference it as an environment variable by adding the following to the ~/.profile file with the following commands.

echo export TNS_ADMIN=~/yourPathToFolderContainingTnsnames.ora >> ~/.bash_profile
. ~/.bash_profilevel*.rpm

sudo apt-get install libaio1

sudo touch /etc/ld.so.conf.d/oracle.conf

sudo ldconfig
touch ~/.bash_profile
echo export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/ >> ~/.bash_profile
. ~/.bash_profile

Bash FTP Script Cronjob

Bash FTP Scripting

Creating a Bash FTP Script and installing as a cronjob can be a great way to move files around at scheduled intervals .

Want to move files around at set intervals? Even to internal servers sometimes the best way to accomplish this is with a bash ftp script. The below example of a bash script logs onto a remote FTP server and checks for messages before downloading them to the /home/user/ftp_in folder. It then checks the /home/user/ftp_out folder and transmits any files found there to the remote FTP server and then finally moves any sent files to /home/user/ftp_sent for archiving purposes.

#!/bin/bash
#pull and push from FTP SERVER
#change to the ftp_in directory
cd /home/user/ftp_in
ftp -niv ftp.address.to.connect.to << FTP_COMMAND
user username password
cd To_pvlogistics
mget *
mdel *
bye
FTP_COMMAND

#Change to the ftp_out directory
cd /home/user/ftp_out
ftp -niv ftp.address.to.connect.to << FTP_COMMAND
user username password
mput *.*
bye
FTP_COMMAND
mv * /home/user/ftp_sent

Saving an example of this script in a file called /home/user/ftp-script.sh it can be installed as a cronjob to run every X minutes as below. In this example it is set to run every 10 minutes.
*/10 * * * * /home/user/ftp-script.sh

FTP man pages for further reading and information on standard FTP commands and usage examples:

FTP

File Transfer Protocol

Syntax
      FTP [-options] [-s:filename] [-w:buffer] [host]

Key   
   -s:filename   Run a text file containing FTP commands.

   host          Host name or IP address of the remote host.

   -g            Disable filename wildcards.

   -n            No auto-login.

   -i            No interactive prompts during ftp.

   -v            Hide remote server responses.

   -w:buffer     Set buffer size to buffer
                 (default=4096)

   -d            Debug
 
   -a            Use any local interface when binding data connection.

http://ss64.com/bash/ftp.html