Oracle ORA-12154：TNS：无法解析服务名称错误？

Oracle ORA-12154: TNS: Could not resolve service name Error?

提问by Joe

I am a SQL Server user .

我是 SQL Server 用户。

I am on a project that is using oracle (which I rarely use) I need to create an ODBC connection so I can access the some data via MS Access I have a application on my machine called oraHome90. It seems to allow a configuration of something called a listener in a “net configuration utility”, I think that a “Local Net Service Name Configuration” needs to also be done. The IT support gave me this information to set up the ODBC connection . I have tried every combination that I can think of. I can get past a test that successfully passes a test to “login“ to the oracle server database. When I try to create the ODBC connection I get the following error: ORA-12154: TNS: Could not resolve service name.

我在一个使用 oracle（我很少使用）的项目中，我需要创建一个 ODBC 连接，以便我可以通过 MS Access 访问一些数据 我在我的机器上有一个名为 oraHome90 的应用程序。似乎允许在“网络配置实用程序”中配置称为侦听器的东西，我认为还需要完成“本地网络服务名称配置”。IT 支持人员向我提供了此信息以设置 ODBC 连接。我尝试了所有我能想到的组合。我可以通过一个测试，该测试成功通过了“登录”到 oracle 服务器数据库的测试。当我尝试创建 ODBC 连接时，出现以下错误：ORA-12154：TNS：无法解析服务名称。

Assuming that I want to start from scratch and the following information is supposed to allow for me to connect to the database….. Any suggestions or comment ? Note: ultimately the project will have a website .ASP page query the data, but I have to first prove that I can see the data using the ODBC connection via MS Access

假设我想从头开始并且以下信息应该允许我连接到数据库......有什么建议或评论吗？注意：最终项目会有一个网站.ASP页面查询数据，但是我要先证明我可以通过MS Access使用ODBC连接看到数据

Service name: SERVICENAME
HOST = HOST.XYZi.com
User Id: MYUSERID
Password: MYPASSWORD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'Oracle Connection
Dim ocst
Dim oconn

ocst = "Provider=OraOLEDB.Oracle;" & _ 
        "Data Source=DATASOURCE;" & _ 
        "User ID=CHIJXL;" & _ 
        "Password=password;" 

set oconn = CreateObject("ADODB.Connection")

回答by warren

from http://ora-12154.ora-code.com

来自http://ora-12154.ora-code.com

ORA-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:

ORA-12154：TNS：无法解析指定的连接标识符
原因：使用连接标识符请求到数据库或其他服务的连接，并且无法使用配置的命名方法之一将指定的连接标识符解析为连接描述符. 例如，如果使用的连接标识符类型是网络服务名称，则无法在命名方法存储库中找到该网络服务名称，或者无法定位或访问该存储库。
行动：

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

• 如果您使用本地命名（TNSNAMES.ORA 文件）：

• 确保“TNSNAMES”被列为 Oracle Net 配置文件 (SQLNET.ORA) 中 NAMES.DIRECTORY_PATH 参数的值之一

• 验证 TNSNAMES.ORA 文件是否存在并且位于正确的目录中并且可以访问。

• 检查用作连接标识符的网络服务名称是否存在于 TNSNAMES.ORA 文件中。

• 确保 TNSNAMES.ORA 文件中的任何地方都没有语法错误。查找不匹配的括号或杂散字符。TNSNAMES.ORA 文件中的错误可能会使其无法使用。

• 如果您使用目录命名：

• 验证“LDAP”是否被列为 Oracle Net 配置文件 (SQLNET.ORA) 中 NAMES.DIRETORY_PATH 参数的值之一。

• 验证 LDAP 目录服务器是否已启动并且可以访问。

• 验证用作连接标识符的网络服务名称或数据库名称是否已在目录中配置。

• 通过指定完全限定的网络服务名称或完整的 LDAP DN 作为连接标识符来验证所使用的默认上下文是否正确

• 如果您使用简单的连接命名：

• 验证“EZCONNECT”是否被列为 Oracle Net 配置文件 (SQLNET.ORA) 中 NAMES.DIRETORY_PATH 参数的值之一。

• 确保指定的主机、端口和服务名称正确。

• 尝试将连接标识符括在引号中。有关命名的更多信息，请参阅 Oracle 网络服务管理员指南或 Oracle 操作系统特定指南。

回答by DCookie

Going on the assumption you're using TNSNAMES naming, here's a couple of things to do:

假设您正在使用 TNSNAMES 命名，这里有几件事要做：

• Create/modify the tnsnames.ora file in the network/admin subdirectory associated with OraHome90 to include an entry for your oracle database:

• 在与 OraHome90 关联的 network/admin 子目录中创建/修改 tnsnames.ora 文件，以包含您的 oracle 数据库条目：

> SERVICENAME_alias =
>    (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCP)(HOST = HOST.XYZi.com)(PORT = 1521))
>     (CONNECT_DATA = (SERVICE_NAME = SERVICENAME))

> SERVICENAME_alias =
>    (DESCRIPTION =
>     (ADDRESS = (PROTOCOL = TCP)(HOST = HOST.XYZi.com)(PORT = 1521))
>     (CONNECT_DATA = (SERVICE_NAME = SERVICENAME))

This is assuming you're using the standard Oracle port of 1521. Note that servicename_alias can be any name you want to use on the local system. You may also find that you need to specify (SID = SERVICENAME) instead of (SERVICENAME=SERVICENAME).

这是假设您使用标准 Oracle 端口 1521。注意 servicename_alias 可以是您想在本地系统上使用的任何名称。您可能还会发现需要指定 (SID = SERVICENAME) 而不是 (SERVICENAME=SERVICENAME)。

• Execute tnsping servicename_alias to verify connectivity. Get this working before going any further. This will tell you if you're past the 12154 error.
• Assuming a good connection, create an ODBC DSN using the control panel, specifying the ODBC driver for Oracle of your choice (generally there's a Microsoft ODBC driver at least, and it should work adequately as a proof of concept). I'll assume the name you gave of DATASOURCE. Use the servicename_alias as the Server name in the ODBC configuration.
• At this point you should be able to connect to your database via Access. I am not a VB programmer, but I know you should be able to go to File->Get External Data->Link Tables and connect to your ODBC source. I would assume your code would work as well.

• 执行 tnsping servicename_alias 以验证连接。在继续之前先完成这项工作。这将告诉您是否已超过 12154 错误。
• 假设连接良好，使用控制面板创建一个 ODBC DSN，为您选择的 Oracle 指定 ODBC 驱动程序（通常至少有一个 Microsoft ODBC 驱动程序，它应该足以作为概念证明）。我会假设你给 DATASOURCE 取的名字。在 ODBC 配置中使用 servicename_alias 作为服务器名称。
• 此时您应该能够通过 Access 连接到您的数据库。我不是 VB 程序员，但我知道您应该能够转到 File->Get External Data->Link Tables 并连接到您的 ODBC 源。我假设你的代码也能工作。

回答by guiomie

In reference to #7 in this MSDN POST, adding a registry entry worked for me. I had Vs2010, et oracle 11.0 installed.

参考此MSDN POST 中的#7 ，添加一个注册表项对我有用。我安装了 Vs2010 和 oracle 11.0。

Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If it exists then make sure it has the right value as “Dir:\app\product\11.1.0\client_1\network\admin”. 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:\app\product\11.1.0\client_1\network\admin”

检查 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 中的注册表项“TNS_ADMIN”。如果它存在，请确保它具有正确的值，如“Dir:\app\product\11.1.0\client_1\network\admin”。如果您没有看到密钥，则创建密钥并设置适当的值，如下所示。Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue 并将其命名为 TNS_ADMIN 并赋予值“X:\app\product\11.1.0\client_1\network\admin”

回答by Anto

I struggled to resolve this problem for hours until I found an Environment variable called TNS_ADMIN set in My Computer => Properties => Advanced => Environment Variables => look in System variables for an entry called TNS_ADMIN. TNS_ADMIN is added to change the default path for Tnsnames.ora entry. This is useful when its used in a network environment where a generic tnsnames.ora entry can be setup for all the network computers. To look at the default path of tnsnames.ora add the default path in TNS_ADMIN.

我花了几个小时努力解决这个问题，直到我在我的电脑 => 属性 => 高级 => 环境变量 => 在系统变量中查找名为 TNS_ADMIN 的条目中设置了一个名为 TNS_ADMIN 的环境变量。添加 TNS_ADMIN 以更改 Tnsnames.ora 条目的默认路径。这在可以为所有网络计算机设置通用 tnsnames.ora 条目的网络环境中使用时非常有用。要查看 tnsnames.ora 的默认路径，请在 TNS_ADMIN 中添加默认路径。

回答by Mark Nold

@Warren and @DCookie have covered the solution, one thing to emphasise is the use of tnsping. You can use this to prove your TNSNames is correct before attempting to connect.

@Warren 和 @DCookie 已经涵盖了解决方案，要强调的一件事是使用tnsping. 在尝试连接之前，您可以使用它来证明您的 TNSNames 是正确的。

Once you have set up tnsnames correctly you could use ODBC or try TOrawhich will use your native oracle connection. TOra or something similar (TOAD, SQL*Plus etc) will prove invaluable in debugging and improving your SQL.

正确设置 tnsnames 后，您可以使用 ODBC 或尝试使用您的本机 oracle 连接的TOra。TOra 或类似的东西（TOAD、SQL*Plus 等）在调试和改进 SQL 方面将证明是无价的。

Last but not least when you eventually connect with ASP.net remember that you can use the Oracle data connection libraries. See Oracle.comfor a host of resources.

最后但并非最不重要的是，当您最终连接到 ASP.net 时，请记住您可以使用 Oracle 数据连接库。有关大量资源，请访问Oracle.com。

回答by Venki

It has nothing to do with a space embedded in the folder structure.

它与文件夹结构中嵌入的空间无关。

I had the same problem. But when I created an environmental variable (defined both at the system- and user-level) called TNS_HOME and made it to point to the folder where TNSNAMES.ORA existed, the problem was resolved. Voila!

我有同样的问题。但是，当我创建一个名为 TNS_HOME 的环境变量（在系统级和用户级都定义）并使其指向存在 TNSNAMES.ORA 的文件夹时，问题就解决了。瞧！

venki

文基

回答by Sertan

If there is a space in the beginning of the tns name define in file tnsnames.ora, then some of the the connectors like odbc may give this error. Remove space character in the beginning.

如果文件中定义的 tns 名称的开头有空格tnsnames.ora，则某些连接器（如 odbc）可能会出现此错误。删除开头的空格字符。

回答by stantont

This was mentioned in a commentto another answer, but I wanted to move it to an actual answer since this was also the problem in my case and I would have upvoted it if it had been an answer.

这是在对另一个答案的评论中提到的，但我想将其移至实际答案，因为这也是我的问题，如果它是答案，我会赞成。

I'm on Linux and the tnsnames.ora file was not set to readable by everyone. After making it readable connecting via tns locally worked.

我在 Linux 上并且 tnsnames.ora 文件没有设置为每个人都可读。在通过 tns 本地工作使其可读连接后。

$ chmod +r tnsnames.ora

回答by Jeremy Thompson

Arrhhh!! I RAN INTO THIS AGAIN!!!

啊啊啊！！我又遇到了这个！！！

Just install ToadForOracle in C:\ or any directory without parenthesis in the path.

只需在 C:\ 或路径中不带括号的任何目录中安装 ToadForOracle。

---

---

In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!

就我而言，这是因为我在 x64 PC 上并且仍在使用带有 32 位驱动程序的旧 Oracle 9i！

I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn't like apps that start in a path with brackets:

我正在将 SQL Reporting Services 与 Oracle 数据库一起使用。问题在于 Visual Studio (BIDS) 路径中的括号。Oracle 不喜欢以带括号的路径开头的应用程序：

RDBMS 10g XE problem with parenthesis in path

路径中带括号的 RDBMS 10g XE 问题

So I made a BAT file to open Visual Studio with Progra~2 as the short path name for "Program Files (x86)".

所以我制作了一个 BAT 文件，用 Progra~2 作为“Program Files (x86)”的短路径名打开 Visual Studio。

Here is the contents of the BAT file:

这是BAT文件的内容：

rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

I name this BAT file StartBIDS.BAT and put it in the directory:

我将此 BAT 文件命名为 StartBIDS.BAT 并将其放在目录中：

"C:\Program Files\Microsoft SQL Server\Start BIDS.bat"

“C:\Program Files\Microsoft SQL Server\Start BIDS.bat”

Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.

然后我创建了一个快捷方式到我桌面上的 BAT 文件和我的开始菜单并更改快捷方式图标。这使我可以打开用于与 Oracle 合作的 TOAD、Visual Studio、BIDS 等应用程序。

Update:

更新：

Alternatively make a Junction:

或者做一个连接点：

mklink /J "C:\Program Files (x86)\" "C:\Program Files x86\"

Then remove the brackets in the shortcut:

然后删除快捷方式中的括号：

回答by aemre

I fixed this problem using this steps.

我使用此步骤解决了这个问题。

First of all, this error occured , if you didn't install same directory or drive.

首先，如果您没有安装相同的目录或驱动器，则会发生此错误。

But the answer is here.

但答案就在这里。

1. Login windows as a Adminstrator.
2. Go to Control Panel.
3. System Properties and click Enviroment

4. Find the OS variable and change name as a "TNS_ADMIN"
   
   

5. And change the value as a "tnsnames's directory address"

6. Restart the system.

7. Congrulations.

1. 以管理员身份登录窗口。
2. 进入控制面板。
3. 系统属性并单击环境

4. 找到操作系统变量并将名称更改为“TNS_ADMIN”
   
   

5. 并将值更改为“tnsnames 的目录地址”

6. 重新启动系统。

7. 恭喜。