Thursday, January 28, 2010 11:27 PM
Well this post addresses the connection pooling reference issue that is not handled in ODP.Net.
TO give an intro, ODP.Net is one among the data provider when you use oracle as a back end to connect from your .Net applications.
I worked for a project where we used ODP.Net to connect to ORacle from Asp.net. It went well for few months till we got a critical bug saying the application is showing some yellow screen when user logs in the morning. No exceptions were logged to DB as well. We were clue less what the issue is.
The error we were getting was “ORA-01012: not logged on”,When the session was killed in oracle, the connection object which was part of the connection pool maintained by ODP.Net was not cleared. When a request from the website takes a connection object form the pool to connect, it failed with Not logged in Error, which is why our exception handler couldn’t handle this issue.when we traced back, there was a auto kill script that was added by our team in during the batch job processing every night to free up resources used by sessions,
How can we handle this scenario:
1. You can configure in IIS to reset your application pool once before users could login to the system.
2. You can tweak the connection string to handle this scenario. Add an attribute Connection Lifetime which will take a value to kill all live connections in your connection pool within the idle time for your system
3. Dump ODP.net and user System.Data,OracleClient 
I will cover connection pooling concepts and the way to handle the same with connection strings in detailed in my next post.