The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators. When this occurs, there’s an issue with creating a connection with one of your Oracle services or database instances. In some Oracle database versions, this error may be called “ORA-12154: TNS:could not resolve service name.” The connect identifier is not able to resolve and may be caused by one or more of the following issues:
- Inability to connect to the repository due to unplanned server and network outages
- The entry is missing from tnsnames.ora
- The entry in tnsnames.ora is malformed
- The program is using tnsnames.ora from the wrong ORACLE_HOME
- The program is not using a fully qualified service name, but no default domain is enabled in sqlnet.ora
Because there is more than one cause of the ORA-12154 error, you need to troubleshoot precisely what’s going on with your database connections. You’ll typically see this error in the Oracle client application during the connection process, not the server itself. While it can be frustrating to see this error when you’re working on an application, the fix is relatively straightforward.
Resolving ORA-12154 Error Codes
The Oracle client code uses one of three ways to look up connect data:
- A flat file named tnsnames.ora
- Oracle Names service
- LDAP
When the complete ORA-12154 error appears with the text line, your program has found a working Oracle client install. However, the specified Oracle service is not listed in tnsnames.ora, Oracle Names or LDAP.
The first step in the troubleshooting process is to determine which name resolution method is deployed at your site. Most sites use tnsnames.ora, but enough use Oracle Names and LDAP, so it’s best to confirm this information.
If you are not the database administrator, get in touch with the people managing your Oracle systems and find out which method you should be using. They may be able to guide you in fixing the problem in accordance with your site’s standards.
The client code decides which mechanism to use based on the file sqlnet.ora. This file and tnsnames can usually both be found in the Oracle install directory (“ORACLE_HOME”), under network/admin/. This location may be overridden with the environment variable TNS_ADMIN.
If the sqlnet.ora file does not exist or does not specify a resolution method, then Oracle Net uses tnsnames.ora.
Example locations of Oracle networking files include:
Windows
- ORANTNET80ADMIN
- ORACLEORA81NETWORKADMIN
- ORAWIN95NETWORKADMIN
- ORAWINNETWORKADMIN
UNIX / Linux
- $ORACLE_HOME/network/admin/
- /etc/
- /var/opt/oracle/
If you fix the naming issues, but you still see the ORA-12154 error, check the Oracle service to confirm that it’s available for connections. A power outage, server failure, or network connectivity issue will make this resource inaccessible. It’s also possible that scheduled maintenance or repairs of an unrelated Oracle issue may take that resource temporarily offline.
Get Expert Help with Resolving Your ORA-12154 Errors
Datavail’s Oracle experts have an average of 15 years of experience and are well-versed in resolving common connection problems with this database technology. We offer Oracle services tailored to your needs, whether you need occasional assistance with troubleshooting or end-to-end solutions for your business.
Don’t let Oracle errors get in the way of creating high-availability, stable applications that your organization depends on. Get the most out of your technology investments by contacting us today.
First published on MSDN on Jun 30, 2010
This is one of the most common errors while creating linked server to Oracle database. Today I will discuss the reason for this error and possible resolutions.
Full error message:
OLE DB provider «MSDAORA» for linked server «LINKED_ORA» returned message «ORA-12154: TNS:could not resolve the connect identifier specified».
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «MSDAORA» for linked server «LINKED_ORA».
First of all make sure you have reviewed the following Microsoft KB article that has a lot of good information on troubleshooting Oracle linked server issues.
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
http://support.microsoft.com/kb/280106
Also make sure you have installed Oracle Client on the SQL server. If the SQL server is 64 bit then we need to install 64 bit Oracle provider. You can also create linked server using Oracle ODBC driver together with Microsoft OLE DB provider for ODBC. Once again on a 64 bit SQL server you need to install the
64-Bit OLEDB Provider for ODBC (MSDASQL)
and 64 bit Oracle ODBC drivers. However 64-Bit OLEDB Provider for ODBC (MSDASQL) is already there in Windows Vista/Windows Server 2008 and later OS.
This particular error message is a very general error message and can happen for quite a number of reasons. For general understanding of the error, you can review oracle documentation like this
http://ora-12154.ora-code.com/
In SQL Server Linked Server, it could indicate a few things (not limited to)–
1. SQL Server (and oracle net libraries) is not able to get the TNS alias from tnsnames.ora file.
2. Something is wrong with the way the alias is created in the tnsnames.ora file (incorrect syntax)
3. TNS alias could not be resolved into a connect descriptor
Below is a list of things that you can try to resolve this issue.
1. Verify that the tnsnames.ora file has the alias and the service name that the customer is using.
TNS entry for the Oracle database
===========================
OracleDB_Dev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleDB)
(SERVER = DEDICATED)
)
)
In the above tnsnames.ora file Alias = OracleDB_Dev
Service Name: OracleDB (Actual Oracle service name [instance name in SQL])
2. Check the sqlnet.ora file under ‘Admin’ folder in Oracle home [Dir:appproduct11.1.0client_1networkadmin] and ensure that we have TNSNames in NAMES.DIRECTORY_PATH
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
3. Verify if you can connect to Oracle from the SQL server machine using tools installed with Oracle Client [For example «SQL Developer» or “SQL Plus”] with the same user id/password or TNS alias.
5. Check if the environment variable ‘PATH’ has the path for tnsnames.ora file specified.
Sample Value of Environment Variable PATH:
E:appproduct11.1.0client_1bin
;C:Program FilesBusiness ObjectsCommon3.5binNOTES;C:Program FilesBusiness ObjectsCommon3.5binNOTESDATA;%Systemroot%Microsoft.NETFrameworkv1.1.4322;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesDellSysMgtomabin;C:Program FilesMicrosoft SQL Server80ToolsBINN;C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATEADMIN1033;C:Program FilesMicrosoft SQL Server80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program FilesMicrosoft SQL Server90Toolsbinn;C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;C:Program FilesMicrosoft Network Monitor 3
Note: make sure that the path is a valid path and there is no space.
6. Check the value of the key ”Oracle_Home” in the registry under HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraClient11g_home1 and verify that it has the right path for the Oracle home.
7. Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINESOFTWAREORACLE. If it exists then make sure it has the right value as “Dir:appproduct11.1.0client_1networkadmin”. If you don’t see the key then create the key and set appropriate value as below.
Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name
it TNS_ADMIN and give the value “X:appproduct11.1.0client_1networkadmin”
Note: This is not a must but in some cases this is what fixed the issue.
8. Check if SQL server start up account has permission to the Oracle Home. Also collect Process monitor log and check for “access denied”. Process monitor log should show if we are able to find the tnsnames.ora file.
9. Make sure you don’t have multiple Oracle homes or multiple Oracle clients installed. Check the «HKEY_LOCAL_MACHINESOFTWAREORACLEALL_HOMESHOME_COUNTER» key value.
10. Check if Oracle OLE DB provider is running InProcess. If ‘yes’ then try to run out-of- process and see if that resolves the issue.
Note: You can check and verify if MS OLE DB Provider for Oracle is running InProcess from the registry key at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerProvidersMSDAORA
11. You can try collecting simultaneous Network trace from both SQL and Oracle servers and check if there are any communications between the two servers.
12. Try to connect to Oracle from the SQL server using the UDL. Use the same TNS name. If you get the same error that means the issue is not specific to SSMS or linked server.
Creating and Configuring Universal Data Link (.udl) Files
http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx
13. Try to specify all the information in the data source instead of using the TNS alias to connect to the Oracle database (this is a way to bypass tnsnames.ora file when connecting to Oracle).
Sample Data Source:
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));
Author : Mohammad(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Azim(MSFT), SQL Developer Technical Lead , Microsoft
Have you gotten an “ORA-12154: TNS:could not resolve the connect identifier specified” error? Learn what causes it and how to resolve it in this article.
ORA-12154 Cause
If you attempt to access or log on to an Oracle database, you might get this error:
ORA-12154: TNS:could not resolve the connect identifier specified
This means that the tnsnames.ora file was not found or has an error within it.
There are a few steps you can take to resolve this ORA-12154 error.
Check that the tnsnames.ora file exists
There is a tnsnames.ora on both the client and server systems. It’s located in the ORACLE_HOME/network/admin directory. I’ve written a guide to the TNSNAMES file here which has more information.
ORACLE_HOME is where your Oracle database is installed on the server, or on your own computer if you’re using Oracle Express.
For example, in my installed version of Oracle Express, my ORACLE_HOME is:
C:oraclexeapporacleproduct11.2.0server
If I open the network then admin folders, I will see a tnsnames.ora file.
If it exists in this folder, then you need to check that it has no errors (see the next step).
If it doesn’t exist, then you can create one.
To do this:
- Create a new file in this folder and call it tnsnames.ora.
- Open the file in a text editor and add the information in this format:
The syntax of the tnsnames.ora file is:
<addressname> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) ) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) )
The example in my Oracle Express instance is:
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
So, just copy and paste this into your new tnsnames.ora file, make changes as necessary, and save it.
Try your connection again (the one where you got the error) and see if it works.
Check that TNSNAMES.ORA has no syntax errors
If the file exists, open it and see that there are no syntax errors.
Using the example above:
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
Check that the brackets are all in the right place, there are no quotes in there, no missing lines or anything unexpected.
Check that TNSNAMES.ORA has your service name in it
To be able to connect to your database, the tnsnames.ora file needs to have your service name in it.
Open the tnsnames.ora file and add it in there if it does not exist, using the examples above.
Check that TNSNAMES.ORA has read permission
Sometimes, the file can exist and be syntactically correct, but doesn’t have any permissions.
If other users or processes cannot read the file, you’ll get the ORA-12154 error.
So, check that the file can be read by other users by applying read permissions to it.
Run the TNSPING Utility
Oracle includes a tnsping utility for checking that the TNSNAMES is OK.
You can find this by going to ORACLE_HOME/bin/tnsping.exe
For example:
C:oraclexeapporacleproduct11.2.0serverbin
If you’re on Windows, you can open the Command Prompt and CD to this directory.
Then, run tnsping xe (or your service name you want to check)
This should show if it is OK or not.
TNSADMIN Environment Variable is Missing
If you’re connecting on Windows, this error can sometimes happen if the TNSADMIN environment variable is missing.
To check this:
- Go to Start > Control Panel
- Open System
- Click “Advanced system settings”
- Click Environment Variables
- Add a new system variable called TNSADMIN with a value of ORACLE HOMEnetworkadmin
This is often not needed, but if you’ve tried everything else, and are still getting the ORA-12154 error, you can try adding the TNSADMIN environment variable.
So, there are a few solutions to the “ORA-12154: TNS:could not resolve the connect identifier specified” error.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
While working with different functionalities ; the oracle developers are facing different types of errors.The most common error coming while connecting database is ORA-12154: TNS:could not resolve the connect identifier specified .In this article i will explain how ORA-12154 error will solve and why this error will come.This is the most common error and most searched error by developers and DBAs.This error will come while trying to establish connection first time.So this is most common error in oracle.
“ORA-12154 error has been searched on google approximately 1 lakh times per month”
The error is displayed in the above images.
CLICK HERE FOR CHECKING DIFFERENCE BETWEEN SQL AND NOSQL
Why ORA-12154 error will come?
ORA-12154 error is very common error related to ‘tnsnames.ora’ file.This error will come when oracle is unable to locate the service name specified in ODBC data source.ORA-12154 error will come at local service when application tries to connect with remote server.This error will come in following situations :
Situation 1 :
When Application tries to connect with database.
Situation 2 :
When one database will try to connect with other database via database link.
Situation 3 :
Just after installing the oracle database and tries to connect system user.
In above situations this error will occur.There should be multiple reasons for this error.The following should be the reasons of this error. So If this error will come then DBA or developer needs to do first check i.e. tnsnames.ora.
Following needs to check to resolve this error :
1.The Service name entry is missing from tnsnames.ora
Make sure that the service name we are using is the same for which the listener is listening for.
2.The Entry is wrong in tnsnames.ora file
3.tnsnames.ora is in wrong ORACLE_HOME location
4.Your Service name might have an alias.Check that in global entries as well as local entries.
Location for tnsnames.ora :
$ORACLE_HOME/network/admin/tnsnames.ora
5.Check for global_name settings with SQL.
Select * from global_name;
This query value should match the init.ora parameters db_name and db_value. If the value is not matching you can change that value using alter command of database.
Alter Database Rename global_name to ‘New Name’;
Resolution of Error :
ORA-12154: TNS:could not resolve the connect identifier specified
For Local naming : (tnsnames.ora file issue)
- Kindly check the tnsnames.ora file is present in specified location or not.
- Check that tnsnames.ora is accessible to database
- The Service name which are using should present as connect identifier in tnsnames.ora
- Make sure that tnsnames is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile.
- Check for the syntax errors in tnsnames.ora file.If there is error in this file then this file is unusable.
NO TIME TO READ CLICK HERE TO GET THIS ARTICLE
TNSNAMES.ORA file sample :
Database_name =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Database_name)
)
)
If you are using instant client method rather than tnsnames.ora file then database attribute name needs an connect URL rather than service name.
Syntax :
//host:port/service_name
Where host is the host_name or ip address and port will be the oracle port and service name is the service name specified.
Example:
10.98.186.22:ODSDB/PDWHS
CLICK HERE TO GET THE SAMPLE TNSNAMES.ORA FILE
For Directory Naming :
1.LDAP must be there in names.directory_path parameter of sqlnet.ora file.
2.Verify LDAP directory server is up and accessible.
3.Verify that service name and database name is configured in directory or not.
I have specified some ways to resolve the error ‘ORA-12154: TNS:could not resolve the connect identifier specified’ which is frequent error on Oracle. Hope you like this article.If you like this article don’t forget to share it.
Problem
DataStage job fails with error ORA-12154 when accessing an Oracle database via the Oracle run-time client. The following are typical errors, but multiple variations exist:
Error while trying to retrieve text for error ORA-12154
APT_OraReadOperator: connect failed.
ORA-12154: TNS:could not resolve the connect identifier.
ORA-12154: TNS:could not resolve service name
Resolving The Problem
Error while trying to retrieve text for error ORA-12154
The above error means that not only did the connection to Oracle Database fail, but that Oracle was unable to retrieve the text of error message ORA-12154. This usually indicates that either the DataStage userid running the job does not have read access to the Oracle run-time client files, or that the environment variable ORACLE_HOME is not defined.
ORA-12154: TNS:could not resolve the connect identifier.
ORA-12154: TNS:could not resolve service name
The above 2 errors indicate that the connection identifier or service name specified in the DataStage job (or in the ORACLE_SID environment variable or ODBC definition) was not known to either the Oracle client or the Oracle server. The connection/service identifiers known to the Oracle run-time client are defined in the tnsnames.ora file, $ORACLE_HOME/network/admin/tnsnames.ora
Verify that the identifier specified for the failing Oracle connection has been defined in tnsnames.ora. If it is correctly defined, then next verify that the ORACLE_HOME environment variable is correctly defined, and that tnsnames.ora file has correct read permissions.
If the above items are configured correctly, also check the listener.log on Oracle server to confirm that the service id (or the database it maps to) are known to the Oracle server.
Setting up environment variables required to use Oracle run-time client
The Oracle client requires that the following environment variables be defined. These should be set in the .dsenv file in the DataStage DSEngine directory.
ORACLE_HOME=/home/oracle
LIBPATH=$LIBPATH:$ORACLE_HOME/lib:
PATH=$PATH:$ORACLE_HOME/bin
Change the path defined for ORACLE_HOME to the correct path for your system. ORACLE_HOME should be set to the absolute path to the home Oracle directory which is the directory level directly above the lib and bin directories.
Please also note that the name of the library path environment variable varies with different operating systems:
- AIX — use LIBPATH
- Solaris — use LD_LIBRARY_PATH
- HP-UX — use SHLIB_PATH
- Linux — use LD_LIBRARY_PATH
[{«Product»:{«code»:»SSVSEF»,»label»:»IBM InfoSphere DataStage»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»—«,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF016″,»label»:»Linux»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.1;8.0;7.5″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]