Intermittent vb.net oracle connection problem

Question

I am seeing a strange intermittent connection failure. My application works just fine and then starts throwing connection failure exceptions. A reset of IIS is required before the app will start working again. I've been able to trace a few of the failures back to an unexpected restart of the oracle cluster but not all of them. Has anyone else seen this problem? I've searched the web but haven't found anything.

Client - Win2003 server, IIS running ASP.net 2.0.50727, code is VB.NET, ODBC connection via Oracle Client 10.2.0.1.0

Server - Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production on a Linux cluster.

Failure:

Attempting connection.Open()
FAILED connection.Open()
Message: 
Stack Trace: 
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src,     String procedure) 
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src) 
at Oracle.DataAccess.Client.OracleConnection.Open() 
at MABridge2._0.debug.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\xxxx\My Documents\Visual Studio     2008\Projects\xxx\debug.aspx.vb:line 19
Closed connection

Code:

Imports Oracle.DataAccess.Client
Partial Public Class debug
Inherits System.Web.UI.Page

Dim loggingstring As String

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim connString As New String("Data Source=xxx_rac;Persist Security Info=True;User ID=xxx;Password=xxxxx;")
    Using conn As New OracleConnection(connString)
        Dim cmd As New OracleCommand()
        Response.Write("Attempting connection.Open()" + "<br>")
        Try
            conn.Open()
        Catch ex As OracleException
            Response.Write("FAILED connection.Open()" + "<br>")
            loggingstring = "Message: " + ex.Message + "<br>" + "Stack Trace:" + ex.StackTrace + "<br>"
            Response.Write(loggingstring)
        End Try
        conn.Close()
        Response.Write("Closed connection" + "<br>")
    End Using

End Sub

Answer 1

I assume from your connect string that you are connecting to a RAC DB consisting of more than 1 instance...

IF my assumption is true then there could be another explanation:

In a RAC configuration the Oracle listener you connect to returns information telling the client to which instance the client should connect... that means: you connect to listener A and listener A tells you to connect to instance B. This is part of what happens "behind the scenes" (in Oracle Client, not in your code).

When a listener communicates this information it doesn't tell you an IP addresse but a NAME (DNS...). IF your computer can't resolve that name you get exactly the behaviour you describe... connecting works sometimes and sometimes not... either put all hostnames relevant to all RAC nodes into the local hosts or (better) make them DNS-resolvable in your LAN...

Answer 2

Try modifying your code to dispose of your OracleCommand instance, otherwise you will have a resource leak which could be the cause of your problems.

Dim connString As New String("Data Source=...")
Using conn As New OracleConnection(connString)
    Using cmd As New OracleCommand()
        Try
            conn.Open()
        Catch ex As OracleException
            logger.LogError(ex.ToString())
            Throw 
        End Try
    End Using
End Using 

Answer 3

To summarize the problem:

• The error occurs on connection open
• the error occurs after the system has been used for a while
• The error is temporarilly solved by doing an IIS reset

Based on this it sounds as if your code is leaking connections or some other resource.

For performance counters and events see: http://download.oracle.com/docs/html/B13831_01/monitor.htm#i1005706