In this document I will discuss step by step solutions for ORA-12154, TNS-12154 and TNS-03505. About ORA-12154 it is already discussed in
Troubleshoot ORA-12154. This post will be applicable to troubleshoot for both ORA-12154, TNS-12154 and TNS-03505.
What Oracle Says about the ProblemIf you open
http://arjudba.blogspot.com/2008/06/how-to-get-oracle-error-message-from.html you will see about the description of TNS-12154 or ORA-12154 they are telling same like below.
Oracle Error : TNS-12154
TNS: could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Action:
- If you are using local naming (TNSNAMES.ORA file):
- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
- If you are using directory naming:
- Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Verify that the LDAP directory server is up and that it is accessible.
- Verify that the net service name or database name used as the connect identifier is configured in the directory.
- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier
- If you are using easy connect naming:
- Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Make sure the host, port and service name specified are correct.
- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
For case TNS-03505: it says
TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.
which indicates for both ORA-12154, TNS-12154 and TNS-03505 we will have a generic solution.
Step 01: Overview of the ProblemBoth ORA-12154, TNS-12154 and TNS-03505 indicates that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
As the problem is regarding TNSNAMES.ORA and SQLNET.ORA so in order to dig into solution it is important to print out or a view of both the TNSNAMES.ORA and the SQLNET.ORA files. Looking at these files at the same time is helpful since references will be made to both.
Step 02: Identify the location of TNSNAMES.ORA and SQLNET.ORAIt is necessary to locate the location of TNSNAMES.ORA and SQLNET.ORA. If you simply use tnsping dummy it will say the location of SQLNET.ORA and there TNSNAMES.ORA should be in the same location.
E:\Documents and Settings\Arju>tnsping dummy
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-JAN-2010 11:00:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Arju\product\11.1.0\db_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
From the above output we see both of these files are located in the path D:\app\Arju\product\11.1.0\db_1\network\admin\.
Through above ways we can determine current path of these two files. However it is important to know the default path of these files. Based on the operating system default path are mentioned below.
i)Windows 3.x clientORAWIN\NETWORK\ADMIN directory
ii)Windows 95/98 clientSQL*Net 2.x - ORAWIN95\NETWORK\ADMIN
Net8 - ORAWIN95\NET80\admin
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iii)Windows NT clientSQL*Net 2.x - ORANT\NETWORK\ADMIN
Net8 - ORANT\NET80\ADMIN
Net8i - ORACLE\ORA81\NETWORK\ADMIN
iv)UNIX Client$ORACLE_HOME/NETWORK/ADMIN
or /etc
or /var/opt/oracle
Step 03: Print out or View both TNSNAMES.ORA and SQLNET.ORA.Following is the correct TNSNAMES.ORA from my system
# tnsnames.ora Network Configuration File: D:\app\Arju\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
A =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = a)
)
)
Following is the correct SQLNET.ORA from my system.
# sqlnet.ora Network Configuration File: D:\app\Arju\product\11.1.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.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Step 04: Check several factors one by one.To Troubleshoot problem let's start with some complex TNSNAMES.ORA and SQLNET.ORA.
TNSNAMES.ORA Experimental Sample
DEV.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 10.1.1.1)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
SQLNET.ORA Experimental Sample
TRACE_LEVEL_CLIENT = OFF
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)
AUTOMATIC_IPC = OFF
i) Determine which tns entry you used to connect. For example if you used dummy then issue a tnsping dummy and ensure that the entry DUMMY must exist in the TNSNAMES.ORA.
For example, my "tnsping a" worked because in my TNSNAMES.ORA I have an entry regarding alias A which I have pasted my TNSNAMES.ORA above.
E:\Documents and Settings\Arju>tnsping a
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-JAN-2010 11:00:34
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Arju\product\11.1.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ARJU)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = a)))
OK (330 msec)
tnsping dummy will fail because there is no entry in my TNSNAMES.ORA with name dummy and also connection with dummy will fail too.
Make sure by using IP Address entry instead of hostname inside TNSNAMES.ORA. So use HOST=192.168.1.1 something like that instead of HOST=ARJU.ii) Check both file again and ensure that there is no CTRL-A (^A) or CTRL-C (^C) characters inserted at the ends of any lines.
iii) If both are fine check the TNSNAMES.ORA alias. Here in our experimental sample it is DEV.WORLD which means .WORLD is our domain and it should be specified it our SQLNET.ORA. So add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file. Save the file, and try the connection again.
iv) If within the TNSNAMES.ORA there is no default domain in the alias*(for example it is simply DEV but not DEV.WORLD) then remove NAMES.DEFAULT_DOMAIN = world from the SQLNET.ORA file if there existed. After removing the parameter, save the SQLNET.ORA file, and try connecting again.
v) If the parameter NAMES.DIRECTORY_PATH exists in the SQLNET.ORA file, make sure the value in parenthesis lists TNSNAMES. For example following two examples are valid entry.
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
Though NAMES.DIRECTORY_PATH parameter is not needed, but if it exists then it should look ok. After removing it you can try connecting again.
vi) At this phase the configuration files are most likely technically accurate.
Now you have to ensure that your TNSNAMES.ORA is in the correct location and you have used correct TNS entry while connecting. The TNS location can be set by TNS_ASMIN environmental variable.
On your Unix environment,
% echo $TNS_ADMINIf nothing is returned, try setting the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.
In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_fileIn K Shell, the syntax is:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMINAfter setting try the connection/tnsping again.
In case of Windows 3.x,- TNS_ADMIN is set in the ORACLE.INI.
- Look in the C:\WINDOWS directory for the ORACLE.INI file. Look for the parameter TNS_ADMIN. TNS_ADMIN is a parameter that can be set to have SQL*Net point to an alternate location for the configuration files.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows 95/98, - Open the file ORACLE_HOME\bin\oracle.key.
- Search for 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.
- Search for duplicate TNSNAMES.ORA and SQLNET.ORA files.
- Make sure none exist.
- Once you are sure try the connection/tnsping again.
In case of Windows XP,- Make sure TNS_ADMIN environemental points to correct location. Ensure that there must not exist both user variable or system variable environmental variable. If nothing exist you can add one user variable. To set user/system variable,
-- Right click on My computer
-- Click Properties.
-- Click Advanced Tab.
-- Click Environemental Variables.
-- You can have both User and System Variables there.
You can add, edit or delete variables. You can simply add TNS_ADMIN and its location in the user variable section. Note that Variable Name would be "TNS_ADMIN" and variable would be "The location of the TNSNAMES.ORA in your client environement."
- After you set try connection again.
Your problem would be resolved.
Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132
