Ora 17002 ошибка

I encountered same problem with ORACLE 11G express on Windows.
After a long time waiting I got the same error message.

My solution is to make sure the hostname in tnsnames.ora (usually it’s not «localhost») and the default hostname in sql developer(usually it’s «localhost») same. You can either do this by changing it in the tnsnames.ora, or filling up the same in the sql developer.

Oh, of course you need to reboot all the oracle services (just to be safe).

Hope it helps.

I came across the similar problem again on another machine, but this time above solution doesn’t work.
After some trying, I found restarting all the oracle related services can fix the problem. Originally when the installation is done, connection can be made. Somehow after several reboot of computer, there is problem.
I change all the oracle services with start time as auto. And once I could not connect, I restart them all over again (the core service should be restarted at last order), and works fine.

Some article says it might be due to the MTS problem. Microsoft’s problem. Maybe!

I encountered same problem with ORACLE 11G express on Windows.
After a long time waiting I got the same error message.

My solution is to make sure the hostname in tnsnames.ora (usually it’s not «localhost») and the default hostname in sql developer(usually it’s «localhost») same. You can either do this by changing it in the tnsnames.ora, or filling up the same in the sql developer.

Oh, of course you need to reboot all the oracle services (just to be safe).

Hope it helps.

I came across the similar problem again on another machine, but this time above solution doesn’t work.
After some trying, I found restarting all the oracle related services can fix the problem. Originally when the installation is done, connection can be made. Somehow after several reboot of computer, there is problem.
I change all the oracle services with start time as auto. And once I could not connect, I restart them all over again (the core service should be restarted at last order), and works fine.

Some article says it might be due to the MTS problem. Microsoft’s problem. Maybe!

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