ORA-12154: TNS:could not resolve the connect identifier specified
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
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
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
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
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install «Software Only», this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
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
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 ™» 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 error code 12154
Oracle Net Services provides methods for understanding, testing and resolving network problems. Oracle Database includes utilities, and log and trace files for testing and diagnosing network connection and problems. The TNSPING and TRCROUTE utilities test connectivity. The log and trace files keep track of the interaction between network components as errors occur. Evaluating this information helps to diagnose and troubleshoot network problems.
Understand the common testing procedures and network errors, and outline procedures for resolving problems. Also, learn methods for logging and tracing error information to diagnose and troubleshoot more complex network problems.
- Understanding Automatic Diagnostic Repository
- Diagnosing Oracle Net Services
- Resolving the Most Common Error Messages for Oracle Net Services
- Troubleshooting Suggestions for Oracle Net Services
These are the suggestions for diagnosing network problems and troubleshooting Oracle Connection Manager in Traffic Director Mode. - Example of Troubleshooting a TNS-12154 Error
- Logging Error Information for Oracle Net Services
- Tracing Error Information for Oracle Net Services
- Contacting Oracle Support Services
16.1 Understanding Automatic Diagnostic Repository
The Automatic Diagnostic Repository (ADR) (ADR) is a systemwide tracing and logging central repository. The repository is a file-based hierarchical data store for depositing diagnostic information, including network tracing and logging information.
The ADR home is the unit of the ADR directory that is assigned to an instance of an Oracle product. Each database instance has its own ADR home. Similarly, each listener, Oracle Connection Manager, and client instance has its own ADR home.
In case of a process failure, an incident is generated. The incident dump files are located in the ADR_BASE/ADR_HOME/incident/ directory, By default, ADR_BASE is ORACLE_BASE if the ORACLE_BASE variable is set. If the variable is not set, then ADR_BASE is ORACLE_HOME/log . ADR_BASE can be set to any location.
The incident dump file location can be found inside the process trace file.
The location of an ADR home is given by the following path, which starts at the ADR base directory:
The following table lists the values of the path components for an Oracle Net Listener instance.
Table 16-1 ADR Home Path Components for an Oracle Net Listener Instance
| Path Component | Value for Oracle Net Listener | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Path Component | Value for Oracle Connection Manager | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Subdirectory Name | Contents | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Parameter | DIAG_ADR_ENABLED=ON | DIAG_ADR_ENABLED=OFF | |||||
|---|---|---|---|---|---|---|---|
| Parameter | DIAG_ADR_ENABLED=ON | DIAG_ADR_ENABLED=OFF | ||
|---|---|---|---|---|
| Parameter | DIAG_ADR_ENABLED=ON | DIAG_ADR_ENABLED=OFF |
|---|---|---|
| Protocol | Verify that you can. |
|---|---|