1

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 (hostnamecommand) matches the service name output from lsnrctl status. The output shows localhost instead of ip-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: enter image description here
  • This database server is our dev instance so no license was installed
  • Updated listener.ora by setting the HOST value to 0.0.0.0, restarted with lsnrctl 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
Improve this question
7
  • Oracle doesn't require a license to run. But Development does need to be licensed
    – Michael Kutz
    Aug 3, 2020 at 19:42
  • @MichaelKutz Did you mean Production?
    – OSGI Java
    Aug 3, 2020 at 20:33
  • All Oracle installastions - both production and development - must be licensed.
    – pmdba
    Aug 3, 2020 at 22:40
  • As to your original question, are you sure that there is a firewall exception in place to allow the remote connections? It looks most likely that your connection is being blocked by something like that.
    – pmdba
    Aug 3, 2020 at 22:41
  • ORA-12541: TNS:no listener means exactly what it says: that you can reach the server on the port but that there is no listener running
    – miracle173
    Aug 4, 2020 at 4:06

2 Answers 2

Reset to default
1

No, the below does not indicate that you could reach the server through that port:

telnet ec2-pubIp.compute.amazonaws.com 1522
Trying pubIp...
telnet: connect to address pubIp: Connection refused
telnet: Unable to connect to remote host

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/

Improve this answer
2
  • Thank you so much! I will research and try your suggestions. On a side note, while telnet is not a perfect test, it shows that the port is reachable or in a different state to one that is closed, not responding. When you try to telnet to a port that is not opened, then you get a different error: telnet: connect to address xx.xxx.xxx.xxx: Operation timed out
    – OSGI Java
    Aug 4, 2020 at 19:18
  • Thank you again, the ssh tunneling and port forwarding in sql developer worked! One thing to mention when configuring the connection in sql developer, the service name must match the service name returned by running lsnrctl status. In other words, the default orcl did not work (which works fine with sqlplus on the ec2 instance).
    – OSGI Java
    Aug 4, 2020 at 21:56
0

This is not a full answer and I wrote parts of this answer already in a comment. But the error messages are very clear so I will them explain here because I am not sure if this is clear from Balazs' answer,. The correct interpretation helps when analyzing a problem.

From the sqlplus error message

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

you can conclude:

  1. you can reach port 1522 of ec2-pubIp.compute.amazonaws.com
  2. no Oracle listener is listening at port 1522 of ec2-pubIp.compute.amazonaws.com

From the telnet error message

telnet ec2-pubIp.compute.amazonaws.com 1522
Trying pubIp...
telnet: connect to address pubIp: Connection refused
telnet: Unable to connect to remote host

you can conclude

  1. you can reach port 1522 of ec2-pubIp.compute.amazonaws.com
  2. no process is listening at port 1522 of ec2-pubIp.compute.amazonaws.com

So both messages indicate that you can reach port 1522 of ec2-pubIp.compute.amazonaws.com

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.