如何检测 Oracle 断开/停止的连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1431560/
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
How to detect Oracle broken/stalled connection?
提问by Kosi2801
In our server/client-setup we're experiencing some weird behaviour. The client is a C/C++-application which uses OCI to connect to an Oracle server (using the OTLlibrary).
在我们的服务器/客户端设置中，我们遇到了一些奇怪的行为。客户端是一个 C/C++ 应用程序，它使用 OCI 连接到 Oracle 服务器（使用OTL库）。
Every now and then the DB server dies in a way (yes this is the core issue, but from application-side we're unable to solve it but have to deal with it anyway), that the machine does not respond anymore to new requests/connections but the existing ones, like the Oracle-connections, do not drop or time out. Queries sent to the DB just never return successfully anymore.
数据库服务器时不时地以某种方式死掉（是的，这是核心问题，但从应用程序方面我们无法解决它，但无论如何必须处理它），机器不再响应新请求/connections 但现有的，如 Oracle 连接，不会丢弃或超时。发送到数据库的查询再也不会成功返回。
What possibilities (if any) are provided by Oracle to detect these stalled connections from the client-application side and recover in a more or less safe way?
Oracle 提供了哪些可能性（如果有）从客户端应用程序端检测这些停止的连接并以或多或少的安全方式恢复？
采纳答案by siddhusingh
This is a bug in Oracle ( or call it a feature ) till 11.1.0.6 and they said the patch on Oracle 11g release 1 ( patch 11.1.0.7 ) which has the fix. Need to see that. If it happens you will have to cancel ( kill ) the thread performing this action. Not good approach though
这是 Oracle 中的一个错误（或称其为特性），直到 11.1.0.6，他们说 Oracle 11g 第 1 版（补丁 11.1.0.7）上的补丁已修复。需要看到。如果发生这种情况，您将不得不取消（杀死）执行此操作的线程。虽然不是很好的方法
回答by Pirks
In all my DB schema i have a table with one constant record. Just poll such table periodically by simple SQL request. All other methods unreliable.
在我所有的数据库模式中，我有一个带有一个常量记录的表。只需通过简单的 SQL 请求定期轮询此类表。所有其他方法都不可靠。
回答by Paul Walker
Sounds like you need to fire off a query to the database (eg SELECT * FROM dual;), then if the database hasn't responded within a specified amount of time, assume the server has died and react accordingly. I'm afraid I don't know C/C++, but can you use multi-threading to fire off the statement then wait for the response, without hanging the application?
听起来您需要向数据库发出查询（例如SELECT * FROM dual;），然后如果数据库在指定的时间内没有响应，则假设服务器已经死亡并做出相应的反应。恐怕我不知道 C/C++，但是您可以使用多线程来触发语句然后等待响应，而不挂起应用程序吗？
回答by Philip Schlump
This works - I have done exactly what you are looking for. Have a parent process (A) create a child process (B). The child process (B) connects to the database, performs a query (something like "select 1 from a_table" - you will get better performance if you avoid using "dual" for this and create your own table). If (B) is successful then it writes out that it was successful and exits. (A) is waiting for a specified amount of time. I used 15 seconds. If (A) detects that (B) is still running - then it can assume that the database is hung - it Kills (B) and takes necessary actions (Like calling me on the phone with a SMS).
这有效 - 我已经完成了你正在寻找的东西。让父进程 (A) 创建子进程 (B)。子进程 (B) 连接到数据库，执行查询（类似于“从 a_table 中选择 1” - 如果您避免为此使用“dual”并创建自己的表，您将获得更好的性能）。如果 (B) 成功，那么它会写出它成功并退出。(A) 正在等待指定的时间量。我用了15秒。如果 (A) 检测到 (B) 仍在运行 - 那么它可以假设数据库已挂起 - 它会杀死 (B) 并采取必要的操作（例如通过短信给我打电话）。
If you configure SQL*NET to use a timeout you will probably notice that large queries will fail because of it. The OCI set_timeout configuration will also cause this.
如果您将 SQL*NET 配置为使用超时，您可能会注意到大型查询将因此失败。OCI set_timeout 配置也会导致这种情况。
回答by Glen
There's a set_timeoutAPI in OTL that might be useful for this.
set_timeoutOTL 中有一个API 可能对此有用。
Edit: Actually, ignore that. set_timeoutdoesn't work with OCI. Have a look at the set_timeoutdescription from herewhere it describes a technique that can be used with OCI
编辑：实际上，忽略它。 set_timeout不适用于 OCI。查看此处的set_timeout描述，其中描述了可与 OCI 一起使用的技术
回答by Sachin Chourasiya
There is a manual way to avoid this. You can open a firewall and do something like ping database after every specified duration of time. In this way the database connection will not get lost.
有一种手动方法可以避免这种情况。您可以在每个指定的时间段后打开防火墙并执行诸如 ping 数据库之类的操作。这样数据库连接就不会丢失。
idea
主意
If (current_time - lastPingTime > configuredPingTime)
{
//Dummy query
select 1 from dual;
}
