How to clear the ODP.NET connection pool on connection errors?
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 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.
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.
Is there any way to configure ODP.NET to automatically clear the connection pool when any connection throws an connection exception?
Answer
If you can use odac (odp) 11g, you have setting Validate Connection for your pool. It can validate the connection before you use it.
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 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 ClearPool method.
Clear connections in all the connection pools in an application domain, using the ClearAllPools method.
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.
Also, may be this post will help you.
Update: As pointed by @MPelletier, for oracle 12 the link is different.