Cloud security, Cloud Services, Database Management Services, Oracle Databases
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.
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
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:oracle12cproduct12.2.0dbhome_1networkadminsqlnet.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:oracle12cproduct12.2.0dbhome_1networkadminsqlnet.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.
2
