Ora 12154 как исправить

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

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.

ORA-12154 means that your connection tools (e.g. sqlplus) cannot use TNSNAMES naming method for some reason.

This reminds me that there’re same error patterns in TNS-03505: Failed to resolve name. In that post, I used tnsping to test the connectivity to the listener.

In fact, all symptoms in ORA-12154 will be found in TNS-03505. The difference is, ORA-12154 will be seen in sqlplus or other connection tools, TNS-03505 will be seen in tnsping or listener logs.

There’re several possible causes that may throw ORA-12154:

1. Absent Connect Method
2. Missing tnsnames.ora File
3. Connect Identifier Mismatch
4. Searching for Wrong Domain
5. Missing Parenthesis

Let’s do some tests to reproduce the error by sqlplus.

Due to Absent Connect Method

There could be one of connect methods is missing from your settings.

Local Naming Method is Absent

First of all, we set TNS_ADMIN environment variable explicitly.

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)

Then we tried to connect to the database.

C:UsersEd>sqlplus /nolog

We saw ORA-12154 in sqlplus. This is because we have no TNSNAMES naming method to support our connection.

Easy Connect Method is Absent

For the same reason, if you are going to use Easy Connect method to connect to the database, you have to make sure that EZCONNECT is in the list of NAMES.DIRECTORY_PATH. Otherwise, you will get ORA-12154 like this:

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

Solution to ORA-12154

Let’s make sure TNSNAMES and EZCONNECT naming method are added 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> conn hr/hr@10.10.10.1:1521/orcl
Connected.

ORA-12154 was solved.

Due to Missing tnsnames.ora File

I deliberately deleted tnsnames.ora for testing the effect.

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

Then we tried to connect to the database.

C:UsersEd>sqlplus /nolog

This is because sqlplus found no file to lookup the connect identifier.

Solution to ORA-12154

Let’s create a new tnsnames.ora or 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

Then we tried to connect to the database again.

C:UsersEd>sqlplus /nolog

Due to Connect Identifier Mismatch

I deliberately changed the connect identifier while connecting.

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

Solution to ORA-12154

We should use the correct connect identifier to connect to the database.

SQL> conn hr/hr@ORCL
Connected.

It told us that we should use a matched connect identifier which also exists in tnsnames.ora.

Due to Searching for Wrong Domain

Some database environments which have been set the default domain for search may result ORA-12154 if some connect descriptors are wrongly configured.

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

Solution to ORA-12154

In such situation, we can turn it off by commenting out NAMES.DEFAULT_DOMAIN:

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 again.

C:UsersEd>sqlplus /nolog

We connected.

Due to Missing Parenthesis

Sometimes, you may not notice that there’s one parenthesis is missing from the connect descriptor, usually it’s the right side one. Let’s take an entry in tnsnames.ora for example.

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

Can you tell the problem in the entry? Yes, I missed one parenthesis, a right round bracket. Even though the connect identifier is correct, but its connect descriptor is wrong, which will cause ORA-12154 eventually.

Solution to ORA-12154

To correct the structure of connect descriptor, we should make them paired:

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

Now the connect identifier is fine.