Error while trying to retrieve text for error ora 12154

[{«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»}}]

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

Hi HERBET,

  if you are using a windows system please go to the directory where oralce is installed.Under which go to networkadmin. There you will find two files called tnsname.ora and sqlnet.ora.If you want to do a client configuration you have to edit this file(if you cannot use the gui tool,gui tools internaly edit these files).

   eg. of path where you can find C:OracleOra81networkADMIN

  you can find entries like this in tnsnames.ora file.

 
   connectstring=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = hostname or IP Address of the target computer)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA = (SID = sidname of your database)
    )
  )

the connect string can be any string which you use while connecting to the database .

   eg.    connect scott/tiger@connectsting

       
   the main things you need to specify here is connect string,protocol which you are using(default it will be TCP),Host computer name ie. server name or the IP of the server.Default PORT is enough.
  The next thing you need to define is SID.Here you can specify your database name.

Once you specify all these details you can save the file.The next step you can open sqlnet.ora file

Check an entries like these in sqlnet.ora file.    names.directory_path = (TNSNAMES)

eg.

   testdb=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = kripa)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA = (SID = test)
    )
  )

in this case my connect string is testdb,i use this connect string while connecting to the database.my host computer name is kripa where oracle server is running.and my database name is test.’

you can connect to the database  like this.

 sql>connect scott/tiger@testdb

you can test the connection once you configure this tnsnames.ora using tnsping utility.

 eg.   tnsping testdb

once its responding you can connect to database.

Hope this will solve your problem.

Cheers
Kripa

While using Oracle database, you may come across several errors due to some uncertain reasons and one of the error is the ORA-12154 error. If you are the one who has got this error then you don’t have to worry at all. I am saying so because here I am going to show you how to fix ora-12154 error with ease. Yes, you are so lucky that you have visited this webpage because after going through the solutions, you will be definitely able to resolve ora-12154 error in no time.

But, before we proceed to the solutions let us know something more about this ORA-12154 error that takes place while using Oracle database.

If you receive ora-12154 error then it means that the Oracle database client has been failed while he/she tries to connect with the listener on the desired server.

Or, in Oracle language, this error is defined as ‘a connection to the service or the database was requested using a connect identifier, as well as the connect identifier that is specified could not be resolved into a connect descriptor with the use of one of the naming methods configured.’ As for example, if the type of connect identifier used was actually a net service name then the net service name is not found in a naming method repository or the repository is actually be reached or located.

There are several reasons that are responsible for causing this oracle error and due to the unknown reasons, it has become quite tricky to fix ora-12154 error. However, here I have tried to solve this error based on some reasons that may cause this error to occur.

What Causes ORA-12154 Error To Occur?

As I have already mentioned that there are several causes due to which ora-12154 error takes place. Here are some of the major known causes:

• Missing tnsnames.ora File
• Searching for Wrong Domain
• Absent Local Naming Method
• Missing Parenthesis
• Connect Identifier Mismatch

How To Fix ORA-12154: TNS:could not resolve the connect identifier specified?

Here, you will get to know the best ways to fix ORA-12154: TNS:could not resolve the connect identifier specified error based on the error message you get. All these solutions are very easy and effective that will definitely fix ora-12154 error.

Error Message #1: ORA-12154 Due to Missing tnsnames.ora File

When you deliberately deleted tnsnames.ora file for testing the effect, you get the message:

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

File Not Found

After getting this message, when you try to connect to the database, you get the below message:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:12:14 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

You get these message because of the sqlplus found no file to lookup the connect identifier.

Solution To Fix This Error:

In order to fix this error, you can create a new tnsnames.ora or you can also restore the original one.

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

tnsnames.ora
1 File(s)            388 bytes
0 Dir(s)  179,702,697,984 bytes free

After doing so, you can again try to connect to the Oracle database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:20:41 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

Error Message #2: ORA-12154 Due to Absent Local Naming Method

When you set TNS_ADMIN environment variable explicitly, you try this:

C:UsersEd>set TNS_ADMIN=C:appclientAdministratorproduct12.2.0client_1networkadmin

Check the content of sqlnet.ora. There’s no TNSNAMES naming method.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT)

After this, you try to connect to the database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:02:33 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Here, you can get ora-12154 error in sqlplus because there is no TNSNAMES naming method available to support the connection.

#1: Solution To Fix This Error: Add TNSNAMES Method

You can fix ora-12154 error here by adding TNSNAMES method back to NAMES.DIRECTORY_PATH.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Then we tried to connect to the database again.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:05:24 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

Using this way, you will be able to fix this ora-12154 error with ease.

#2: Solution To Fix This Error: Easy Connect Naming Method

You can also try connect naming method to fix ora-12154 error and connect to the database. For this, you have to make sure that you have EZCONNECT in the list of NAMES.DIRECTORY_PATH.

If you don’t have then you will get ora-12154 like this and it will solve your problem soon:

SQL> conn hr/hr@ora11g.example.com:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Error Message #3: ORA-12154 Due to Connect Identifier Mismatch

If you changed the connect identifier while connecting the database using the method below then you can sometime get this error:

SQL> conn hr/hr@ORCL123
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution To Fix This Error

If you get this error due to connect identifier mismatch then you can try the correct connect identifier so that you can connect to the database easily. To do so, you have to try the below query:

SQL> conn hr/hr@ORCL
Connected.

It is always advisable that you should always use a matched identifier which also exists in tnsnames.ora.

Error Message #4: ORA-12154 Due to Missing Parenthesis

Sometimes it happens that you may not notice that there is one parenthesis that is missing from the connect descriptor; generally it is the right side one. You can take an entry in tnsnames.ora, as for example:

ERPAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERPAPP)
(SERVER = DEDICATED)
)

Have you found any error in the above entry? No…..but I have noticed that there is a single parenthesis missing. Even if the connect identifier is correct but its connect descriptor is not correct then it will cause ORA-12154 error in oracle.

Solution To Fix This Error:

You can try the below codes to fix ORA-12154 due to missing parenthesis:

 ERPAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERPAPP)
(SERVER = DEDICATED)
)
)

Using the above codes, the connect identifier is correct.

Error Message #5: ORA-12154 Due to Searching for Wrong Domain

There are some database environments that have been set to the default domain for search which may result ora-12154 error is there is some connect descriptor are configured wrong by these queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = example.com

Then we tried to connect to the database.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:31:50 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution To Fix This Error

If the ora-12154 error takes place due to wrong domain then you can turn off by connecting out NAMES.DEFAULT_DOMAIN by the following queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#NAMES.DEFAULT_DOMAIN = example.com

After this, you can again try to connect to the database by following these queries:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:33:55 2019

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

SQL> conn hr/hr@ORCL
Connected.

You will then see that it has been connected.

Ultimate Solution: Fix ORA-12154 Error Using Oracle File Repair Tool

Apart from all these fixes, if you are unable to fix ORA-12154: TNS:could not resolve the connect identifier specified you get due to any of the reasons mentioned above then just Oracle File Repair Tool. This tool has the capability to fix any kind of error you get while using Oracle database. Several reasons are there that may cause this error but now you can easily fix it by using this tool. It has great features that allow your oracle database to overcome any kind of error and get back the database easily as it was previously saved before. All you have to do is to download and install Oracle File Repair Tool and use the step by step guide and fix the Oracle database error easily.

Steps To Fix ORA-12154 Error Using Oracle File Repair Tool

Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

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.