Oracle Connection Pool Problem

[Alex Chan]

Hi All,

I know this might not be an appropriate newsgroup for this question. I
have tried to search in OTN but seems no good solution exists. Just see any
developers face similar problem before

I have used Oracle Data Provider.NET (ODP) and built a C# window
service. I have used the oracle connection pooling as default. However, as
my oracle database would be down for backup every night. Every day, i need
to restart my C# service in order to avoid the TNS Packet Writer failure
error. I know this is becoz the previous connections pooled are no longer
valid due to the shutdown of Oracle database.

ODP suggest using an option in the connection string "valid
connection=true" to avoid this problem, however, performance penalty will be
paid for the round trip checking of connection, that indeed violates the
objective of using connection pool i think.

Any developers encountered this problem? any standard way to tackle
this?

Regards,

Alex

 

[Alberto Salvati]

Hi, Alex.

I think that you can find a solution here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;830173

HTH

Alberto Salvati

 

[Alex Chan]

Dear Alberto,

Thx for your information. But that is the fix for Microsoft Data
Adapeter for Oracle. And the one i am using is coming from Oralce. More
precisely is Oracle Data Adapter (ODP .NET)

Regards,

Alex

 

[Nicholas Paldino [.NET/C# MVP]]

Alex,

Instead of trying to compensate for it in your code, can you make it so
that the event that triggers the backup also shuts down your service while
the DB is offline, and then restarts your service when it comes back online?
This would be a better solution IMO (it decouples the maintinence of the DB
from your code, and makes your code cleaner).

Hope this helps.