oracle 如何在连接错误时清除 ODP.NET 连接池？

How to clear the ODP.NET connection pool on connection errors?

提问by cremor

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:

我正在使用 NHibernate 和 ODP.NET 连接到 Oracle 11g 数据库。当然，可能存在连接错误（网络故障、DB 宕机……）。我在我的代码中处理所有这些异常，所以没问题。但是当然用户可以重试他的操作（也许这只是一个短暂的网络故障），这就是我的问题：

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 默认使用连接池。通常没有问题，但是当用户在连接错误后重试操作时，NHibernate 会从 ODP.NET 获取无效（池化）连接。用户必须多次重试（直到池为空）才能使其再次工作。

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?

有没有办法配置ODP.NET在任何连接抛出连接异常时自动清除连接池？

采纳答案by evgenyl

If you can use odac (odp) 11g, you have setting Validate Connection for your pool. It can validate the connection before you use it.

如果您可以使用 odac (odp) 11g，则您必须为您的池设置验证连接。它可以在您使用之前验证连接。

The Validate Connectionattribute 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.

该验证连接属性验证来连接池出来。仅在绝对必要时才应使用此属性，因为它会导致在将每个连接提供给应用程序之前立即进行到数据库的往返验证每个连接。如果无效连接不常见，开发人员可以创建自己的事件处理程序来检索和验证新连接，而不是使用 Validate Connection 属性。这通常提供更好的性能。

If it will not be good enough - you can try thisdocument from oracle.

如果它不够好 - 你可以试试oracle 的这个文档。

Connection Pool Management

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:

Note: These APIs are not supported in a .NET stored procedure. Clear connections from connection pools using the ClearPoolmethod.

Clear connections in all the connection pools in an application domain, using the ClearAllPoolsmethod.

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.

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.

连接池管理

ODP.NET 连接池管理为 ODP.NET 应用程序提供显式连接池控制。应用程序可以明确地清除连接池中的连接。

使用连接池管理，应用程序可以执行以下操作：

注意：.NET 存储过程不支持这些 API。使用ClearPool方法从连接池中清除连接。

使用ClearAllPools方法清除应用程序域中所有连接池中的连接。

从池中清除连接后，ODP.NET 会使用新连接重新填充池，这些新连接的连接数至少为连接字符串中的 Min Pool Size 设置的连接数。新连接并不一定意味着池将具有有效连接。例如，如果在调用 ClearPool 或 ClearAllPools 时数据库服务器关闭，ODP.NET 会创建新连接，但这些连接仍然无效，因为它们无法连接到数据库，即使数据库稍后启动。

建议在应用程序可以创建回数据库的有效连接之前不要调用 ClearPool 和 ClearAllPools。.NET 开发人员可以开发代码，不断检查是否可以创建有效的数据库连接，并在创建成功后调用 ClearPool 或 ClearAllPools。

Also, may be this postwill help you.

另外，可能这篇文章会对你有所帮助。

Update: As pointed by @MPelletier, for oracle 12 the link is different.

更新：正如@MPelletier 所指出的，对于 oracle 12 ，链接是不同的。

回答by Daniel P. Bullington

Generally speaking, you should avoid trying to manipulate the connection pool for any ADO.NET provider (and also WCF channels - an aside). If you application needs to be resilient in the face of underlying data errors (e.g. timeouts, broken connections in pool, etc.) then you should implement the appropriate level of transaction to ensure data integrity and retry logic to re-execute the failed operation.

一般来说，您应该避免尝试操作任何 ADO.NET 提供程序（以及 WCF 通道 - 旁白）的连接池。如果您的应用程序需要在面对底层数据错误（例如超时、池中的连接断开等）时具有弹性，那么您应该实现适当级别的事务以确保数据完整性和重试逻辑以重新执行失败的操作。