Ошибка tns 12541 tns no listener - Не ошибается лишь тот, кто ничего не делает!

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,0622 gold badges16 silver badges29 bronze badges

asked Nov 13, 2012 at 9:58

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,4161 gold badge18 silver badges27 bronze badges

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,6198 gold badges37 silver badges52 bronze badges

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

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

Источник

ORA-12541 is the same as TNS-12541, they all indicate that the listener is not running according to your specified destination.

Let’s see how we reproduce ORA-12541 and TNS-12541.

SQL*Plus throws ORA-12541

SQL> conn hr/hr@orcl ERROR: ORA-12541: TNS:no listener

TNSPING throws TNS-12541

C:Usersed>tnsping compdb


  TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:20:06
  Copyright (c) 1997, 2013, Oracle.  All rights reserved.
  Used parameter files:
 C:oracleappclientedproduct12.1.0client_1networkadminsqlnet.ora



  


                       
  




   Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
 TNS-12541: TNS:no listener

Cause

The connection cannot find the listener, it could be:

The listener is not started.

This is the most common problem of ORA-12541 we have met. Just start it up and make sure it’s running.

The listener is listening to another port other than the default one, e.g. 1522.

Backup port like 1522 may be used by the listener for connection, you should ask for DBA to handle it.

The connection went for the wrong database server.

Please check every parameter in your connect descriptor and make sure they are correct.

Routing to the wrong destination.

It’s rare, but it did happen in a very complex network environment. Part of connections are routed by some network device to an unknown server where no listener on port 1521.

Solution

Check the status and port of LISTENER on server side and start it if necessary.

[oracle@primary01 ~]$ lsnrctl status


  LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:22:34



  


                       
  




  Copyright (c) 1991, 2013, 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: 2: No such file or directory
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1521)))
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 111: Connection refused

As you can see, the database port is 1521 without questions. But the listener is not started. So let’s start it up.

[oracle@primary01 ~]$ lsnrctl start


  LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:23:59
  Copyright (c) 1991, 2013, Oracle.  All rights reserved.
  Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
  TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
 Log messages written to /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 Start Date                22-JUL-2014 19:23:59
 Uptime                    0 days 0 hr. 0 min. 2 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))
 The listener supports no services
 The command completed successfully

For more testing skills, you may check Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.

Источник

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@MYDB or C:>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.

Источник

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?

Источник

Step 1 — Edit listener.ora

Step 2 — Restart Oracle services

SQL*Plus throws ORA-12541

TNSPING throws TNS-12541

Cause

Solution

Error Message:

Resolution:

Step-1: First check your listener status.

Step-2: Start the listener, if listener is not running.

Step-3: Stop the listener, if listener is running.

Step-4: Now restart the listener.

Step-5: One last and very important thing which you have to do.

Step 1 — Edit `listener.ora`