MacLochlainns Weblog

Having configured an AlmaLinux 8.6 with Oracle Database 11g XE, MySQL 8.0.30, and PostgreSQL 15, we migrated it to AWS EC2 and provisioned it. We used the older and de-supported Oracle Database 11g XE because it didn’t require any kernel modifications and had a much smaller footprint.

I had to address why attempting to connect with the sqlplus utility raised the following error after provisioning a copy with a new static IP address:

ERROR:
ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor

A connection from SQL Developer raises a more addressable error, like:

ORA-17069

I immediately tried to check the connection with the tnsping utility and found that tnsping worked fine. However, when I tried to connect with the sqlplus utility it raised an ORA-12514 connection error.

There were no diagnostic steps beyond checking the tnsping utility. So, I had to experiment with what might block communication.

I changed the host name from ip-172-58-65-82.us-west-2.compute.internal to a localhost string in both the listener.ora and tnsnames.ora. The listener.ora file:

# listener.ora Network Configuration FILE:
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

The tnsnames.ora file:

# tnsnames.ora Network Configuration FILE:
 
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I suspected that it might be related to the localhost value. So, I checked the /etc/hostname and /etc/hosts files.

Then, I modified /etc/hostname file by removing the AWS EC2 damain address. I did it on a memory that Oracle’s TNS raises errors for dots or periods in some addresses.

The /etc/hostname file:

ip-172-58-65-82

The /etc/hosts file:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-172-58-65-82
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 ip-172-58-65-82

Now, we can connect to the Oracle Database 11g XE instance with the sqlplus utility. I believe this type of solution will work for other AWS EC2 provisioned Oracle databases.