当前位置：文江博客话题详情

当连接出现问题时，有什么方法可以让 JBoss 连接池重新连接到 Oracle？

发布于 2024-07-06 18:21:06 字数 115 浏览 6 评论 0原文

我们的 JBoss 和 Oracle 位于不同的服务器上。连接似乎已断开并导致 JBoss 出现问题。如果连接状况不佳，同时我们首先要找出连接被丢弃的原因，我怎样才能让 JBoss 重新连接到 Oracle？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦在深巷 2024-07-13 18:21:06

虽然您可以使用旧的“从对偶中选择 1”技巧，但这样做的缺点是每次从池中借用连接时都会发出额外的查询。对于大批量来说，这是浪费的。

JBoss 提供了一个适用于 Oracle 的特殊连接验证器：

<valid-connection-checker-class-name>
    org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker-class-name>

它利用 Oracle JDBC Connection 类上专有的 ping() 方法，并使用驱动程序的底层网络代码来确定连接是否仍然有效。

但是，每次借用连接时都运行此操作仍然很浪费，因此您可能需要使用后台线程检查池中的连接并默默地丢弃失效连接的功能。这效率更高，但意味着如果连接确实失效，则在后台线程运行检查之前使用它们的任何尝试都将失败。

请参阅 wiki 文档了解如何配置背景检查（查找 background-validation-毫利斯）。

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.

JBoss provides a special connection validator which should be used for 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.

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 do go dead, any attempt to use them before the background thread runs its check will fail.

See the wiki docs for how to configure the background checking (look for background-validation-millis).

回复收藏 0 原文

清风挽心 2024-07-13 18:21:06

池上通常有一个配置选项，可以在借用时执行验证查询。如果验证查询成功执行，池将返回该连接。如果查询未成功执行，池将创建一个新连接。

JBoss Wiki 记录了池的各种属性。

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

看起来应该可以解决问题。

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 Wiki documents the various attributes of the pool.

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

Seems like it should do the trick.

回复收藏 0 原文

迟月 2024-07-13 18:21:06

没有足够的代表来发表评论，所以它以答案的形式出现。尽管连接检查确实提供了一定程度的抽象，但“Select 1 from Dual”和 skaffman 的 org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker 方法是等效的。我们必须反编译 Oracle jdbc 驱动程序以进行故障排除练习，Oracle 的 ping 内部实现是执行'Select 'x' from Dual'。纳奇。

回复收藏 0 原文

旧人哭 2024-07-13 18:21:06

JBoss 提供了 2 种验证连接的方法：
- 基于 Ping 的 AND
- 基于查询

您可以根据要求使用。这是由单独的线程根据数据源配置文件中定义的持续时间进行调度的。

<background-validation>true</background-validation> <background-validation-minutes>1</background-validation-minutes>

有时，如果您在 Jboss 上没有正确的 Oracle 驱动程序，您可能会收到类转换或相关错误，并且该连接可能会开始从连接池中丢失。您可以尝试通过实现 org.jboss.resource.adapter.jdbc.ValidConnectionChecker 接口来创建自己的 ConnectionValidator 类。此接口仅提供单一方法“isValidConnection()”，并期望“NULL”返回有效连接。

前任：

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;
   }
}

JBoss provides 2 ways to Validate connection:
- Ping based AND
- Query based

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.ValidConnectionChecker interface. This interface provides only single method 'isValidConnection()' and expecting 'NULL' in return for valid connection.

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;
   }
}

回复收藏 0 原文

云柯 2024-07-13 18:21:06

对@skaffman 的答案进行一点更新。在 JBoss 7 中，设置有效连接检查器时必须使用“class-name”属性，并且包也不同：

回复收藏 0 原文

忘年祭陌 2024-07-13 18:21:06

最近，我们遇到了一些由于孤立的 Oracle DBMS_LOCK 会话锁导致的浮动请求处理失败，这些会话锁无限期地保留在客户端连接池中。

因此，这里有一个强制会话在 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>

这可能会导致从池中获取连接的过程变慢。确保在负载下进行测试。

We've recently had some floating request handling failures caused by orphaned oracle DBMS_LOCK session locks that retained indefinitely in client-side connection pool.

So here is a solution that forces session expiry in 30 minutes but doesn't affect application's operation:

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

回复收藏 0 原文

~没有更多了~