如何清除连接错误时的ODP.NET连接池? [英] How to clear the ODP.NET connection pool on connection errors?
问题描述
我正在使用NHibernate和ODP.NET连接到Oracle 11g数据库.当然，可能会出现连接错误(网络故障，数据库关闭等).我正在代码中处理所有这些异常，因此没有问题.但是，当然，用户可以重试其操作(也许只是短暂的网络故障)，这就是我的问题:
I'm using NHibernate and ODP.NET to connect to a Oracle 11g database. Of course there can be connection errors (network failure, DB down, ...). I'm handling all these exceptions in my code, so no problem there. But of course the user can retry his actions (maybe it was just a short network failure), and there comes my problem:
默认情况下，ODP.NET使用连接池.通常，这没问题，但是当用户在连接错误后重试操作时，NHibernate从ODP.NET获得无效(池化)连接.用户必须多次重试(直到池为空)才能使它再次正常工作.
ODP.NET is using connection pooling by default. No problem with that usually, but when the user retries an action after a connection error, NHibernate gets an invalid (pooled) connection from ODP.NET. The user has to retry it multiple times (until the pool is empty) to get it working again.
我当然可以在ODP.NET中禁用连接池，但是我想避免这种情况.我还阅读了有关设置的信息，该设置针对池中每个返回的连接检查与DB的连接，但是这也增加了我想避免的每个连接的往返行程.
Of course I can disable connection pooling in ODP.NET, but I'd like to avoid that. I've also read about a setting that checks the connection to the DB for each returned connection from the pool, but this adds an additional round trip to each connection which I'd like to avoid too.
是否有任何方法可以将ODP.NET配置为在任何连接引发连接异常时自动清除连接池?
Is there any way to configure ODP.NET to automatically clear the connection pool when any connection throws an connection exception?
推荐答案
如果可以使用odac(odp) 11g ，则需要为池设置验证连接".在使用之前，它可以验证连接.
If you can use odac (odp) 11g, you have setting Validate Connection for your pool. It can validate the connection before you use it.
验证连接属性可验证从池中出来的连接.仅在绝对必要时才应使用此属性，因为它会导致到数据库的往返，以在将每个连接提供给应用程序之前立即对其进行验证.如果无效连接不常见，开发人员可以创建自己的事件处理程序来检索和验证新连接，而不必使用Validate Connection属性.通常，这样可以提供更好的性能.
The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance.
If it will not be good enough - you can try this document from oracle.
连接池管理
Connection Pool Management
ODP.NET连接池管理提供了显式的连接池 对ODP.NET应用程序的控制.应用程序可以明确清除 连接池中的连接.
ODP.NET connection pool management provides explicit connection pool control to ODP.NET applications. Applications can explicitly clear connections in a connection pool.
使用连接池管理，应用程序可以执行以下操作:
Using connection pool management, applications can do the following:
注意:.NET存储过程不支持这些API.清除 使用 ClearPool 方法从连接池进行连接.
Note: These APIs are not supported in a .NET stored procedure. Clear connections from connection pools using the ClearPool method.
清除应用程序中所有连接池中的连接 域，请使用 ClearAllPools 方法.
Clear connections in all the connection pools in an application domain, using the ClearAllPools method.
从池中清除连接后，ODP.NET将重新填充池 具有至少具有设置的连接数的新连接 通过连接字符串中的最小池大小".新连接不 必然意味着该池将具有有效的连接.例如，如果 调用ClearPool或ClearAllPools时数据库服务器已关闭， ODP.NET创建新的连接，但这些连接仍然 无效，因为它们无法连接到数据库，即使 数据库稍后出现.
When connections are cleared from a pool, ODP.NET repopulates the pool with new connections that have at least the number of connections set by Min Pool Size in the connection string. New connections do not necessarily mean the pool will have valid connections. For example, if the database server is down when ClearPool or ClearAllPools is called, ODP.NET creates new connections, but these connections are still invalid because they cannot connect to the database, even if the database comes up a later time.
建议直到调用ClearPool和ClearAllPools为止 应用程序可以创建返回数据库的有效连接. .NET开发人员可以开发代码来连续检查是否 不能创建有效的数据库连接，然后调用ClearPool或 一旦做到这一点，ClearAllPools即可.
It is recommended that ClearPool and ClearAllPools not be called until the application can create valid connections back to the database. .NET developers can develop code that continuously checks whether or not a valid database connection can be created and calls ClearPool or ClearAllPools once this is true.
也可能是这篇文章将为您提供帮助.
Also, may be this post will help you.
更新: 正如@MPelletier所指出的，对于oracle 12 链接是不同的.
Update: As pointed by @MPelletier, for oracle 12 the link is different.
这篇关于如何清除连接错误时的ODP.NET连接池?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
