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,0522 gold badges16 silver badges29 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,4161 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,6198 gold badges37 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
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.
- The listener is listening to another port other than the default one, e.g. 1522.
- The connection went for the wrong database server.
- Routing to the wrong destination.
This is the most common problem of ORA-12541 we have met. Just start it up and make sure it’s running.
Backup port like 1522 may be used by the listener for connection, you should ask for DBA to handle it.
Please check every parameter in your connect descriptor and make sure they are correct.
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.
Содержание
- aistikb
- Ошибка при входе в программу «ORA-12541: TNS no listener»
- Решение
- 1. Проверьте настройки подключения клиентского приложения.
- 2. На сервере проверьте запущены ли службы OracleServiceXE и OracleXETNSListener
- Ora 12541 tns no listener как исправить
- Ora 12541 tns no listener как исправить
- Ora 12541 tns no listener как исправить
- Ora 12541 tns no listener как исправить
aistikb
Ошибка при входе в программу «ORA-12541: TNS no listener»
Решение
1. Проверьте настройки подключения клиентского приложения.
Для этого необходимо запустить программу и в окне ввода пароля в поле «База данных» нажать кнопку «. »
Проверьте правильно ли в поле host указан IP адрес сервера на котором установлена база данных системы. Поля port и dbname должны иметь значения 1521 и XE соответсвенно.
Часто бывает что ПК выполняющий роль сервера базы данных полчает IP адрес по DHCP, через некоторый промежуток времени срок «аренды» IP адреса истекает и серверу выдается другой IP
адрес.
2. На сервере проверьте запущены ли службы OracleServiceXE и OracleXETNSListener
Если вывод как на рисунке 4, то попробуйте выполнить команду lsnrctl start
В случае успешного запуска вывод будет как на рисунке 3. В противном случае необходимо проверить конфигурацию сервиса OracleXETNSListener.
Конфигурация храниться в следующем файле:
1. C:oraclexeapporacleproduct10.2.0serverNETWORKADMINlistener.ora
Откройте данный файл с блокнотом и проверьте блок
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = thehostname)(PORT = 1521))
)
)
вместо thehostname далжен быть указан IP адрес сетевого интерфейса сервера через который производится подключение к БД.
Если значение не соответствует, то введите правильный IP и сохраните изменения.
3. На сервере проверьте запущен ли экземпляр oracle.
Иногда возможна ситуация, что сервис OracleServiceXE работает, но экземпляр базы данных не запущен.
В окне введите команду sqlplus sys as sysdba, на запрос пароля введите пароль для пользователя SYS (данный пароль задается при установке OracleXE).
Если при подключении выйдет сообщение Connected to the idle instance, то выполните команду startup и дождитесь ее завершения (рисунок 5).
Источник
Ora 12541 tns no listener как исправить
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID = CLRExtProc)
(ORACLE_HOME = C:oracleproduct10.2.0db_3)
)
(SID_DESC =
(SID = PLSExtProc)
(ORACLE_HOME = C:oracleproduct10.2.0db_3)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10.tulasoft.com)(PORT = 1522))
)
)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10.tulasoft.com)(PORT = 1522))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10.tulasoft.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.com)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
) 23 ��� 08, 15:05����[6217908] �������� | ���������� �������� ����������
 Re: ORA-12541: TNS:no listener [new] |
|
| tru55 Member ������: ��� |
1. ����� ����� ����� ��������� �� ������� ������������� ������. 2. ������ listener �� �� ������������ ����� 1521? ��� ���������� ��� ���? 3. ����� ������������ ����������� �����������, �.�. ��������� SID ���� � SID_LIST_LISTENER (����������� �����������) 4. ���� �������� � ���������, ��� ����� ��������� tnsping |
| 23 ��� 08, 15:11����[6217952] �������� | ���������� �������� ���������� |
| Re: ORA-12541: TNS:no listener [new] |
|||
| semenar Member ������: �������������� |
lsnrctl status |
||
| 23 ��� 08, 15:11����[6217954] �������� | ���������� �������� ���������� |
| Re: ORA-12541: TNS:no listener [new] |
| charodej Member Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias C:oracleproduct10.2.0db_3BIN>lsnrctl status Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle10.tulasoft.com)(P |
| 23 ��� 08, 15:29����[6218087] �������� | ���������� �������� ���������� |
| Re: ORA-12541: TNS:no listener [new] |
|
| tru55 Member ������: ��� |
�� ��� ���� ��������� listener ��������
���� ���������� ������� 1521 (�� ������� listener) |
| 23 ��� 08, 15:32����[6218106] �������� | ���������� �������� ���������� |
| Re: ORA-12541: TNS:no listener [new] |
|||
| Brodiaga Member ������: |
� ����� �� ���������? � ��� ������������ ��? |
||
| 23 ��� 08, 15:35����[6218139] �������� | ���������� �������� ���������� |
| Re: ORA-12541: TNS:no listener [new] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| tru55 Member ������: ��� |
|
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.
Contents
- Why Is Your System Is Showing Ora 12541 TNS No Listener Issue
- How To Solve This Error
- – ORA-12541 TNS No Listener Error While Using SQL *Plus
- – Example 2: ORA-12541 TNS No Listener in SSIS Error
- – Example 3: ORA-12541 TNS No Listener in Windows
- – Example 4: ORA-12541 TNS No Listener In Project
- FAQs
- – What Are TNS and Listeners?
- – What is a TNS File in SQL Developer?
- – How To Save a Tnsname File?
- Conclusion
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.
- Author
- Recent Posts
Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team
Grossmeister, а подробнее можете про то, как их запустить?
Добавлено через 5 минут
Grossmeister, OracleService вижу, Listener же не нашла..
У OracleService автоматический запуск установлен, но тем не менее он “лежит”, что же нужно сделать, чтобы он запускался и работал нормально?
Подскажите, пожалуйста!
Напишите подробно, как чайнику. Я уже не знаю, что с этой ошибкой делать и к кому обращаться, чтобы с этой ошибкой расправиться!
Добавлено через 35 минут
Фух, вроде заработало
0
