Oracle exception вывести ошибку

The ora 12541 tns no listener is an issue that can happen due to various reasons such as if the listener is down, connectivity problem with the network, improper configuration, and if the connection has not been started. If you don’t know which of these reasons apply to the issue you are having, our coding experts are here to help you out! Read on as we inspect each of these causes and provide solutions.

Why Is Your System Is Showing Ora 12541 TNS No Listener Issue

The error message can be caused by some major reasons which are listed below and can make the user run into ora 12541 tns no listener whenever there is a problem with the listener. The error can be caused:

• If the database was briefly unavailable at the time when the user tried to access it
• If the oracle client application on the machine is not configured
• It can be due to a network connection problem, and if the connection has not started or if it went for a wrong database parameter
• If there are corrupted listener configurations files
• If the user is incorrectly registered or listened to another port other than the default
• If there is a complex network environment whereby the connection is routed by another device to an unknown server.

The warning syntax looks like this:

ERROR:
ORA-12541: TNS: no listener

How To Solve This Error

There are a few steps to take when resolving ora 12541 tns no listener issue.

You should make sure that the listener is up and running. To do this, go to the control panel into services under NT or listener control programs. This will show you the current status of the listener whether active or not. If the listener is not active i.e not running, restart the listener service by running the net start command by filling in “XX”.

What To Do After Checking the Listener Status? If the listener is active, the problem may be caused if it is not associated with the correct instance or protocol. To make sure all protocols or instances are correct, obtain the basic information about the listener and the configuration settings by using the  “STATUS” command. This will give you basic information about the listener and the configured protocol address, the summary of the services registered, and the services l handlers allocated to each service.

What to do next? Determine the type of status received:

• If the status is “Ready” the instance can accept connections
• If the status is “Blocked” the instance cannot accept connections
• If the status is Ready/Secondary, the primary and the second configuration is ready to accept connections
• If the status is *Unknown”, it is because the instance is registered in the Listener. ora file instead of the service registration

To avoid ora 12541 tns no listeners as a user, you should actively monitor and ensure adequate security.

Let us consider some major examples of possible ora 12541 errors you can encounter in your programs and how to solve them.

– ORA-12541 TNS No Listener Error While Using SQL *Plus

The ora 12541 tns no listener can also be caused when trying to connect to a database using SQL *Plus in oracle.

If you encounter such an issue when using SQL *Plus, there are steps to take to identify where the problem lies.

On the database server, connect to the database using an SQLNet connection which is

sqlplusacott/tiger@databasename

If the first method doesn’t work for you after trying it, don’t give up. There may be an issue with the connection setup on the server. What you can do is to check the listener log file and try tnsping from the server and see if the settings are the same with the client.

If you have challenges with the above solution, you can check the listener log file again for failed connections by increasing your trace level for adequate information.

The last problem to watch out for is the firewall. If you have tried all the above-mentioned methods and all failed, resolve the firewall problem first by disabling it and then try all over again.

– Example 2: ORA-12541 TNS No Listener in SSIS Error

The error ora 12541 tns no listener in SSIS is caused when connecting to oracle through SSIS.

If you are seeing this error when connecting to the oracle, you should check for tnsnames. ora. Check whether the listener is running and then check the path variable containing oracle here (oracle_himebin) for the solution.

If you are still experiencing the error, what you need to do is to troubleshoot the SSIS. To do that, you will use the database native tools to check the connectivity, using SQLPLUS.EXE. But if you have an issue when using the native tool, the problem is not an SSIS issue. If it is SSIS, check if you can resolve the host by using PING<hostname>. If this doesn’t work, try PING <IP address> and check the port with TELNET <host> <port>.

If it doesn’t work, then the problem has been detected. It is either the service is not listening or you need to open the port, which is:

SQL Server (default port 1433)

and a web server (default port 80 for unencrypted comms)

– Example 3: ORA-12541 TNS No Listener in Windows

Let’s consider an ora error in windows. We will provide solutions to errors due to an issue with the window box to the VM.

What we recommend that you should do is to check the VM/Windows step up, including the software and the address. To solve the ora-12541 problem relating to the window issue, always check the status of your listener, then open the command prompt and type Isnrctl status. If you get no listener, open the listener.ora file which is present in the following directory:

C:oraclexeapporacleproduct11.2.0servernetworkADMIN

After that, open the file and change the host parameter to your computer name ( You can get the computer name by right-clicking on My Computer and checking your computer name), and replace the host parameter with your computer name as follows.

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = Electron-PC)(PORT = 1521)) ) )

So here you can observe HOST = Electron-P, which is a computer name. Save the listener.ora file and again return to command prompt and type the following in command prompt lsnrctl start

There is another way to solve the problem, you can use this method if the solution above doesn’t work on your system. You have to uninstall all the oracle files from your computer if you had previously installed them. Stop all its services by going to services. msc in cmd.

What to do:

• Restart your computer
• Install oracle if you have not already
• After that, go to cmd
• Type services. msc in cmd, then select any service and type ‘O’. This will get to you all the services starting with O. Select OracleJobScheduerXE from the list. Right-click and go to properties
• In the general tab go to Startup type. Select automatic from it
• Click apply then click start and then OK
• Also, enable OracleXeClrAgent and start your service. So totally 4 services would be working out of 5 except OracleMTSRecoveryService
• Now go to cmd type lsnrctl->enter-> type status you should be able to see your instance

The oracle-12541 error was caused by changing the ownership to root. This error can be caused if the administration or user attempts to stop or start the listener by executing the command as a root or root-like user.

The error can be resolved by carrying out an operation in the form of the root. Stop the listener  (it may not stop cleanly)

su – oracle

lsnrctl stop

mv /var/tmp/.oracle /home/oracle

lsnrctl start

– Example 4: ORA-12541 TNS No Listener In Project

The ora-12541 TNS no listener, may occur when opening or validating a ReqPro project.

The error is caused because the connection request to the database could not be completed. After all, the listener is not running or the listening address supplied in the TNSNAMES. The ORA file is invalid.

To resolve the problem we need to check if the listener is active by running the lsnrctl utility on the Oracle server.

To start the lsnrctl utility:

• Select Start > Run…, then type CMD and click OK
• At the Command line. type in lsnrctl and hit Enter
• Type in status and hit Enter
• If the listener is not started you will receive several error messages that start with TNS.
• To start the listener type start from the command line and hit Enter.

If the listener has been started, and you continue to receive the error message, check if the destination address in the TNSNAMES.ORA file on the client system matches one of the listening addresses listed in the LISTENER.ORA file on the database server.

FAQs

– What Are TNS and Listeners?

A TNS name is like an alias to the service instance. The TNS listener service acts as a sort of lookup service. If the actual service you are trying to connect to via a TNS name isn’t valid, you will have an error.

The listener can be referred to as the “oracle Net listener” or the “oracle TNS listener“.

TNS stands for Transparent Network Substrate and it is the network protocol used by oracle for connectivity to the oracle database.

– What is a TNS File in SQL Developer?

Tnsnames.ora is a SQL*Net/Oracle Network Manager configuration file that describes databases and how to connect to them. The file contains the connection name, protocol, address, network port, and instance name. The tnsnames.ora file translates the Oracle Net service alias in the connection string to a network address, protocol, service, and instance name.

The tnsnames.ora plain text file is located on both the client and the server. Typically, this file is located under the oracle_homenetworkadmin directory. You can make changes to the file using any text editor.

– How To Save a Tnsname File?

In most cases, you may want to save the file at a location where you need administrator permissions to do so. There are two possible solutions for this:

Store the file somewhere elsewhere you don’t need admin permission (like your own documents or something like that).

Start the program you use to save the file as Administrator (if you have the permission to do so) and save the file anywhere you want. !Caution! If you save the file this way, you can overwrite other files with the same name even if they are critical and you can store the file in locations where a user can’t read it.

Conclusion

In the article, we have shown you the causes of ora 12541 tns no listener error and possible solutions. Let’s check the key points we mentioned in the article:

• The ora 12541 tns no listener is caused when the TNS name isn’t valid
• The error can be caused by reasons such as connectivity problems, and incorrect registration
• TNS is the network protocol used by oracle for connectivity to the oracle database
• The error can be caused if the listener is not associated with the correct protocol

Now that you have reached the article, you can avoid such errors and maintain a good database with your client.

[{«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»}}]

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

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:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.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. 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

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.