Ошибка при соединении ora 12154

• testomat.io управление авто тестами

• Python
• Реклама
• Работа
• Консультации
• Обучение

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

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.