Hinky Oracle 连接（TNSNAMES.ora 帮助）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/444741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hinky Oracle Connection (TNSNAMES.ora Help)
提问by 18Rabbit
We recently changed physical DB's, new servers, new locations, same database schema and data and since the change over, whenever we try to connect directly to the DB in our own desktop type applications about half of the time we get this error:
我们最近更改了物理数据库、新服务器、新位置、相同的数据库架构和数据，自从更改后，每当我们尝试在我们自己的桌面类型应用程序中直接连接到数据库时，大约有一半的时间会出现此错误：
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach[0]
ORA-12545: Connect failed because target host or object does not exist
The rest of the time, it connects right away without any issues. Our applications that establish connections via JDBC don't seem to have any issues but we do when going through something that does a tnsnames.ora lookup (or that's my hunch at least). TNSPING works 100% but using an oracle executable like SQLLDDR fails at least 50% of the time. Here's an anonymized snppet of our TSNNAMES file and a TNSPING output:
其余时间，它会立即连接，没有任何问题。我们通过 JDBC 建立连接的应用程序似乎没有任何问题，但我们在进行 tnsnames.ora 查找时会出现任何问题（或者至少这是我的预感）。TNSPING 100% 工作，但使用像 SQLLDDR 这样的 oracle 可执行文件至少有 50% 的时间失败。这是我们的 TSNNAMES 文件和 TNSPING 输出的匿名snppet：
DB_CONNECTION =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MY.URL.COM)
)
)
And the TNSPING:
和 TNSPING：
C:\>TNSPING DB_CONNECTION
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 1.2.3.4)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MY.URL.COM)))
OK (200 msec)
I have the SID of the DB, which is what the JDBC connections rely on but adding it to the tnsnames.ora doesn't improve the odds of making a connection. I don't know enough about how the tnsnames file is being used by oracle to intelligently resolve this issue so if you have suggestions or see something that is obviously missing please let me know.
我有 DB 的 SID，这是 JDBC 连接所依赖的，但将它添加到 tnsnames.ora 并不能提高建立连接的几率。我不太了解 oracle 如何使用 tnsnames 文件来智能地解决此问题，因此，如果您有任何建议或看到明显缺失的内容，请告诉我。
EDIT: The new DB's may be two load balanced databases which may be part of the problem.
编辑：新数据库可能是两个负载平衡的数据库，这可能是问题的一部分。
采纳答案by 18Rabbit
We recently had a similar issue with our application. The application would sometimes connect to the Oracle RAC and sometimes it would complain with ORA-12545. In short the issue was that the server had a real name while we were using virtual IP addresses in TNSNAMES.ORA. Once we added the mapping of the server name to IP address via system32\drivers\etc\hosts file everything started to work properly.
我们最近在我们的应用程序中遇到了类似的问题。应用程序有时会连接到 Oracle RAC，有时会抱怨 ORA-12545。简而言之，问题是当我们在 TNSNAMES.ORA 中使用虚拟 IP 地址时，服务器具有真实名称。一旦我们通过 system32\drivers\etc\hosts 文件添加了服务器名称到 IP 地址的映射，一切都开始正常工作。
I have written a bit more in my blog <shameless advert>http://dcarapic.blogspot.com/2009/04/intermittent-ora-12545-error.html</shameless advert>
我在我的博客 <shameless advert> 中写了更多内容http://dcarapic.blogspot.com/2009/04/intermittent-ora-12545-error.html</shameless advert>
回答by Quassnoi
If you are using Oracle 10g, you may use an Easy Connecthandler instead:
如果您正在使用Oracle 10g，则可以改用Easy Connect处理程序：
//servername/instancename
,
,
//1.2.3.4/my.url.com
in your case.
在你的情况下。
It's immune to lotsof TNSNAMESissues.
这是免疫大量的TNSNAMES问题。
回答by Karl
Ah, gotta love the intermittent problems :) (In the following I have had to replace the underscore character as I don't know the escape char for this wiki language)
啊，必须喜欢间歇性问题:)（在下面我不得不替换下划线字符，因为我不知道这种 wiki 语言的转义字符）
Are you always using the same OS user to connect and to do the tnsping?
您是否总是使用相同的操作系统用户进行连接和执行 tnsping？
Check for environment variables TNS[underscore]ADMIN pointing to different tnsnames.ora locations. Do a scan of the client for duplicate tnsnames.ora; either in locations pointed to by $TNS[underscore]ADMIN or in different $ORACLE[underscore]HOMES (e.g. if you have 2 Oracle client installs).
检查指向不同 tnsnames.ora 位置的环境变量 TNS[underscore]ADMIN。扫描客户端是否有重复的 tnsnames.ora；要么在 $TNS[underscore]ADMIN 指向的位置，要么在不同的 $ORACLE[underscore]HOMES（例如，如果您安装了 2 个 Oracle 客户端）。
Likewise check that you are using the same $ORACLE[underscore]HOME and $PATH environment variables for all connection attempts and tnsping. (E.g. always the same OS user or each user has the same values)
同样检查您是否对所有连接尝试和 tnsping 使用相同的 $ORACLE[underscore]HOME 和 $PATH 环境变量。（例如总是相同的操作系统用户或每个用户具有相同的值）
I see that the tnsping output reports version 9.2 so this is not likely, but in 11g the DB registers with the listener which can take a minute or so. (maybe also true with 10g). Attempts to connect prior to this will not find the target.
我看到 tnsping 输出报告了 9.2 版，所以这不太可能，但在 11g 中，DB 向侦听器注册，这可能需要一分钟左右的时间。（对于 10g 也可能如此）。在此之前尝试连接将找不到目标。
Another unlikely possibility - is a service with the same service name still advertised on the old host server? Remove it if at all possible.
另一种不太可能的可能性 - 旧主机服务器上是否仍在广告具有相同服务名称的服务？如果可能，请将其删除。
After that I would start to look at the network itself. Is a ping of the server always successful and quick? If you're using the hostname rather than IP in the tnsnames.ora, does the hostname reliably resolve to the correct IP (nslookup). Is there a local firewall and if so is it behaving?
之后，我将开始研究网络本身。服务器的 ping 总是成功且快速吗？如果您在 tnsnames.ora 中使用主机名而不是 IP，主机名是否可靠地解析为正确的 IP (nslookup)。是否有本地防火墙，如果有，它的行为是否正常？
Regards Karl
问候卡尔
