4

I've been working this issue all day, reading posts here and other locations. I'm just not able to get past this error. I can connect just fine locally on my server (CentOS), but continue to get the TNS:no listener error when trying to connect via my Mac (OSX Mavericks) which is on the same subnet. Below are some notes I've captured based on what information I see that has been requested for this type of issue:

--------------------------------------------------------------------------------
MISC NETWORK CONFIG:
--------------------------------------------------------------------------------
[root@rubyrailssvr ~]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.14   rubyrailssvr.homenet.com
[root@rubyrailssvr ~]#


[root@rubyrailssvr ~]# more /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=rubyrailssvr.homenet.com
[root@rubyrailssvr ~]#
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
LISTENER.ORA File:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ more /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)
[oracle@rubyrailssvr ~]$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
TNSNAMES.ORA File:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ more /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[oracle@rubyrailssvr ~]$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
SQLNET.ORA File:
--------------------------------------------------------------------------------
jfhogarty@rubyrailssvr:/u01/app/oracle/product/11.2.0/xe/network/admin$ more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)
jfhogarty@rubyrailssvr:/u01/app/oracle/product/11.2.0/xe/network/admin$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
CHECK LISTENERS:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-AUG-2014 17:56:21

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                13-AUG-2014 17:46:45
Uptime                    0 days 0 hr. 9 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rubyrailssvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.14)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rubyrailssvr.homenet.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
ON THE CLIENT:
--------------------------------------------------------------------------------
➜  ~  more /usr/local/oracle/admin/network/tnsnames.ora
rubyrailssvr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XE))
  )

rubyrailssvr.homenet.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XE))
  )
➜  ~
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
TRYING TO CONNECT:
--------------------------------------------------------------------------------
➜  ~  sqlplus system@rubyrailssvr

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 13 18:21:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener


Enter user-name: % 
➜  ~



➜  ~  sqlplus system@rubyrailssvr.homenet.com

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 13 18:22:17 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener


Enter user-name: 
➜  ~
--------------------------------------------------------------------------------
3

First, thanks for the response. I'm working on a Mac, and the instant client doesn't include the tnsping utility. I had tried to login, from the remote computer, and that is where I got the TNS:no listener error. Logging in on the server is fine.

I had tried the telnet test before, but as I did not have telnet installed and running on the server, that test was not helpful. However, I decided to install telnet as it can be useful in testing. While doing so, I noticed that a command I had executed at the command line to open a port to iptables was not there. So I updated that file and now everything is working.

vim /etc/sysconfig/iptables

[BEFORE:]
# Generated by iptables-save v1.4.7 on Fri Jan 31 09:19:34 2014
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [6630:522769]
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Fri Jan 31 09:19:34 2014

[AFTER:]
# Generated by iptables-save v1.4.7 on Fri Jan 31 09:19:34 2014
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [6630:522769]
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p udp -m state --state NEW --dport 23 -j ACCEPT
-A INPUT -p tcp -m state --state NEW --dport 23 -j ACCEPT
-A INPUT -p tcp --dport 1521 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Fri Jan 31 09:19:34 2014

service iptables restart

Chased my tail so much yesterday that I never thought to review the iptables file - /slap head!

Thank you,

John... (HoGi)

2

My sequence of steps for troubleshooting

  1. Is the listener resolvable? C:\>tnsping MYDB (Confirms: the database listener is running on serverside)
  2. Is there a firewall issue? C:\>telnet hostname 1521
  3. Can I login? C:\>sqlplus system/pass@MYDB or C:\>sqlplus system/pass@hostname:1521/MYDB (Confirms: The database is open)
  4. Finally, chances of having a successful login with your favorite dev-tool has increased by 100%
0

In listener.ora file

set HOST=LOCALHOST

reason being that if your on a wireless network you can get assigned different ip addresses after every reboot which invalidates your listener settings if using a fixed ip address in your listener.ora file.

With HOST=LOCALHOST you are guaranteed consistency with regards the ip address settings between system reboots when it comes time that the listener service starts up.

0

Its too late but I also face this problem currently and I solved this by.

  1. Go to Net Manager(Oracle Net Manager) in your oracle installed system.
  2. Expand Local > Listners > LISTNER.
  3. Add new address protocol:TCP/IP, Host:YOURPCNAME.DOMAIN, Port:YOUR PORT.
  4. Go to File > Save Network Configuration.
  5. RESTART system.

below is snapshot for adding new address enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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