Oracle OLEDB Connection Pooling and Invalid Connections

We are using ADO to access Oracle 10g release 2, Oledb provider for Oracle 10g. We are facing some issue with the connection pooling. The database reside on the remote machine and connection pooling is occuring as it should. But if the remote machine goes down for some reason, the connection is returned from the pool and query on that connection fails. When this connection is closed, it is returned back to the pool instead of being invalid. The subsequent connection opening requests are sucessfull but query fails. This is strange behaviour, according to OLEDB specifications, provider must support DBPROP_CONNECTIONSTATUS property, thus in case of invalid connection, it would not be returned back to the pool.

Things get weired when the remote machine comes up. The connections in the pool are still invalid and although the connection open开发者_开发百科ing succeeds, query on the connection fails. Oracle OLEDB is unable to connect to the server anymore and we have to restart our application. Well this is undesired cause our application is a critical application.

Any ideas on how to get over this.

Thanks Mubashir

If you are doing this programmatically, use a try block, so that if something does happen, it won't fail. With a try block, you can catch an exception and ignore it, so that the errors are shushed.

You could tell the pool to not accept invalid connections, by marking the connection invalid before it is returned to the pool.

Connections are recovered after 10 minutes by default. Time can be set by the registry key SPTimeout under the oledb provider's root key.

Actually the default connection pool timeout is 120 seconds at least for this Oracle 11 32-bit OLEDB installation. You can find the registry settings at:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Oracle\KEY_orac

Where KEY_orac is the KEY_<oracle_home_name>

The key name is ORAMTS_CONN_POOL_TIMEOUT and the default value is 120.

It does not appear that you can set connection pool parameters at the connection string level.

In most connection pool implementation it is possible to check the connection before using it. For example: you define a check query like select * from dual and if you pick up a connection from the pool this query will be executed. If it fails, then the connection will be excluded from the pool and a new one will be opened.