Ошибка oracle 12154 tns невозможно разрешить заданный идентификатор соединения
I need to use PLSQL Developer to access oracle databases. I get the following error when I try to connect to my database. ORA-12154: TNS:could not resolve the connect identifier specified. I am able to use SQLPLUS from the command line to connect to the database, and tnsping returns successfully, but I can't figure out why PLSQL Developer will not work - it's using the right tnsnames.ora file, and the connection string in there is correct because it's the same one that tnsping uses.
Anyone have an idea what I can do to fix this? I've looked at other threads about this specific error with no luck.
tnsnames.ora
sqlpus command line image here
PLSQL Developer Connection Preferences: image here
Are you sure that PL/SQL Developer is using exactly the same TNS alias that you are using in SQL*Plus? My wild guess is that you're missing a domain (i.e. you need FOO.WORLD rather than FOO)
@JustinCave my computer doesn't have a domain name. If I run the command echo %userdomain% from the command line - I get my computer's hostname
I'm not talking about your computer's domain. I'm talking about the NAMES.DEFAULT_DOMAIN from your sqlnet.ora file, whether or not there is a domain on the TNS alias in your tnsnames.ora file (the .WORLD in FOO.WORLD), and whether or not that domain is present on the TNS alias that you are using in both SQL*Plus and PL/SQL Developer.
@JustinCave My tnsnames.ora file just contains my hostname without any domain name. My sqlnet.ora file does not have an entry for NAMES.DEFAULT_DOMAIN - do I need to give it a value?
I'm not talking about the domain on the hostname either. I'm talking about the domain (or lack thereof) on the TNS alias. Can you post your tnsnames.ora file, your sqlnet.ora file, the command line you are using for SQL*Plus, and exactly how you are configuring the connection in PL/SQL Developer?
Answers
here is my tns.ora
GISDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gisdb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
here is my listener .ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
)
here is my sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Hi,
I have some guidelines for you,can you please go through the details below:
Error: ORA-12154 / TNS-12154
Text: TNS:could not resolve service name
-------------------------------------------------------------------------------
Cause: The service name specified is not defined correctly in the
TNSNAMES.ORA file.
Action: Make the following checks and correct the error:
- Verify that a TNSNAMES.ORA file exists and is in the proper
place and accessible. See the operating system specific manual
for details on the required name and location.
- Check to see that the service name exists in one of the
TNSNAMES.ORA files and add it if necessary.
- Make sure there are no syntax errors anywhere in the file.
Particularly look for unmatched parentheses or stray characters.
Any error in a TNSNAMES.ORA file makes it unusable. See
Chapter 4 in the SQL*Net V2 Administrator's Guide. If
possible, regenerate the configuration files using the Oracle
Network Manager.
*** Important: The notes below are for experienced users - See Note:22080.1
Explanation:
The SQL*Net layer cannot find a definition for the alias supplied
(as in "sqlplus scott/[email protected]").
Diagnosis:
1) Check the tnsnames.ora file you are using, verify that it is accessible.
Eg: On Unix:
- Check if you have a $HOME/.tnsnames.ora file - This will be
used in addition to 'tnsnames.ora'.
- Check TNS_ADMIN is set in your environment.
- There is a readable tnsnames.ora file in $TNS_ADMIN
2) Ensure that the tnsnames.ora file contains a line of the form
'alias=(. )' for the alias you are specifying.
Aliases are NOT case sensitive.
3) Make sure that there are no mismatched parentheses in the
tnsnames.ora file.
4) Even if TNS_ADMIN is set SQL*Net looks in other locations for
configuration files. Check the default directories for old (or bad)
copies of TNS_NAMES.ORA. Eg: /etc, /var/opt/oracle,
$ORACLE_HOME/network/admin
5) Check the default domain name being used, and the path used to
locate aliases, in the SQLNET.ORA file.
The default domain is specified in the NAMES.DEFAULT_DOMAIN
parameter - this is appended to the alias specified in the
connect string if there is no domain given.
Eg: If NAMES.DEFAULT_DOMAIN=mydom.uk
and a connect to "scott/[email protected]" is requested
SQL*Net will look for the alias "mydb.mydom.uk"
If NAMES.DIRECTORY_PATH is also specified this determines where
SQL*Net looks for the alias expansion.
6) If none of these show an error enable client side tracing
at level 16 and see what has been written to the client trace
file. There list of aliases in the trace file under the heading
'TNS.NAMES.ORA TABLE HAS THE FOLLOWING CONTENTS'.
7) If ORA-12154 is returned when selecting over a database link from a
client check that the alias in the link can be resolved in the
tnsnames.ora file ON THE SERVER.
8) If you are connecting from a login dialog box, verify that you are
not placing an "@" symbol before your connect net service name.
9) When going from Windows to Linux/Unix platforms,
you can see characters at the
end of each line instead of . Be sure you
use ascii mode when ftp'ing this between Windows and Linux/Unix
to rafi : thanks rafi..but i think i've tried a lot of method..and it makes me sick to solve this error..
to admin : yes i've try your suggesstion..and it stil give me an error..
please can you post the output from tnsping GISDB executed on the host from where you are trying to connect to the DB?
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 29-APR-2
010 17:02:17
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_1\network\ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = alias-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gisdb)))
OK (20 msec)
the output of tnsping shows that you are able to connect to the listener listening on alias-PC on port 1521 and the listener knows of service gisdb.
Does an sqlplus /@gisdb work on the host were you executed the tnsping command?
What does an lsnrctl status executed on alias-PC telling you?
pimpom wrote:
hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.
could anyone help me on this?
ORA-12154: TNS:could not resolve the connect identifier specified
=================================
ORA-12154: TNS:could not resolve the connect identifier specified
This error means one thing, and one thing only. The client could not find the specified entry in the tnsnames.ora file being used.
As a follow-on to that statement, remember that when you use a dblink, the database in which the link is defined is acting as a client to the database that is the target of the link. So in this case, the tnsnames.ora file on the host of your source should have an entry for your target db, as defined in the db_link.
And for the umpteenth time . this error has nothing to do with the status of a listener. The connection request never got far enough to reach a listener. If anyone tells you to check a listener, they are not paying attention, or do not understand how TNS works. This error is the equivelent of not being able to place a telephone call because you don't know the number of the party you want to reach. You wouldn't debug that situation by going to the other guy's house and testing his telephone, or by going to the phone company and testing the switchboard. And you don't debug a ORA-12154 by checking the listener. If I had a top ten list of "Incredibly Simple Concepts (tm)" that should be burned into the brain of everyone who claims to be an Oracle DBA, it would include "ORA-12154 Has Nothing To Do With The Listener".
Assume you have the following in your tnsnames.ora:
Now, when you issue a connect, say like this:
tns will look in your tnsnames.ora for an entry called 'larry'. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).
Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.
Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you'll be connected.
A couple of important points.
First, the listener is a server side only process. It's entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.
Second, the tnsnames.ora file is a client side issue. It's purpose is for addressess resolution - the tns equivelent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.
What can go wrong?
First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.
Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"
Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"
Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"
Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"
Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"
Я пытаюсь подключиться к Oracle db из классического приложения ASP, однако я продолжаю работать с ошибкой ORA-12154.
TNSNAMES.ORA настроен правильно
C:Documents и настройкиUSERID.A>tnsping DBSOURCE
утилита TNS Ping для 32-битных Windows: Версия 10.2.0.4.0-производство 09-MAR-2 011 09:12:31
Copyright (c) 1997, 2007, Oracle. Все права защищены.
используемые файлы параметр : C:oracleproduct10.2.0client_1NETWORKADMINsqlnet.Ора!--2-->
Я могу создать соединение ODBC, и я проверил соединение, которое успешно.
Я проверил свой sqlnet.файл Ora
заменить sqlnet.AUTHENTICATION_SERVICES= (НЕТ)
имена.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Я также проверил, что Файл tnsnames.ORA как один из моих системных путей.
Я могу подключиться к БД с помощью SQLPLUS
вот код, который выдает ошибку
Я запускаю windows XP Pro 32bit SP3
Я тестировал это на машине коллег, и приложение asp не имеет проблем, я где-то пропустил конфигурацию или я испортил свою конфигурацию?
Спасибо за помощь всем!
кажется, все настройки были проверены, но вот мои предложения для некоторых проблем съемки:
- как насчет ограничений на учетную запись IIS работает под? Имеет ли он права доступа к файлам для чтения имен TNSNAMES? Вы пытались дать пользователю больше прав. Даже запустить IIS с локальной учетной записью системы? Не забудь вернуть все как было.
- вы уверены, что нет 3rd party app такого антивируса или брандмауэра, который может повлиять / заблокировать доступ. (Отключите их для тестирования, но не забудьте повторно включить :)
(Что Вы, наконец, сделали, чтобы решить эту проблему?)
" это работает на машине коллеги, но не мой."
ORA-12154 обычно является проблемой конфигурации, и это, безусловно, будет так в вашем случае. Короткий ответ-сравнить вашу машину с машиной вашего коллеги и выяснить, в чем разница.
вещи для проверки включают:
- в TNANAMES.Ora file
- на заменить sqlnet.Ora file
- слушатель.Ora-файл (если вы используете локальный база данных)
У вас есть несколько домов Oracle? Убедитесь, что IIS не указывает на другой ORA_HOME (и, следовательно, не находит ваши имена tnsnames.запись Ора). Я не очень хорошо знаком с конфигурацией IIS, поэтому я бы проверил это, добавив запись TNS ко всем tnsnames.Ора на моем компьютере.
Если вы используете 64-разрядную ОС и oracle 10, попробуйте установить все исправления для oracle. Что-то подобное произошло со мной, SQL plus и все остальное сработало, кроме моей .NET-программы. Я запускал программу .Net из папки" Program files (x86)". Установка патчей исправила это.
вероятной проблемой является отсутствие разрешения на чтение в каталогах Oracle пользователем IIS выполняется как.
Как говорит @ Derick,временно установите широко открытые разрешения в каталоге $ORACLE_HOME (Everyone = Full Control), перезапустите IIS и проверьте. Если это работает, предоставьте пользователю IIS разрешение на чтение для этого каталога.
попробуйте установить значение переменной среды TNS_ADMIN на машине с проблемой в /network / admin (или где бы ваши tnsnames.ora file lives) и посмотреть, поможет ли это.
У меня была аналогичная проблема и я нашел простое решение. Вам не нужно изменять файлы конфигурации. Надеюсь, это поможет вам:
что делает команда
изменить: Мне кажется, что имя экземпляра базы данных на самом деле не "orcl"? Вывод служб lsnrctl говорит мне, что служба "orcl", хотя и определена в прослушивателе.Ora файл, на самом деле не работает.
можете ли вы войти в систему с прямым подключением на сервере? Если да, то что вы используете в качестве Oracle_sid, вам нужно будет значение переменной окружения? Войдите в систему как пользователь SYS и введите команду:
ИЗМЕНИТЬ СИСТЕМНЫЙ РЕГИСТР;
затем снова выполните команду lsnrctl services и посмотрите, не появится ли дополнительный экземпляр.
кроме того, как указывает Алекс, команда tnsping сообщает полное имя службы. Редактирование заменить sqlnet.Ora файл и установить имена.Значение DEFAULT_DOMAIN равно NULL, если оно имеет значение.
редактировать 2: делает tnsping ud06 на сервер работы? Или мое предположение, что клиент и сервер находятся в разных системах неправильно?
ответ на эту проблему очень прост. Не беспокойтесь об этом .файлы ora или любая другая конфигурация. Oracle делает все это просто идеально.
только при подключении через командную строку он путается с паролями, в которых есть символ@.
поэтому при подключении через SQL командной строки Не используйте пароль с " @ " в нем. Просто используйте веб-интерфейс для создания учетной записи, имеющей пароль без символа " @ " в он.
вот!! Проблема решена. Я ломал голову в течение нескольких дней, и теперь моя проблема решена!!
на SERVICE_NAME на tnsping вывод не соответствует записи в tnsnames.ora ; это файл из окна Windows или окна Linux? Похоже, у вас нет локального (Windows) tnsnames.ora запись u06 и он угадывает, что имя службы должно расширять его - я думаю, что это то, что означает ссылка на адаптер имени хоста.
Я также сталкиваюсь с ORA-12154:TNS: не удалось разрешить указанный идентификатор подключения, и добавление пользователя, пытающегося подключиться к группе oinstall oracle, исправило его.
сначала попробуйте запустить экземпляр oracle:
Пуск - > Панель конфигурации - > Администрирование - >Службы - >ORACLESERVICEORCL (кстати, мой экземпляр) - >пуск
указать строку подключения следующим образом:
Я также столкнулся с той же ошибкой, попробуйте этот код:
и если вы обнаружите ту же ошибку, это означает, что у вас есть другое имя подключаемой базы данных. Проверьте имя подключаемой базы данных, просто написав следующую команду в sqlplus
я исправила эту проблему, используя эти шаги.
прежде всего , эта ошибка произошла, если вы не установили тот же каталог или диск.
- войдите в windows в качестве администратора.
- перейти к Панели Управления.
- Свойства системы и нажмите Enviroment
найдите переменную ОС и измените имя как "TNS_ADMIN"
и измените значение как " адрес каталога tnsnames"
Answers
ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ora-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
post content of tnsnames.ora
Hi I was informed I posted in the wrong forum, so I reposted. I understand why this is happening, but do not know how to fix it.
Why are you first connecting as SYS to the root/cdb if you really want to connect to a PDB as HR?
The most common mistake people make with 12c multitenant is trying to connect as a local user to the root/cdb.
1. Connecting as SYS doesn't use the TNSNAMES.ORA file
2. Connecting as SYS from the OS connects to the root/cdb - not to a PDB
3. PDBs do NOT open by default when you startup the database. If you want them to open automatically you either need to create an AFTER STARTUP trigger to open them or use the SAVE STATE when you clost a PDB>
4. If you want to use TNSNAMES.ORA to connect you MUST USE service_names that YOU create in the ora file.
The exception is telling you there IS NO connection info for PDBORCL in the TNSNAMES.ORA file
hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.
could anyone help me on this?
ORA-12154: TNS:could not resolve the connect identifier specified
12 Answers 12
The answer was simply moving the PLSQL Developer folder from the "Program Files (x86) into the "Program Files" folder - weird!
Thanks for posting this answer! I've been trying to troubleshoot this issue on a Windows 7 workstation along with the Network Engineers and the Oracle DBA at where I work.
The parentheses caused this problem for me. After renaming the directory that housed my application to exclude the parentheses, it fixed it.
This error is very common, often the very first one you get on trying to establish a connection to your database. I suggest those 6 steps to fix ORA-12154 :
- Check instance name has been entered correctly in tnsnames.ora.
- There should be no control characters at the end of the instance or database name.
- All paranthesis around the TNS entry should be properly terminated
- Domain name entry in sqlnet.ora should not be conflicting with full database name.
- If problem still persists, try to re-create TNS entry in tnsnames.ora.
- At last you may add new entries using the SQL*Net Easy configuration utility.
As I can't add a comment, just thought I'd post this for completion. tufy's answer is correct, it's to do with parenthesis (brackets) in the path to the application being run.
There is an existing networking bug where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.
Filed with Oracle, Bug 3807408 refers.
copy paste pl sql developer in program files x86 and program files both. if client is installed in other partition/drive then copy pl sql developer to that drive also. and run from pl sql developer folder instead of desktop shortcut.
ultimate solution ! chill
- It did not work so i switched to oracle sql developer and it worked with no problems (making connection under 1 minute).
- This link gave me an idea connect to MS Access so i created a user in oracle sql developer and the tried to connect to it in Toad and it worked.
Or second solution
you can try to connect using Direct not TNS by providing host and port in the connect screen of Toad
For me it was bad formatting of the tnsnames.ora connect identifier. The indentation of the identifier string is required as shown in the tnsnames.ora example in the comment.
Just wanted to add -- apparently this can also be caused by installing Instant Client for 10, then realizing you want the full install and installing it again in a parallel directory. I don't know why this broke it.
Also in addition to above solutions, also check the location where the tnsname ora file exists and compare with the path in the environment variable
I had an issue at work. The oracle server was "patched" and one of the databases I use could not be connect via the TNSNames entry but via Basic connection. The Database was up and admin could see it was up and running.
Addionally any application that used TNS for connecting to the database would not work either.
The problem found was that the database name was not correct in the TNS file but for some reason it's been working for years. Correcting the name fixed it for us. I did find that Oracle SQL Developer kept using the old TNS entry even after I updated it and I don't feel like reinstalling it for just one DB Connection. It appears that when the database was created it was given a smaller name then the others and via some cut and paste action within the TNSNames file it got mixed up. No one is sure how its worked as we're investigating it but the oracle patch ensured that the name had to be correct
An example of the name was it was down as "DBName.Part1.Part2" but in fact the DB name was "DBName"
Hi, I was able to connect to connect [email protected], but know I am not able to.
Do I need to re-install SQL Plus? Any advice and how to fix?
QL*Plus: Release 12.1.0.2.0 Production on Fri May 27 15:13:17 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> connect [email protected]
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Warning: You are no longer connected to ORACLE.
Читайте также: