I am new on Oracle database, but I have one issue.
On my Database server (server1) listener and database instance run correctly and I can use sqlplus to connect to this DB.
When I connect to database by using other server, I check the TNS configuration and it is correct but system says:
ERROR: ORA-12541: TNS:no listener
My database is Oracle 10gR2
So how can I solve this issue?
Gryu
2,0142 gold badges15 silver badges27 bronze badges
asked Nov 13, 2012 at 9:58
10
You need to set oracle to listen on all ip addresses (by default, it listens only to localhost connections.)
Step 1 — Edit listener.ora
This file is located in:
- Windows:
%ORACLE_HOME%networkadminlistener.ora. - Linux: $ORACLE_HOME/network/admin/listener.ora
Replace localhost with 0.0.0.0
# ...
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
)
)
# ...
Step 2 — Restart Oracle services
-
Windows: WinKey + r
services.msc -
Linux (CentOs):
sudo systemctl restart oracle-xe
answered Feb 19, 2014 at 14:58
Mikael HolmgrenMikael Holmgren
2,3861 gold badge18 silver badges27 bronze badges
10
I also faced the same problem but I resolved the issue by starting the TNS listener in control panel -> administrative tools -> services ->oracle TNS listener start.I am using windows Xp and Toad to connect to Oracle.
answered Jul 1, 2013 at 5:53
subhashissubhashis
4,5938 gold badges36 silver badges52 bronze badges
2
Check out your TNS Names, this must not have spaces at the left side of the ALIAS
Best regards
answered Oct 31, 2013 at 0:31
ArmandArmand
511 silver badge5 bronze badges
0
According to oracle online documentation
ORA-12541: TNS:no listener
Cause: The connection request could not be completed because the listener is not running.
Action: Ensure that the supplied destination address matches one of the addresses used by
the listener - compare the TNSNAMES.ORA entry with the appropriate LISTENER.ORA file (or
TNSNAV.ORA if the connection is to go by way of an Interchange). Start the listener on
the remote machine.
answered Nov 13, 2012 at 11:37
dariyooshdariyoosh
6041 gold badge4 silver badges12 bronze badges
1
Problem
User tries to launch Controller. User receives error message on screen.
If they look at their client PC’s ‘application’ Event Log, there is an entry there, corresponding with the time when they tried to logon.
On the Controller application server, there are periodic VBRUNTIME errors.
Symptom
Client PC screen:
An error occurred while trying to access the server
Client PC Event Log:
Event Type: WarningEvent Source: Cognos ControllerEvent Category: NoneEvent ID: 0Date: 1/31/2008Time: 10:20:10 AMUser: N/AComputer: PCNAMEDescription:Error occured at 1/31/2008 10:20:10 AM in Cognos Controller, Error No=5, Source=FrangoDirect.ServerInfoD.GetServerInfo#ControllerProxyClient, Description=System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Runtime.InteropServices.COMException (0x80004005): ORA-12541: TNS:no listener at FrServerB.ServerInfoClass.GetServerInfo(String sGuid, String sUser) at Cognos.Controller.Proxy.CCRWS.ServerInfo_GetServerInfo(String sGuid, String sUser) — End of inner exception stack trace —, HelpFile= HelpContext=0
Application Server Event Log:
- Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 1/31/2008
Time: 10:08:02 AM
User: N/A
Computer: SERVERNAME
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 3424 ,Logged: Error occured at 1/31/2008 10:08:02 AM in FrSrvFunc, Error No=-2147467259, Source=Server function:Db_CreateConnection(..) , Description=ORA-12541: TNS:no listener, HelpFile= HelpContext=0
And:
- Event Type: Error
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 1/31/2008
Time: 10:08:02 AM
User: N/A
Computer: SERVERNAME
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 3424 ,Logged: Connection Error: Udl=File Name=C:Program Filescognosc8DataDEVELOPMENT.Udl But security is OFF!!!
Error No=-2147467259, Source=OraOLEDB, Description=ORA-12541: TNS:no listener, Native Error=12541, Sql State =
In addition, if using TNSPING, you may get the error ‘TNS-12541: TNS: no listener‘.
Cause
There are many potential causes for ‘ORA-12541 TNS:no listener’ errors. For example:
- Scenario #1 — Tnsnames.ora file does not have the correct information required to connect to
the Oracle instance
- See separate IBM Technote 1346169 for more details
- Scenario #2 — Oracle server has more than one network card, but only 1 card is configured
- See separate IBM Technote 1353150 for more details
- Scenario #3 — IBM Cognos server has Oracle 9 client installed, but the data source is hosted on an Oracle 10 database server.
- Scenario #4 — Server has Symantec Firewall installed. By default, this blocks TNSPING
- See separate IBM Technote 1372042 for more details
- Scenario #5 — Problem with sqlnet.ora file
- See separate IBM Technote 1342952 for more details
However, this IBM Technote specifically deals with the scenario where the Oracle server was not operational/misconfigured. In one customer’s specific case, the Oracle server’s listener was not configured correctly.
Resolving The Problem
Ask Oracle DBA to check the Oracle server, to solve connection problem.
NOTE: Oracle is a third party (non-IBM) piece of software. IBM cannot give any official advice on third-party (non-IBM) products.
However, in one customer’s case, the problem was solved by deleting and re-creating the Oracle Listener.
Steps to delete/re-create an Oracle Listener:
- Obtain a short period of downtime
- Logon to Oracle server as an administrator
- Click «Start — Programs — Oracle — OraDb10g_home1 — Configuration and Migration Tools — Net Configuration Assistant»
- Select «Listener configuration» and choose ‘Next’
- Choose ‘Delete’.
- Use the wizard to delete the listener(s).
- Repeat steps 4, 5 and 6 until *all* listeners have been deleted
- Choose ‘Add’ then ‘Next’
- Give the listener a suitable name (for example default = LISTENER) and click ‘Next’
- Ensure that the ‘Selected Protocols’ is correct (typically, this should only include ‘TCP’) and click ‘Next’
- Choose the TCP port (typically accept the default ‘1521’) and click ‘Next’
- Finish
- Test.
Related Information
[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.2″,»Edition»:»All Editions»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
Historical Number
1038229
when i was trying to connect my remote server as-
sqlplus SYSTEM/oracle@192.168.0.171:1521/kannel
it shows me this error-
SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 15 15:36:52 2013
copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
I have checked my listener, it is running
[root@roracle bin]# ./lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-NOV-2013 15:42:24
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
tnsnames.ora file contain —
KANNEL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kannel)
)
)
listener.ora file contain
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = kannel)
(ORACLE_HOME = /opt/u01/app/product/11.2.0/dbhome_1)
(SID_NAME = kannel)
)
)
LISTENER=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)
sqlnet.ora file contain-
SQLNET.AUTHENTICATION_SERVICES= (ALL)
NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)
Oracle home path —
$ORACLE_HOME=/opt/u01/app/product/11.2.0/dbname_1
Oracle base path —
$ORACLE_BASE=/opt/u01/app
i have set environment variable
[root@roracle bin]# env | sort
}
_=/bin/env
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/root
HOSTNAME=roracle
LANG=en_IN
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOADEDMODULES=
LOGNAME=root
LS_COLORS=rs=<......something........>:
MAIL=/var/spool/mail/root
module=() { eval `/usr/bin/modulecmd bash $*`
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
MODULESHOME=/usr/share/Modules
OLDPWD=/root
ORACLE_BASE=/opt/u01/app/
ORACLE_HOME=/opt/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=kannel
PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/opt/u01/app/product/11.2.0/dbhome_1/bin
QTDIR=/usr/lib64/qt-3.3
QTINC=/usr/lib64/qt-3.3/include
QTLIB=/usr/lib64/qt-3.3/lib
SHELL=/bin/bash
SHLVL=1
SSH_CLIENT=192.168.0.83 35604 22
SSH_CONNECTION=192.168.0.83 35604 192.168.0.171 22
SSH_TTY=/dev/pts/0
TERM=xterm
USER=root
When i access database as a localhost —
./sqlplus / as sysdba[oracle@roracle bin]$ ./sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 16:04:02 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select v$database;
select v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
I Don’t understand where i am mistaking…..Can somebody help?
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:
➜ ~
--------------------------------------------------------------------------------
asked Aug 13, 2014 at 22:43
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)
answered Aug 14, 2014 at 12:56
HoGiHoGi
711 gold badge1 silver badge6 bronze badges
My sequence of steps for troubleshooting
- Is the listener resolvable?
C:>tnsping MYDB(Confirms: the database listener is running on serverside) - Is there a firewall issue?
C:>telnet hostname 1521 - Can I login?
C:>sqlplus system/pass@MYDBorC:>sqlplus system/pass@hostname:1521/MYDB(Confirms: The database is open) - Finally, chances of having a successful login with your favorite dev-tool has increased by 100%
answered Aug 14, 2014 at 5:20
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.
answered Nov 6, 2015 at 15:48
Its too late but I also face this problem currently and I solved this by.
- Go to Net Manager(Oracle Net Manager) in your oracle installed
system. - Expand Local > Listners > LISTNER.
- Add new address protocol:TCP/IP, Host:YOURPCNAME.DOMAIN, Port:YOUR PORT.
- Go to File > Save Network Configuration.
- RESTART system.
below is snapshot for adding new address
answered Nov 19, 2016 at 9:04
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:
➜ ~
--------------------------------------------------------------------------------
asked Aug 13, 2014 at 22:43
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)
answered Aug 14, 2014 at 12:56
HoGiHoGi
711 gold badge1 silver badge6 bronze badges
My sequence of steps for troubleshooting
- Is the listener resolvable?
C:>tnsping MYDB(Confirms: the database listener is running on serverside) - Is there a firewall issue?
C:>telnet hostname 1521 - Can I login?
C:>sqlplus system/pass@MYDBorC:>sqlplus system/pass@hostname:1521/MYDB(Confirms: The database is open) - Finally, chances of having a successful login with your favorite dev-tool has increased by 100%
answered Aug 14, 2014 at 5:20
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.
answered Nov 6, 2015 at 15:48
Its too late but I also face this problem currently and I solved this by.
- Go to Net Manager(Oracle Net Manager) in your oracle installed
system. - Expand Local > Listners > LISTNER.
- Add new address protocol:TCP/IP, Host:YOURPCNAME.DOMAIN, Port:YOUR PORT.
- Go to File > Save Network Configuration.
- RESTART system.
below is snapshot for adding new address
answered Nov 19, 2016 at 9:04
N no. of articles you will find on the internet about “ORA-12541 TNS no listener error” but still finding an effective solution. Don’t worry this solution helped me a lot and I hope it will help you too.
I got the same issue while working on Oracle 10g database then I decided to share my experience and steps to resolve this issue.
Error Message:
ORA-12541 TNS no listener error
Resolution:
Now you have to follow below mentioned steps to resolve the ORA-12541 issue.
Step-1: First check your listener status.
C:>lsnrctl status
The above command will show the current status of your listener.
Step-2: Start the listener, if listener is not running.
C:>lsnrctl start
This command will start all your listener services.
Step-3: Stop the listener, if listener is running.
C:>lsnrctl stop
This command will stop all your listener services.
Step-4: Now restart the listener.
C:>lsnrctl reload
This command will restart all your listener services.
Note: If you want to see all the available listener commands. Type lsnrtcl help on command prompt and you will get all available options.
C:>lsnrctl help
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 02-JUL-2015 20:25:14
Copyright (c) 1991, 2010, Oracle. All rights reserved.
The following operations are available
An asterisk (*) denotes a modifier or extended command:start stop status
services version reload
save_config trace change_password
quit exit set*
show*
Step-5: One last and very important thing which you have to do.
Open sqlnet.ora file and make the changes as suggested.
Change NTS to NONE
SQLNET.AUTHENTICATION_SERVICES = (NONE)
Now restart the listener services and your listener problem ORA-12541 will be resolved.
If this is not working, do write in comment section. I will try to help you at the earliest.