4

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 opening 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

Mubashir Khan
  • 1,464
  • 1
  • 12
  • 17
  • I don't suppose this is a .NET project? I ask because we had no issues with the ODP .NET driver on 10g, worked great. – Nate Pinchot Jun 25 '10 at 21:30
  • no its not .NET. we are using OLEDB provider for oracle with ADO in VC++ project – Mubashir Khan Jun 28 '10 at 06:26
  • Do you use any static methods to open database connections? It is a long shot since this is OLEDB/Oracle/VC++, but there is documented behavior in .NET/SQL Server of database pool corruption when a database connection is opened in a static method. – Phil Hunt Nov 06 '10 at 13:40
  • its oracle oledb and vc++ using classical ado. not using .NET providers – Mubashir Khan Nov 10 '10 at 09:42

4 Answers4

1

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.

alexyorke
  • 4,224
  • 3
  • 34
  • 55
  • how is this 'mark the connection invalid' functionality is available with the classical ado. this whole thing is supposed to be done by driver itself. SQL provider works fine, but oracle provider does not. – Mubashir Khan Nov 10 '10 at 09:43
  • The OP said "it is returned back to the pool instead of being invalid." and my response was probably too simplistic. – alexyorke Dec 13 '12 at 20:58
0

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.

Charles Owen
  • 2,403
  • 1
  • 14
  • 25
0

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

Mubashir Khan
  • 1,464
  • 1
  • 12
  • 17
-1

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.

HamoriZ
  • 2,370
  • 18
  • 38
  • 1
    DBPROP_CONNECTIONSTATUS is supposed to do this at driver level, but oracle provider does not support it properly. – Mubashir Khan Nov 10 '10 at 09:45
  • Can you think of the performance degradation of this approach? Isn't connection pooling meant to "increase the system performance?" – Jaywalker Nov 12 '10 at 11:50
  • It depends on the case. In most cases it has just a slightly negative effect. What if you can not afford to have invalid connections in your pool? In most production systems it is not an option. Let's image there is a short networrk issue an you loose your db. Then all of the conections will be invalid... – HamoriZ Nov 12 '10 at 14:22