Connecting to Oracle instance in AWS RDS

I inherited a development Oracle instance in Amazon Web Services’s Relational Database Service (AWS RDS) from a former colleague. I wanted to know what was in the database, and for that, I had to connect to it. I decided the most straightforward way would be with the simplest tool: SQL*Plus.

First, I spun up a Debian server in the AWS EC2 (Elastic Compute Cloud) service. Then I installed 2 packages from the Oracle Instant Client for Linux (AKA instaclient):

• Instant Client Package – Basic
• Instant Client Package – SQL*Plus

These come packaged as RPMs, so I had to use alien to install it. The Ubuntu documentation on installing the Oracle Instant Client was helpful.

Then I tried my connection string:
sqlplus adminuser/t0u6h*pa55w0rd//really.long.aws.string:1521

ERROR:
ORA-12170: TNS:Connect timeout occurred

OK, that meant that the IP address of my EC2 instance was not authorized in the security group for the database. I added the IP address via the AWS web interface.

sqlplus adminuser/t0u6h*pa55w0rd//really.long.aws.string:1521

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

OK, I do need to provide a service name, don’t I? I tacked on the DB Name listed in the AWS web interface:

sqlplus adminuser/t0u6h*pa55w0rd//really.long.aws.string:1521/devdb

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

If I’m not mistaken, TNS service names are defined in the tnsnames.ora file, and really…I’ve always found tnsnames.ora to be more trouble than it’s worth. I tried a different route instead by using just a connect string, as suggested by AWS’s Connecting to a DB Instance Running the Oracle Database Engine.

sqlplus 'adminuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=really.long.aws.string)(PORT=1521))(CONNECT_DATA=(SID=devdb)))'

ERROR:
ORA-21561: OID generation failed

A new error message is still progress, right?

I assumed the error meant the specified SID was incorrect. I had read in the AWS documentation somewhere that “the value for the Oracle SID will be the name of the DB instance’s database that you specified when you created the DB instance, not the name of the DB instance.” Since I had not created the DB instance, I was afraid I might never connect. However, that did not turn out to be the problem.

Several sites suggested that the fix for “ORA-21561: OID generation failed” is to make sure that the hostname and the /etc/hosts entry for the machine match. Most of the posts online described people who were trying to connect to an Oracle DB on the the localhost machine. I figured that this suggestion was for the server-side and was therefore not relevant in my case. Besides, Oracle was running as a service and I had no access to the underlying server machine. However, it turns out that the hostname and hosts entry mismatch makes a difference for the client machine as well.

# hostname
ip-NNN-NNN-NNN-NNN

# head -1 /etc/hosts
127.0.0.1 localhost

I updated the entry in /etc/hosts to:
127.0.0.1 ip-NNN-NNN-NNN-NNN localhost

And then it worked!

sqlplus 'adminuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=really.long.aws.string)(PORT=1521))(CONNECT_DATA=(SID=devdb)))'

Enter password:

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

SQL>

I realized that just because I could connect to it from my Linux machine would not help my colleagues who might want to access it from Windows. I installed the Oracle instant client (Basic + SQL*Plus) for Windows and tried connecting. It wouldn’t connect at all. I tried to test the connection:

telnet really.long.aws.string 1521
'telnet' is not recognized as an internal or external command, operable program or batch file.

Oh, Windows–why isn’t telnet installed by default? I enabled telnet via the Turn Windows features on or off option in the Control Panel.

telnet really.long.aws.string 1521
Connecting To really.long.aws.string...Could not open connection to the host, on port 1521: Connect failed

Right! I had added the IP address of the Linux machine, but I needed to add the IP address of the Windows machine to the AWS security group. After that, I could connect via telnet. I tried the connect string again:

sqlplus 'adminuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=really.long.aws.string)(PORT=1521))(CONNECT_DATA=(SID=devdb)))'

ERROR
ORA-12560: TNS:protocol adapter error

That error, as it turned out, indicated that sqlplus on Windows did not like the quoted connection string:

C:\oracle\instant_client_12_1>sqlplus adminuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=really.long.aws.string)(PORT=1521))(CONNECT_DATA=(SID=devdb)))
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 16 19:44:19 2015

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

Enter password:

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

SQL>

It worked! Everything seemed quite straightforward once I’d made all the obvious mistakes.