I have an Oracle database 12c standard edition release 12.2.0.1.0 running on a Linux instance of AWS ec2. When I try to connect to it remotely from my machine (macOS) using SQL Developer I get an IO Error: The Network Adapter could not establish the connection
Vendor Code 17002
error.
- connecting using sqlplus from my machine:
sqlplus cd2/ @ec2-publicIp.myCloud.compute.amazonaws.com:1522/orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 3 15:30:56 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
- On the ec2 instance, Security Groups, a TCP 1522 port is open for my machine's public IP
- telnet to the instance, port 1522 seems to indicate that the port is open:
telnet ec2-pubIp.compute.amazonaws.com 1522
Trying pubIp...
telnet: connect to address pubIp: Connection refused
telnet: Unable to connect to remote host
- Port 1522 is used consistently across all the configurations
- I verified that the ec2 hostname (
hostname
command) matches the service name output fromlsnrctl status
. The output showslocalhost
instead ofip-localIp.myCloud.compute.internal
.
$ hostname
ip-localIp.myCloud.compute.internal
$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-AUG-2020 15:20:44
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-localIp.myCloud.compute.internal)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 03-AUG-2020 13:30:22
Uptime 0 days 1 hr. 50 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/centos/diag/tnslsnr/ip-localIp/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "orcl.myCloud.compute.internal" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.myCloud.compute.internal" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
- The hostname and port also matches the default listener configured in
listener.ora
:
cat /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/centos/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-localIp.myCloud.compute.internal)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
- Oracle parameters are as shown, again both host and port match:
SQL> show parameter listener_networks
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST =
ip-localIp.myCloud
.compute.internal)(PORT = 1522
))
SQL> show parameter remote_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string
- I can connect from the ec2 instance:
sqlplus / as sysdba
- I can connect from the ec2 instance as a schema/user cd2:
sqlplus cd2/ @orcl
SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 3 18:04:13 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Mon Aug 03 2020 18:03:52 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SP2-0310: unable to open file "orcl.sql"
SQL> select 1 from dual;
1
----------
1
SQL>
- SQL Developer connection matches both the port and ec2's public domain name:
- This database server is our dev instance so no license was installed
- Updated
listener.ora
by setting theHOST
value to0.0.0.0
, restarted withlsnrctl
but the result was the same:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
/etc/hosts
content:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-localIp.myCloud.compute.internal
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
- I also tested it with all inbound traffic and all ports opened for the ec2 instance
Best Answer
No, the below does not indicate that you could reach the server through that port:
This is just a generic network problem. The 17002 error can be reproduced any time at will by simply entering incorrect host or port at connection details.
Your listener listens on the internal address which in your case is the 127.0.0.1 address of the loopback adapter. But you tried connecting through the external address.
You need some forwarding/firewall rule in your cloud infrastructure. Or you need to change the listener configuration so it would listen on the external address. But even if you do that, it may not work without the above.
A better options is to encapsulate the connection in an SSH tunnel. You can set up the tunnel via your preferred SSH client (this is for Oracle Cloud, but the concept is the same Creating an SSH Tunnel to a Compute Node Port). Even SQL Developer has some built-in functionality for that: Connecting Remotely to the Database by Using Oracle SQL Developer.
Or here is a blog post about setting up an SSH tunnel in SQL Developer with screenshots: https://learncodeshare.net/2016/06/02/sql-developer-oracle-database-connection-through-an-ssh-tunnel/