[问题记录.Oracle/odp.net]托管ODP中，连接池的连接验证参数(validate connection=true)无效？

问题描述：
使用ODP访问Oracle时，某些情况会开启连接池（如，存在并发访问的情况，想节省每次访问重新建立连接的时间）。但也会引来一些连接池的问题。如：遇到数据库重启、会话被kill等情况。此时直接使用从池里取出的连接就会报特定的错误，需要清理掉重新创建连接。

有一个专门的连接串参数“validate connection”来控制，是否自动来做这个检查（使用连接前，检查从池中取出的连接是否仍然可用），只是会有额外的性能消耗，建议在实际环境中验证具体影响。

多年前做过验证，存在明显的额外耗时，所以禁用了这个参数。只是针对特定错误类型，做连接池的清理。

switch (oraEx.Number)
{
    case 3113:  //ORA-03113: 通信通道的文件结尾- 可能发生于重启数据库之后重新连接时
        OracleConnection.ClearAllPools();
        throw new Exception("发生ORA-03113错误，已清空数据库连接池。", ex);
    case 28:    //ORA-00028: 会话己被终止 -  kill会话时可能发生00028或03111异常
    case 1012:  //ORA-01012: 没有登录(not logon) - 发生在ORA-00028后再访问数据库
    case 1013:  //ORA-01013: 用户请求取消当前的操作
    case 2396:  //ORA-02396: 超出最大空闲时间(exceeded maximum idle time)
    case 3111:  //ORA-03111: 通信通道收到中断 - kill会话时可能发生00028或03111异常
    case 3135:  //ORA-03135: 连接失去联系(connection lost contact)
    case 6508:  //ORA-06508: 无法找到正在调用的程序单元 - 存储过程用了全局变量可能出现
    case 12535: //ORA-12535: TNS操作超时(TNS:operation timed out)
    case 12537: //ORA-12537: 网络会话: 文件结束
    case 12570: //ORA-12570: 网络会话：意外的数据包读取错误
        if (conn != null)
        {
            OracleConnection.ClearPool(conn);
            throw new Exception("xxxxx", ex);
        }
        else
        {
            OracleConnection.ClearAllPools();
            throw new Exception("xxxxx", ex);
        }
    default:
        break;
}

 

这样也挺好，只是。。。（常在河边走，哪有不湿鞋）难免不会遇到新的状况（如，“Oracle.ManagedDataAccess.Client.OracleException (0x80004005): 连接请求超时”）。如果没有自动处理机制，问题要是发生在凌晨或业务高峰期就很不友好了。

所以，打算通过策略开启“validate connection”参数。可没想到的是，基于托管odp（Oracle.ManagedDataAccess.4.122.19.1.20191122）验证居然木有效果。。木有效果。。。。。。能说什么呢——对托管odp组件存在这样和那样的问题，早已见怪不怪了 - -||||||。   没有oracle服务号，不去登记需求bug了。。。。。暂备案待查先

 

 

附录：相关资料和参考
https://stackoverflow.com/questions/5726499/how-to-clear-the-odp-net-connection-pool-on-connection-errors
https://stackoverflow.com/questions/7845559/odp-net-connection-pooling-how-to-tell-if-a-connection-has-been-used
https://forums.asp.net/t/1701986.aspx?Oracle+Connection+to+Net+Connection+Pool
https://www.oracle.com/technetwork/database/windows/tom-091315.html
 

In this Document 

 Symptoms

 Cause 

 Solution

 References

---

APPLIES TO:

Oracle Data Provider for .NET - Version 10.2.0.1.0 and later
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)

***Checked for relevance on 24-May-2017*** 

SYMPTOMS

ODP.NET applications that have connection pooling enabled (which is the default) may intermittently experience the following error:

ORA-03135: connection lost contact

CAUSE

The nature of connection pooling is that there are frequently long lived idle connections, which may end up being disconnected by 3rd party software such as firewalls and load balancers.

Typically the exception occurs when trying to use a connection via a DataReader, DataAdapter, OracleCommand, etc, rather than during the OracleConnection::Open call.

For ease of troubleshooting, often the behavior can be reproduced from a SQLPlus session if left idle long enough.

SOLUTION

The ideal solution is to address the issue in the environment to prevent the disconnect from occurring, however several workarounds can be employed:

1) Simply catch the exception, and retry the operation.

2) Use the ODP.NET connection string parameter "validate connection=true" By default, it is set to false, which means that no checking of the connection is done when it is retrieved from the pool as a result of a OracleConnection::Open call. Setting it to true will cause ODP.NET to verify that the connection is still good by making a database round trip. If a problem with the connection is found, it is removed from the pool, and another connection is tried internally.

Note that while this typically alleviates ora-3135, it does have performance implications as every con.Open call will result in a round trip to the database. The overhead may or may not be significant depending upon application performance, so testing should be done in your environment.

3) Enable Dead Connection Detection on the database, which will result in a probe packet being sent from database to client periodically, which will usually prevent the firewall or load balancer from seeing the connection as idle.

Note 151972.1 Dead Connection Detection (DCD) Explained 

4) Connection pooling can be disabled entirely, but this will likely have a much larger performance impact.  To disable connection pooling, add "pooling=false" to the connection string.