Troubleshooting Guide for the TNS-12154 Error
(Also Applies to Troubleshooting TNS-03505)
The TNS-12154 (ORA-12154) means that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
Before starting, it is recommended that a print out or a view of both the TNSNAMES.ORA and the SQLNET.ORA files are available. Looking at these files at the same time is helpful since references will be made to both.
1.1 Locating the TNSNAMES.ORA and SQLNET.ORA
The default locations for these files are:
- Windows NT/2000/2003/2008 client
ORACLE_HOME\network\admin
- UNIX Client
$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle
or /etc
or /var/opt/oracle
2.1 UNIX Client/Server - Troubleshooting the ORA-12154
The TNSNAMES.ORA and SQLNET.ORA files should resemble the following example:
Table 2-1: Sample .ORA Files
To begin the diagnostic process, determine which section of this document applies to the problem. For example, using the sample files shown in Table 2-1 along with the chart in Table 2-2, refer to section 2.2 of this solution. This is because the alias in the TNSNAMES.ORA sample file is DEV1.WORLD and the parameter NAMES.DEFAULT_DOMAIN=world does not exist in the SQLNET.ORA file. Use the Oracle configuration files along with Table 2-2 to determine where troubleshooting should begin.
Table 2-2: Solution Chart
TNSNAMES.ORA Alias
|
NAMES.DEFAULT_DOMAIN
|
Go to Section
|
[alias].world
|
YES
|
2.4
|
[alias].world
|
NO
|
2.2
|
[alias]
|
YES
|
2.3
|
[alias]
|
NO
|
2.4
|
2.2 Add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again. If the TNS-12154 still persists, go to section 2.4.
2.3 Remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file. After removing the parameter, save the SQLNET.ORA file, and try connecting again. If the TNS-12154 still persists, go to section 2.4.
2.4 If the files were transferred from the client to the server, check the configuration files and ensure that CTRL-M (^M) or CTRL-R (^R) characters were not inserted at the ends of any lines. If everything seems okay, continue to section 2.5.
2.5 If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example:
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
This parameter is not needed, but if it exists and looks okay, continue to section 2.6.
2.6 The configuration files are most likely technically accurate. At the Unix prompt, echo the TNS_ADMIN environment variable.
% echo $TNS_ADMIN
If nothing is returned, try setting the TNS_ADMIN environment variable to explicitly point to the directory where the TNSNAMES.ORA file is located. The filename is NOT included in TNS_ADMIN.
In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file
In K Shell, the syntax is:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN
Try the connection again. If it fails, go to the next section 2.7.
2.7 In the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. Try the connection again. If this still fails, check the permissions of the TNSNAMES.ORA and SQLNET.ORA file and parent directories. Usually .ora files are either -rwxrwxrwx or -rwxrwx---. In any event, changing permissions of the .ora files to 777 will set the permissions to fully open. If problems still exist, continue to the next section (2.8) in which the configuration will be redone. Please note that a permissions setting of 777 will allow anyone on the system to access the configuration files. This step should be done as a temporary test.
2.8 Set the TNS_ADMIN environment variable to '/tmp'. Go to the /tmp directory and vi a new tnsnames.ora file. Type in the sample TNSNAMES.ORA from Table 2-1 into vi. Save the new TNSNAMES.ORA file and exit vi. At the command prompt, type:
% sqlplus scott/tiger@dev1.world
This should either connect or progress to the next logical error.
3.1 Windows NT Client/Server - Troubleshooting the ORA-12154
One of the most common reasons for getting a TNS-12154 on 32-bit platforms is that both SQL*Net 2.x and Net8 are installed on a client, and the application being used (i.e. SQL*Plus) is trying to access the wrong TNSNAMES file. Or, data might be changed
to a TNSNAMES.ORA file in a 32-bit Oracle Home, but a 16-bit application is being used.
to a TNSNAMES.ORA file in a 32-bit Oracle Home, but a 16-bit application is being used.
16-bit versus 32-bit
When starting SQL*Plus, a "connect" dialog box is presented. If this connect box is white and/or has non 3-D text fields, then the 16-bit version of SQL*Plus is being used and instructions should be followed in section 5.1 "Troubleshooting ORA-12154 on Windows 3.X".
If the connect dialog box is gray and/or has 3-D text fields, then the 32-bit version of SQL*Plus is being used and instructions should be followed for this solution.
If 32-bit SQL*Net is being used and the version of SQL*Plus is 3.x or SQL*Plus 8.1, then use the TNSNAMES.ORA file in the ORANT\Network\Admin directory. If the version of SQL*Plus is 4.x, then use the TNSNAMES.ORA file in the ORANT\Net80\Admin directory.
Troubleshooting
To begin the diagnostic process, determine which section of this document applies to the problem. For example, using the sample files shown in Table 3-1 along with the chart in Table 3-2, refer to section 3.2 of this solution. This is because the alias in the TNSNAMES.ORA sample file is DEV1.WORLD and the parameter NAMES.DEFAULT_DOMAIN=world does not exist in the SQLNET.ORA file. Use the Oracle configuration files along with Table 3-2 to determine where troubleshooting should begin.
Table 3-1: Sample .ORA files
Table 3-2 Solution Chart
TNSNAMES.ORA Alias
|
NAMES.DEFAULT_DOMAIN
|
Go to Section
|
[alias].world
|
YES
|
3.4
|
[alias].world
|
NO
|
3.2
|
[alias]
|
YES
|
3.3
|
[alias]
|
NO
|
3.4
|
3.2 Add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again. If the TNS-12154 still persists, go to section 3.4.
3.3 Remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file. After removing the parameter, save the SQLNET.ORA file, and try connecting again. If the TNS-12154 still persists, go to section 3.4.
3.4 If the files were transferred from the client to the server, check the configuration files and ensure that CTRL-M (^M) or CTRL-R (^R) characters were not inserted at the ends of any lines. Check from a DOS prompt that the TNSNAMES.ORA file is not called TNSNAMES.ORA.TXT. This can happen when editing the files from Notepad. If everything seems okay, continue to section 3.5.
3.5 If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example:
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
This parameter is not needed, but if it exists and looks okay, continue to section 3.6.
3.6 The configuration files are most likely technically accurate. The absolute fix for this problem would be to move the TNSNAMES.ORA file and the SQLNET.ORA file to the ORANT\bin directory. Once this is done connections should work. If the connection works but more explanation is needed for why placing the configuration files in the ORANT\bin directory worked, please skip to section 3.7.
3.7 When the configuration files were placed in the ORANT\bin directory, the files were placed in the 'working directory' of SQL*Net. Because of this, SQL*Net does not have to search any other directory or mapped drive for the configuration files. Possible reasons for getting the TNS-12154 when the configuration files are not in the working directory include:
1. TNS_ADMIN is set in the environment.
Open the file ORACLE_HOME\bin\oracle.key. Look in the Windows NT Registry under the location specified by the .key file, such as HKEY_LOCAL_MACHINE\Software\Oracle\Home1
for a parameter called TNS_ADMIN. TNS_ADMIN is a parameter that can be set to
have SQL*Net point to an alternate location for the configuration files.
for a parameter called TNS_ADMIN. TNS_ADMIN is a parameter that can be set to
have SQL*Net point to an alternate location for the configuration files.
2. Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
Make sure none exist.
3. Client computer is mapped to remote drives.
Make sure configuration files do not exist in any of these remote locations.
4.1 Database Links - Troubleshooting the ORA-12154
One of the most common problems with this error and database links is a misunderstanding of where to put the configuration files.
When a client issues a database link connection:
SQL> SELECT * FROM emp@TEST_DBLINK;
TEST_DBLINK's TNS address is not resolved on the client, it is resolved on the server that client's SQL*Plus session is connected to.
If a connection is made from a client to Server1 and then this database link is created:
SQL> CREATE DATABASE LINK TEST_DBLINK
CONNECT TO scott IDENTIFIED BY tiger USING 'DEV1';
CONNECT TO scott IDENTIFIED BY tiger USING 'DEV1';
Server 1 needs a TNSNAMES.ORA file that contains the alias 'DEV1' which will point to Server 2. See table 4-1 for more information.
Table 4-1 Components Needed to Create a Database Link
Client ----->
|
Connected to ->
|
Server1 ---->
|
DB Link --->
|
Server2
|
TNSNAMES.ORA file that contains an alias pointing to Server1
|
TNSNAMES.ORA file containing an entry for DEV1
|
Server with DEV1 instance
|
If there is a TNSNAMES.ORA file on the server, the make sure the file is in the ORACLE_HOME\network\admindirectory.
If there is not a TNSNAMES.ORA file, copy one over to the server and modify it.
If there is a TNSNAMES.ORA file in the ORACLE_HOME\network\admin, and the TNS-12154 still occurs, start a SQL*Plus session on the server where the database link is being initiated and make a connection to the remote database. For example:
% sqlplus scott/tiger@dev1
If a successful connection to the remote database is made from SQL*Plus but the database link is failing, skip to section 4.2. If the connection was not successful, use the table of contents of this bulletin to go to the chapter describing what problem is occuring (i.e. TNS-12154 from a Unix Client).
4.2 If SQL*Plus connected but the database link failed, connect to the origin instance with system privileges and check:
SQL> SELECT db_link, host FROM dba_db_links;
DB_LINK HOST
-------------------------------
TEST_DBLINK dev1.world
-------------------------------
TEST_DBLINK dev1.world
The column DB_LINK shows the name of the database link, and the column HOST shows the name of the TNSNAMES alias being used by the database link.
Rename the SQLNET.ORA file (if there is one) and make sure the value of the HOST column exactly matches the alias being used in the TNSNAMES.ORA file on the server.
Also try recreating the database link again using the complete description instead of the alias:
CREATE DATABASE LINK linkname
CONNECT TO user IDENTIFIED BY password
USING '(description=(address=(protocol=TCP)
(host=xxx.xxx.x.xx)(port=1521))(connect_data=(service_name=target_service)))';
CONNECT TO user IDENTIFIED BY password
USING '(description=(address=(protocol=TCP)
(host=xxx.xxx.x.xx)(port=1521))(connect_data=(service_name=target_service)))';
A connection should be made with the database link now, or a progression to the next logical error should be shown.
5.1 Oracle Names - Troubleshooting the ORA-12154 (Not supported in version 10g and up)
For this chapter it is best to have a copy of the SQLNET.ORA file present. This file is normally located in the ORACLE_HOME network\admin directory.
5.2 In the SQLNET.ORA file find the parameter NAMES.DIRECTORY_PATH. This parameter tells SQL*Net in which order to search for the alias being connected with.
If not already there, add the parameter:
NAMES.DIRECTORY_PATH=(ONAMES)
to the SQLNET.ORA file. If the parameter is already there or resembles something like NAMES.DIRECTORY_PATH=(ONAMES, TNSNAMES), do not change do anything.
5.3 Check for the parameter NAMES.PREFERRED_SERVER in the SQLNET.ORA file. The structure of this parameter looks like this:
NAMES.PREFERRED_SERVERS =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = nameserver1)
(PORT = 1569)
)
)
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = nameserver1)
(PORT = 1569)
)
)
If this parameter does not exist, add it. The parameter HOST is referring to the server the nameserver is running on, and PORT refers to the listening port of the nameserver.
Make sure contact can be made to the nameserver at the TCP level.
To do this, open a DOS window on the client (or an OS prompt on Unix) and type:
ping nameserver_host_name
where nameserver_host_name is the hostname of the Oracle Names Server. If the ping is successful, continue to section 5.4.
If the ping is not successful, or get a message such as "Operation Timed Out", please see a network administrator and determine why the ping to the nameserver was not successful.
5.4 Go to or log on to the nameserver and start the names control utility by typing 'namesctl' at the operating system prompt.
At the NAMESCTL prompt, type 'start'. A response should be given that either says "Name Server already running" or a screen full of status information will be displayed indicating the nameserver is now running. If any fatal errors occur that cause the nameserver
not to start, please refer to the Oracle Names manual on properly setting up a nameserver.
not to start, please refer to the Oracle Names manual on properly setting up a nameserver.
With the Names Server is started and running, type the following command at the NAMESCTL prompt:
NAMESCTL> QUERY alias a.smd
where alias is the name of the alias being used to connect with from the client application. For example:
NAMESCTL>QUERY dev1.world a.smd
All of the TNS aliases are stored in memory in the Name Server's cache. The 'query' command querys the cache to see if the alias exists. Similar output should be displayed::
Total response time: 0 seconds
Response status: normal, successful completion
Authoritative answer: yes
Number of answers: 1
TTL: 1 day
Answers: data type is "a.smd"
Syntax is ADDR:
...(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=otcsco1)PORT=1521)))(CONNECT_DATA=(SID=V732)))
NAMESCTL>
Response status: normal, successful completion
Authoritative answer: yes
Number of answers: 1
TTL: 1 day
Answers: data type is "a.smd"
Syntax is ADDR:
...(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=otcsco1)PORT=1521)))(CONNECT_DATA=(SID=V732)))
NAMESCTL>
If there is no information similar to the above output, then the nameserver is not retrieving the aliases from the Oracle database, checkpoint files, or in DDO's case, the listener's are not registering with the nameserver. Refer to the Oracle Names User's guide to correct this.
LDAP |
6.1 LDAP - Troubleshooting the ORA-12154
For this chapter it is best to have a copy of the LDAP.ORA file present. This file is normally located in the ORACLE_HOME network\admin directory.
There are not very many causes of the TNS-12154 when using LDAP and the solutions are straight forward. The failures are either going to be TCP related or OracleContext related
TCP Related Failures
Make sure the host and port values of the DIRECTORY_SERVERS parameter are correctly defined in the LDAP.ORA file.
Verify the OS command ping ldap_server_name or telnet ldap_server_name work.
If OID is being used, verify oidmon and oidldapd are running on the OID server.
If a firewall is in place, verify it allows traffic to communicate through the LDAP server’s port number.
OracleContext Related Failures
The distinguished name’s attributes and values may be incorrect. For example, if your net service names are stored in an OracleContext under the us.acme.com domain, then a fully qualified name being searched for may be:
cn=netservice_name,cn=OracleContext,dc=us,dc=acme,dc=com
It is this string where correctness must be verified. Searching a level 16 client trace file for the phrase “distinguished name” will point out what the fully qualified distinguished name being searched for is.
The distinguished name may not exist in the directory.
The DEFAULT_ADMIN_CONTEXT parameter in the LDAP.ORA file may be incorrect.
The host and port values of the DIRECTORY_SERVERS parameter are pointing to an alternate LDAP server.
7.1 Operating System Search Path for Configuration Files
The Oracle Networking product's search for configuration files is not standard across platforms and may change from version to version. A small, accurate, list has been provided to show the differences between platforms. Afterwards, methodology will be explained so any user can determine their operating system's search path for configuration files.
Windows NT/2000 running Oracle 9i
First: The directory where the application is launched. For example, if sqlplus resides in
ORACLE_HOME\bin\sqlplus but was launched from the c:\temp directory, then
c:\temp is searched for a tnsnames.ora file.
Second: The value of the TNS_ADMIN environment variable.
Third: ORACLE_HOME\network\admin
First: The directory where the application is launched. For example, if sqlplus resides in
ORACLE_HOME\bin\sqlplus but was launched from the c:\temp directory, then
c:\temp is searched for a tnsnames.ora file.
Second: The value of the TNS_ADMIN environment variable.
Third: ORACLE_HOME\network\admin
UNIX running Oracle 8i or 9i
First: The oracle user's home directory is searched for a hidden '.tnsnames.ora'
Second: The value of the TNS_ADMIN environment variable.
Third: /var/opt/oracle
Fourth: $ORACLE_HOME/network/admin
First: The oracle user's home directory is searched for a hidden '.tnsnames.ora'
Second: The value of the TNS_ADMIN environment variable.
Third: /var/opt/oracle
Fourth: $ORACLE_HOME/network/admin
Note: The PATH environment variable may introduce differences into the behavior of the search.
Determining an operating system's search path for the Oracle configuration files is very easy. On Windows NT/2000, a freeware utility called 'filemon' may be used which displayed all files accessed during the execution of an application (like sqlplus). On UNIX, a truss-like utility should be used that can generate output of files an application tried to access. In both cases, remove the tnsnames.ora file, run the appropriate OS monitor utility, and search for all occurrences of the word 'tnsnames'.
7.2 Troubleshooting a TNS-12154 when no Trace File is Produced
When a client trace file is not produced after the parameter TRACE_LEVEL_CLIENT=16 has been set in the SQLNET.ORA file, this indicates that an alternate or SQLNET.ORA file is being read from the file system. To correct this matter, one of the following should be tried:
When a client trace file is not produced after the parameter TRACE_LEVEL_CLIENT=16 has been set in the SQLNET.ORA file, this indicates that an alternate or SQLNET.ORA file is being read from the file system. To correct this matter, one of the following should be tried:
1. Move the SQLNET.ORA file that was edited to the ORACLE_HOME/bin directory. The bin directory is the working directory of SQL*Plus, and it is this directory that will be searched first for any Oracle configuration files. If the application being used is a 3rd party tool, place the SQLNET.ORA file in the directory where its executable resides. This option should guarantee a trace file is produced.
2. Search the entire file system for any duplicate sqlnet.ora files.
3. Try setting the environment variable TNS_ADMIN to explicitly point to the location of the configuration files.
4. Ensure that the network configuration files do not contain any hidden special characters.To verify , open the configuration files in NetCA or the Net Manager GUI tool
2. Search the entire file system for any duplicate sqlnet.ora files.
3. Try setting the environment variable TNS_ADMIN to explicitly point to the location of the configuration files.
4. Ensure that the network configuration files do not contain any hidden special characters.To verify , open the configuration files in NetCA or the Net Manager GUI tool
Unix**: export TNS_ADMIN=directory_of_Oracle_configuration_files
NT**: Add TNS_ADMIN to the registry in the location: HKEY_LOCAL_MACHINE\Software\Oracle\Home, where 'x' represents the number indicating the Oracle_Home. Set its value to the full path of the configuration files.
NT**: Add TNS_ADMIN to the registry in the location: HKEY_LOCAL_MACHINE\Software\Oracle\Home, where 'x' represents the number indicating the Oracle_Home. Set its value to the full path of the configuration files.
** Only a Unix or NT Administrator should make these changes
No comments:
Post a Comment