in Programming

AWS RDS Oracle DB remote connection

Created the Oracle EE on the AWS RDS, tried different methods to connect the created AWS RDS through internet.

1, install Oracle SQL developer, and SQL Plus on Windows, required JDK.
2, in RDS Security Groups, allow the inbound and outbound Oracle RDS TCP connection, port 1521
3, telnet to test the Endpoint and port 1521 is open and connectable, the format is “Telnet Endpoint(your end point string) 1521”
4, windows SQL plus command line to connect to the AWS RDS, the format as below, for windows Command line, should be without quotation mark ‘, and needs to change the user name, HOST is the endpoint string, SID is the database you created.

PROMPT>sqlplus mydbusr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.mydnsnameexample.rds.amazonaws.com)
(PORT=1521))(CONNECT_DATA=(SID=oracledb)))

after successful connection, the command line will display:
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 17 12:59:07 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL

5, Oracle SQL developer to connect AWS RDS Oracle EE in Windows
fill in the username, password, HostName (the Endpoint string), port (1521), SID (DB name), then click “test”, it will test the connection, and “Connect” to Connect to the AWS RDS Oracle EE.

6, Connect AWS RDS Oracle EE from AWS EC2 Linux RHEL 7

Installed below three RPM,
oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

It is RPM file, for Ubuntu install, require to use alien to generate deb file,

sudo apt-get install alien
sudo alien oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
sudo alien oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
sudo alien oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
sudo dpkg -i ./oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb
sudo dpkg -i ./oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb
sudo dpkg -i ./oracle-instantclient12.1-sqlplus_12.1.0.2.0-2_amd64.deb

The installation folder is “/usr/lib/oracle/12.1/client64/lib/” and “/usr/lib/oracle/12.1/client64/bin/”
Remember to set the environment before use sqlplus command,
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
PATH=/usr/lib/oracle/12.1/client64/bin:${PATH}
export PATH

Error encountered: sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Resolved it by install libaio,
sudo apt-get install libaio1 libaio-dev

The connection result is as below,

[root@ip-172-31-2-32 ec2-user]# sqlplus 'UserName@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Endpoint string)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))'

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 18 01:52:28 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>

Reference link:
http://osric.com/chris/accidental-developer/2015/10/connecting-to-oracle-instance-in-aws-rds/
http://docs.aws.amazon.com/AmazonRDS/2012-01-15/UserGuide/USER_ConnectToOracleInstance.html
https://docs.oracle.com/database/121/SQPUG/apd.htm#SQPUG642
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
https://www.rootusers.com/how-to-enable-the-telnet-client-in-windows-10/
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html

Write a Comment

Comment

eight + eighteen =