Oracle sql developer ошибка 17002

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:
  
• 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

Most probably you have listener configured wrongly, the hostname you specify in connection string must be the same as in the listener.

First check the Firewall and network related issues.

Check if Oracle Listener service is available and running. If not you may use Oracle Net Configuration Assistant tool to add and register new listener.

If the above steps are ok then you need to configure Oracle Listener appropriately. You may use Oracle Net Manager tool or edit “%ORACLE_HOME%networkadminlistener.ora” file manually.

There are 2 options that need to be considered carefully:
Listening Locations associated with the Listener – Hostname(IP) and Port in Listening Location must exactly match the ones used in the connection string.

For example, if you use 192.168.74.139 as target hostname, then there must be Listening Location registered with the same IP address.

Also make sure the you use the same SID as indicated in Database Service associated with the Listener.

Fix Oracle “IO ERROR: The network adapter could not establish the connection” error

Most probably you have listener configured wrongly, the hostname you specify in connection string must be the same as in the listener.

First check the Firewall and network related issues.

Check if Oracle Listener service is available and running. If not you may use Oracle Net Configuration Assistant tool to add and register new listener.

If the above steps are ok then you need to configure Oracle Listener appropriately. You may use Oracle Net Manager tool or edit “%ORACLE_HOME%networkadminlistener.ora” file manually.

There are 2 options that need to be considered carefully:
Listening Locations associated with the Listener – Hostname(IP) and Port in Listening Location must exactly match the ones used in the connection string.

For example, if you use 192.168.74.139 as target hostname, then there must be Listening Location registered with the same IP address.

Also make sure the you use the same SID as indicated in Database Service associated with the Listener.

Fix Oracle “IO ERROR: The network adapter could not establish the connection” error

Hey guys! Have you ever faced the TNS IO error saying “The network adapter could not establish the connection”? when you are trying to make a new connection in SQL Developer or logging on to the database using SQL*Plus editor. If yes then you are at the right place because here in this blog I am going to show you how you can solve this error. 

So without further ado let’s proceed to solve this badass TNS error.

Step 1: Make sure your entries are correct

First of all to solve “The network adapter could not establish the connection” error, check whether you have entered the correct username and password as well as the correct Hostname and Port number. Though these are small things we cannot avoid them. After all “It’s the small things in a relationship that means the most.” 

[bctt tweet=”How to solve “The network adapter could not establish the connection” error in just 2 steps” username=”RebellionRider”]

For the valid hostname and port number, you can check the Listener.ora if you have access to your server as the listener is a server process. In case you don’t have access to listener.ora file then you can check the tnsnames.ora file.

In case of Listener.ora file check for Host and Port entry in Listener tag for valid hostname and port number. 

Listener.ora

And if you are using TNSnames.ora file for validating the hostname and port number then search for the entry which has the same name as your SID and then check for host and port number entry in that particular tag.

TNSnames.ora

Now try connecting. If this solves your problem then you can avoid the next step. But if “The network adapter could not establish the connection” error is still there, then don’t worry just follow the next step.

Step 2: Is your listener taking a rest?

If your problem is still there and you haven’t gotten rid of that nasty error then there is a possibility that your Listener is not running. To check whether Listener is up and running or not, simply open up your command prompt with Administrative privileges and Write 

C:> lsnrctl status

This command will show you the status of your listener. If the output of this command looks like the one shown in the picture below then it clearly means your listener is not running thus next what you have to do is to start the listener. 

To start the listener you just have to write

C:> lsnrctl start.

This will start your listener and most probably solve your problem.

Info Byte:
In case you are having problems in starting your listener then try starting “OracleOraDb11g_home1TNSListener” windows service. For that open your Run Command and write services.msc this will open your service panel. Here search for the same and right click to start it.

That’s all you have to do. This must solve the “The network adapter could not establish the connection” error. May the force be with you. Enjoy.

Also, click here to learn how to do initial configuration of Orcale Enterprise Manager to start working with it. 

If you find this helpful then please do share it on your social network and help me reach out to more people. Also if you have other queries then reach out to me on my Facebook Page & Twitter. Thanks, guys & have a great day!

SQL Developer FAQ