How to Establish Connection to AWS RDS Oracle Database

Overview

Amazon Web Services is a comprehensive, evolving cloud computing platform provided by Amazon. Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.

In this article we demonstrate how to establish connection to AWS RDS Oracle Database Using SQL Client.

Prerequisites

• Install Oracle SQL Client on your client machine (Windows/Linux)
• Use can also use Oracle SQL*Developer tool to establish connection

Procedure to Connect to AWS RDS Oracle Database

• Put tns entry in tnsnames.ora file client machine. 
• Review the blog article on how to find end point details of AWS RDS instance at http://netsoftmate.blogspot.com/2018/07/how-to-find-hostname-from-aws-console.html

nsmprd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nsmprd)
    )
  )

• Check connectivity to AWS RDS Oracle instance using tnsping utility

C:\Users>tnsping nsmprd

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-JUN-2018 16:19:34

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle12c\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
TNS-12535: TNS:operation timed out




Follow the steps below to resolve the timed out issue. 

• Check security group and add rules to VPC security group. While Database instance creation if it is default security group then this instance firewall prevents connections.

To know more about security group please go through following link
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html

Add rule to security group

• Login to console and click on VPC under Networking and content delivery

 

• Under vpc dashboard navigation pane click on security group 

• Select the security group for update.

• Click on inbound rules and edit to add new rules.

• Click on add another rule.

• Select from drop down list

• Choose oracle port in in-bound traffic
  

• Click on save

Now check the connectivity again using tnsping

C:\Users>tnsping nsmprd

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-JUN-2018 18:37:22

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle12c\product\12.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
OK (1500 msec)

We can see that the tnsping is now successful.

• Now Establish the connection to AWS RDS Oracle Instance

C:\Users>sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 13 18:37:30 2018

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

Enter user-name: nsmsystem@nsmprd
Enter password:
Last Successful login time: Wed Jun 13 2018 14:31:06 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,INSTANCE_NAME,OPEN_MODE,HOST_NAME,DATABASE_STATUS,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,v$instance;

NAME      INSTANCE_NAME    OPEN_MODE            HOST_NAME                      DATABASE_STATUS   LOGINS     UP TIME
--------- ---------------- -------------------- ------------------------------ ----------------- ---------- -------------------------
NSMPRD    NSMPRD           READ WRITE           ip-10-1-2-24                   ACTIVE            ALLOWED    10-JUN-2018 09:27:22




Conclusion

In this article we have learned that how to establish connection to to AWS RDS Oracle Instance using SQL Client and also we have seen how to add rules to the VPC security group.

By

Name : Omer
Designation : Senior Database Engineer
Organization: Netsoftmate IT Solutions.