Connect Oracle RDS from EC2 Instance

Pre-req : Linux EC2 instance and Oracle RDS instance - Both running

Create Group & User

1. Create user : oracle
# useradd oracle

2. Create group : dba
# groupadd dba

3. Change group for oracle user
# usermod -a -G dba oracle


Install VNC on EC2

1. # yum install tigervnc-server

2. # yum install xterm

3. Start VNC server
# su - oracle
$ vncserver

4. Check the port of vncserver :
$ lsof -i -P | grep -i "listen" |grep vnc
Xvnc      26573   oracle    0u  IPv6  80110      0t0  TCP *:6004 (LISTEN)
Xvnc      26573   oracle    1u  IPv4  80111      0t0  TCP *:6004 (LISTEN)
Xvnc      26573   oracle    9u  IPv4  80119      0t0  TCP *:5904 (LISTEN)
Xvnc      26573   oracle   10u  IPv6  80120      0t0  TCP *:5904 (LISTEN)
$

5. Open port 5904 and 6004 for EC2 instance, using Security Group rules.

6. Edit ~/.vnc/xstartup to use xterm

#!/bin/sh

exec /etc/X11/xinit/xinitrc [ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup [ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources

xsetroot -solid grey  

vncconfig -iconic &  #!/bin/sh

/usr/bin/xterm -geometry 80x24+10+10 -ls -title "xterm" &  

/usr/bin/xterm -geometry 80x24+200+200 -ls -T "xterm" &  

twm &

7. Launch VNC by using : Servername:Port_Number


Download and Install Firefox

1. Download package signing public key using HTTPS

# curl -X GET -o RPM-GPG-KEY-lambda-epll https://lambda-linux.io/RPM-GPG-KEY-lambda-epll

2. Import package signing public key

# rpm --import RPM-GPG-KEY-lambda-epll

3. Download EPLL Release (signed) RPM package using HTTPS

# curl -X GET -o epll-release-2017.03-1.2.ll1.noarch.rpm https://lambda-linux.io/epll-release-2017.03-1.2.ll1.noarch.rpm

4. Install EPLL Release RPM package

# yum -y install epll-release-2017.03-1.2.ll1.noarch.rpm

5. Install firefox-compat package

# yum --enablerepo=epll install firefox-compat

6. Now download the latest version of Firefox, extract and run it.

$ wget -O firefox-esr.tar.bz2 "https://download.mozilla.org/?product=firefox-45.3.0esr-SSL&os=linux64&lang=en-US"

$ bzcat firefox-esr.tar.bz2 | tar xvf -

$ cd firefox

$ ./firefox


Download and Install Oracle Client

1. Go to http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

2. Download Oracle Client Software to EC2 instance.

3. Unzip and client software.

4. Create a file /etc/oraInst.loc

# cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=dba
#

5. Add swap :

# dd if=/dev/zero of=/var/myswap bs=1M count=1024
# mkswap /var/myswap
# swapon /var/myswap

6. To enable it on system boot, simply edit /etc/fstab file and add following entry at end of file.

/var/myswap   swap   swap   defaults  0 0

7. Install Library Dependencies

# yum install xorg-x11-xauth.x86_64 xorg-x11-server-utils.x86_64

# yum install binutils-* compat-libcap* compat-libstdc++-* compat-libstdc++-* gcc-* gcc-c++-* glibc-* glibc-* glibc-devel-* glibc-devel-* ksh libgcc-* libgcc-* libstdc++-* libstdc++-* libstdc++-devel-* libstdc++-devel-* libaio-* libaio-* libaio-devel-* libaio-devel-* make-* sysstat-*

8. export ORACLE_HOME=/u01/app/oracle/11g

9. cd /home/oracle/Downloads/client
./runInstaller

10. To Fix issue with pre-reqs run following after launching installer :

# cd /tmp

# cd CVU_11.2.0.1.0_oracle/

# ./runfixup.sh
Response file being used is :./fixup.response
Enable file being used is :./fixup.enable
Log file location: ./orarun.log
Setting Kernel Parameters...
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
#

11. Post install run :

# ./u01/app/oracle/11g/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/11g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
#

12. Connect to Oracle RDS instance :

$ export ORACLE_HOME=/u01/app/oracle/11g
$ export TNS_ADMIN=$ORACLE_HOME/network/admin
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus 'vivek@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.********.ap-south-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=orcl)))'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 21 09:31:52 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
ORCL

SQL>