Ошибка tns 12541 tns no listener

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:\Users\ed>tnsping compdb

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. Then connect again.

• 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 has no listener service on port 1521.

Solution

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

[oracle@primary01 ~]$ lsnrctl status

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

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

ORA-12541: TNS:no listener error occurs when the listener for the requested resource is not started or configured incorrectly. The error happens if the listener is down when it tries to connect, or if it hasn’t been started or is set incorrectly. Furthermore, it might be caused by a network connectivity issue. Follow the steps given below to determine what could be causing the issue ORA-12541: TNS:no listener.

The oracle listener is used to connect to a database by listening to it. The Oracle database listener configurations are stored in the listener.ora and tnsnames.ora files. If someone tries to connect to the database, the connection configuration is compared to the settings in the listener.ora and tnsnames.ora files. If the configurations match, the database listener is not running or not started. It’s likely that when the listener initially started listening, they made an error. The listener might also be stopped or not started. If you restart the listener, the error ORA-12541: TNS:no listener will be resolved.

The Problem

The error ORA-12541: TNS:no listener will be thrown if the database listener is not started or stopped when attempting to create a database connection. It’s possible that the sqlplus command is operating OK. The error will be thrown when you try to connect to the database. The database connection cannot be established because the listener is unavailable and not listening.

oracle@localhost ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 7 09:36:12 2022
Version 19.3.0.0.0

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

Enter user-name: hr
Enter password: 
ERROR:
ORA-12541: TNS:no listener


Enter user-name: 

Solution 1

The Oracle database will throw this error if the listener is not started or terminated. Check the database listener’s status first. The database listener’s status may be obtained with the following command. To verify the status of the database listener, use the command below from the command line.

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-APR-2022 09:43:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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=0.0.0.0)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@localhost ~]$ 

Solution 2

Stop and restart the listener if you notice any abnormalities in the state of the listener. The database will shut off the listener if it is already running. It will display that the listener is not started if it is not running. Stop and start the listener to resolve the error ORA-12541: TNS:no listener.

[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-APR-2022 09:36:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-APR-2022 09:43:35

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

Starting /u01/app/oracle/product/version/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/version/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-APR-2022 09:43:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orclcdb
Listener Parameter File   /u01/app/oracle/product/version/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "orclcdb" has 1 instance(s).
  Instance "orclcdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$ 

Solution 3

If the error persists when connecting to the database, there might be a problem with the listener.ora setups. Examine the files listener.ora and tnsnames.ora. Modify the details if they differ from the values provided in the database connection string. This will resolve the error ORA-12541: TNS:no listener . The improper setting in the listener.ora and tnsnames.ora files will cause the database listener to fail to start. Ensure that all of the parameters are valid and that the database listener starts without problem.