当连接变坏时，有没有办法让 JBoss 连接池重新连接到 Oracle？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/128527/
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
Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?
提问by Joshua
We have our JBoss and Oracle on separate servers. The connections seem to be dropped and is causing issues with JBoss. How can I have the JBoss reconnect to Oracle if the connection is bad while we figure out why the connections are being dropped in the first place?
我们在不同的服务器上有我们的 JBoss 和 Oracle。连接似乎已断开并导致 JBoss 出现问题。如果在我们首先找出连接断开的原因时连接不好，我如何让 JBoss 重新连接到 Oracle？
采纳答案by Steve K
There is usually a configuration option on the pool to enable a validation query to be executed on borrow. If the validation query executes successfully, the pool will return that connection. If the query does not execute successfully, the pool will create a new connection.
池上通常有一个配置选项，可以在借用时执行验证查询。如果验证查询成功执行，池将返回该连接。如果查询没有成功执行，池将创建一个新连接。
The JBoss Wikidocuments the various attributes of the pool.
在JBoss的维基文档池的各种属性。
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
Seems like it should do the trick.
似乎它应该可以解决问题。
回答by skaffman
Whilst you can use the old "select 1 from dual" trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful.
虽然您可以使用旧的“select 1 from dual”技巧，但这样做的缺点是每次从池中借用连接时都会发出额外的查询。对于大容量，这是一种浪费。
JBoss provides a special connection validator which should be used for Oracle:
JBoss 提供了一个特殊的连接验证器，应该用于 Oracle：
<valid-connection-checker-class-name>
org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker-class-name>
This makes use of the proprietary ping() method on the Oracle JDBC Connection class, and uses the driver's underlying networking code to determine if the connection is still alive.
这利用了 Oracle JDBC Connection 类上的专有 ping() 方法，并使用驱动程序的底层网络代码来确定连接是否仍然有效。
However, it's still wasteful to run this each and every time a connection is borrowed, so you may want to use the facility where a background thread checks the connections in the pool, and silently discards the dead ones. This is much more efficient, but means that if the connections dogo dead, any attempt to use them before the background thread runs its check will fail.
但是，每次借用连接时都运行它仍然很浪费，因此您可能希望使用后台线程检查池中连接并默默丢弃死连接的功能。这更有效，但意味着如果连接确实死了，任何在后台线程运行检查之前使用它们的尝试都将失败。
See the wiki docsfor how to configure the background checking (look for background-validation-millis).
有关如何配置背景检查（查找）的信息，请参阅wiki 文档background-validation-millis。
回答by abh
Not enough rep for a comment, so it's in a form of an answer. The 'Select 1 from dual'and skaffman's org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionCheckermethod are equivalent , although the connection check does provide a level of abstraction. We had to decompile the oracle jdbc drivers for a troubleshooting exercise and Oracle's internal implementation of the ping is to perform a 'Select 'x' from dual'. Natch.
没有足够的代表发表评论，所以它是一种答案。在'Select 1 from dual'与skaffman的org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker方法是等效的，虽然连接检查并提供一个抽象层。我们不得不反编译 oracle jdbc 驱动程序以进行故障排除练习，而 Oracle 的 ping 内部实现是执行'Select 'x' from dual'. 纳奇。
回答by arviarya
JBoss provides 2 ways to Validate connection: - Ping based AND - Query based
JBoss 提供了两种验证连接的方法： - 基于 Ping 和 - 基于查询
You can use as per requirement. This is scheduled by separate thread as per duration defined in datasource configuration file.
您可以根据需要使用。这是由单独的线程根据数据源配置文件中定义的持续时间安排的。
<background-validation>true</background-validation> <background-validation-minutes>1</background-validation-minutes>
Some time if you are not having right oracle driver at Jboss, you may get classcast or related error and for that connection may start dropout from connection pool. You can try creating your own ConnectionValidator class by implementing org.jboss.resource.adapter.jdbc.ValidConnectionCheckerinterface. This interface provides only single method 'isValidConnection()' and expecting 'NULL' in return for valid connection.
有时，如果您在 Jboss 上没有正确的 oracle 驱动程序，您可能会收到 classcast 或相关错误，并且该连接可能会从连接池中退出。您可以尝试通过实现org.jboss.resource.adapter.jdbc.ValidConnectionChecker接口来创建自己的 ConnectionValidator 类。此接口仅提供单一方法“ isValidConnection()”并期望“NULL”以换取有效连接。
Ex:
前任：
public class OracleValidConnectionChecker implements ValidConnectionChecker, Serializable {
private Method ping;
// The timeout (apparently the timeout is ignored?)
private static Object[] params = new Object[] { new Integer(5000) };
public SQLException isValidConnection(Connection c) {
try {
Integer status = (Integer) ping.invoke(c, params);
if (status.intValue() < 0) {
return new SQLException("pingDatabase failed status=" + status);
}
}
catch (Exception e) {
log.warn("Unexpected error in pingDatabase", e);
}
// OK
return null;
}
}
回答by Vadzim
We've recently had some floating request handling failures caused by orphaned oracle DBMS_LOCKsession locks that retained indefinitely in client-side connection pool.
我们最近遇到了一些由DBMS_LOCK在客户端连接池中无限期保留的孤立 oracle会话锁引起的浮动请求处理失败。
So here is a solution that forces session expiry in 30 minutes but doesn't affect application's operation:
所以这是一个强制会话在 30 分钟内到期但不影响应用程序运行的解决方案：
<check-valid-connection-sql>select case when 30/60/24 > sysdate-LOGON_TIME then 1 else 1/0 end
from V$SESSION where AUDSID = userenv('SESSIONID')</check-valid-connection-sql>
This may involve some slow down in process of obtaining connections from pool. Make sure to test this under load.
这可能涉及从池中获取连接的过程中的一些减慢。确保在负载下进行测试。
回答by Jakub Godoniuk
A little update to @skaffman's answer. In JBoss 7 you have to use "class-name" attribute when setting valid connection checker and also package is different:
对@skaffman 的回答进行了一些更新。在 JBoss 7 中，设置有效的连接检查器时必须使用“class-name”属性，并且包也不同：
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker" />
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker" />
