Connecting oracle XE db on AWS EC2 - configure TNSLIstenr for internal and external IP

Question

I am trying to connect to an Oracle XE instance on an AWS EC2 instance. I found this answer: Connecting dto an oracle database running on ec2. The answer gave some info which I had found already , and talks about using SSH if the ports are blocked. But I don't think that is the problem here.

All AWS EC2 instances have both an internal and external IP. By default, the XE install configured tnsnames and listener for the internal IP. Is it possible to configure the listener for both IPs? When I was a wee baby, I worked at Oracle. Even back then I remember having trouble with these files. Now in my senile old age, I have no clue.

Answer 1

@alex-poole, thanks for the help. Had to make a few more changes to get everything working.

The default config in TNSNAMES and Listner has too much and seems to break if as soon as you add something new. I got rid of anything that was not essential.

1. In TNSNAMES I left a single config, for the service name as opposed to SID, and added the external IP as you suggested.

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = YYY.YYY.YYY.YYY)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )

2. In the Listner file, I just set XE service as default.

DEFAULT_SERVICE_LISTENER = XE

3. With that above config, I was able to connect SQLDeveloper on the server.
4. By using the SSH connection in SQLDeveloper I was then able to connect from my laptop.

Answer 2

@alex-poole, I was thinking exactly the same thing about the SSH, but it wasn't working without it. So could you help me understand what is actually going on here? On the server, you see that I have put both IPs into TNSNAMES under the XE service (is that the right way to refer to it?) Then in the Listener.ora, I am just pointing to the XE service am I not?

On the server, this is the output of lsnrctl status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date                04-APR-2023 12:39:14
Uptime                    0 days 1 hr. 19 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\app\Administrator\product\21c\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\app\Administrator\product\21c\diag\tnslsnr\EC2AMAZ-21HKFFG\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=[AWSHOSTNAME])(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ADMINISTRATOR\PRODUCT\21C\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "d87270c138ed4411b8c05fba94d15f4a" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 2 handler(s) for this service...
The command completed successfully
PS C:\Users\Administrator>

So the xe instance seems to have created the services XE, XEDB, d87270c138ed4411b8c05fba94d15f4a, xedb1 services? I'm don't think I know what that means.

On the client side, I have neither tnsnames nor listener, which I why I guess I need to use the SSH connection? I installed the oracle instant client, but I don't see anything to indicate that sqldev is using that in any way.

This is the connection attempt without SSH (doesn't work)

This is the connection with SSH (does work)