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