Merge Oracle OLEDB connections and invalid connections

Question

We use ADO to access Oracle 10g release 2, the Oledb provider for Oracle 10g. We are facing some problems with the connection pool. The database is on a remote machine, and pooling is done as it should. But if for some reason the remote machine drops, the connection returns from the pool, and the request for this connection fails. When this connection is closed, it is returned back to the pool instead of being invalid. Subsequent requests to open the connection are successful, but the request fails. This strange behavior, according to the OLEDB specifications, the provider must support the DBPROP_CONNECTIONSTATUS property, so if the connection is invalid, it will not be returned to the pool.

Things get in when a remote machine arrives. The connections in the pool are still invalid, and although the opening of the connection succeeds, the connection request fails. Oracle OLEDB can no longer connect to the server, and we must restart our application. This is undesirable because our application is a critical application.

Any ideas on how to overcome this.

Thanks Mubashir

Answer 1

Connections are restored after 10 minutes by default. The time can be set with the SPTimeout registry key under the root key of the oledb provider.

Answer 2

If you do this programmatically, use a try block, so if something happens, it will not work. With the try block, you can catch the exception and ignore it so that the errors are removed.

You can tell the pool not to accept invalid connections by marking the invalid connection before it returns to the pool.

Answer 3

In most connection pool implementations, you can test the connection before using it. For example: you define a validation request, for example select * from dual, and if you get a connection from the pool, this request will be executed. If this fails, the connection will be excluded from the pool and a new one will be opened.