0 Replies Latest reply: Sep 14, 2012 7:12 AM by 962187 RSS

    Help trace ODBC and network connection issues

    962187
      Hello,

      I have run into a weird network connection or Oracle ODBC issue in our business in the last week or so. The problem is that at some point during the day, the connection between the client and the database is broken and we get Oracle errors 03113, 03114 and 03115.

      I tried to figure it out by looking at the trace log I got from one user, but it did not reveal anything useful to me. I'm not the DBA (I'm only a main user of the system using the db) and I do not know the architecture of the database in question. However, I suspect that this issue has something to do with recent Windows XP, Windows 7 or Java security patches which have been installed in the workstations during the last two weeks. The issue does not occur in the sessions which have been launched via Citrix, which supports my theory. Reinstalling the ODBC client software did not help.

      These are the last items in the trace log from the client side (the log is like 14 million lines long so it takes a while for the issue to appear):

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 1
                SWORD 1 <SQL_C_CHAR>
                PTR 0x54051818 [       6] "O79434"
                SQLLEN 1024
                SQLLEN * 0x5403F358 (6)

      scheduler-st 414-84c     ENTER SQLGetData
                HSTMT 0x033DEA90
                UWORD 2
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x5403F3D8

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 2
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x5403F3D8 (8)

      scheduler-st 414-84c     ENTER SQLGetData
                HSTMT 0x033DEA90
                UWORD 3
                SWORD 1 <SQL_C_CHAR>
                PTR 0x54057018
                SQLLEN 1024
                SQLLEN * 0x5403F498

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 3
                SWORD 1 <SQL_C_CHAR>
                PTR 0x54057018 [       2] "CH"
                SQLLEN 1024
                SQLLEN * 0x5403F498 (2)

      scheduler-st 414-84c     ENTER SQLGetData
                HSTMT 0x033DEA90
                UWORD 4
                SWORD 1 <SQL_C_CHAR>
                PTR 0x54057818
                SQLLEN 1024
                SQLLEN * 0x5403F4D8

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 4
                SWORD 1 <SQL_C_CHAR>
                PTR 0x54057818 [       3] "495"
                SQLLEN 1024
                SQLLEN * 0x5403F4D8 (3)

      scheduler-st 414-84c     ENTER SQLFetch
                HSTMT 0x033DEA90

      scheduler-st 414-84c     EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
                HSTMT 0x033DEA90

      scheduler-st 414-84c     ENTER SQLGetDiagRecW
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D15308
                SQLINTEGER * 0x05D157D0
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D155C0

      scheduler-st 414-84c     EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D15308
                SQLINTEGER * 0x05D157D0
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D155C0

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>

      scheduler-st 414-84c     ENTER SQLExecDirect
                HSTMT 0x033DEA90
                UCHAR * 0x05D11688 [      -3] "SELECT SEQ_ABSENCE_SERVER_ID.NEXTVAL FROM DUAL\ 0"
                SDWORD -3

      scheduler-st 414-84c     EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UCHAR * 0x05D11688 [      -3] "SELECT SEQ_ABSENCE_SERVER_ID.NEXTVAL FROM DUAL\ 0"
                SDWORD -3

      scheduler-st 414-84c     ENTER SQLNumResultCols
                HSTMT 0x033DEA90
                SWORD * 0x05D11870

      scheduler-st 414-84c     EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                SWORD * 0x05D11870 (1)

      scheduler-st 414-84c     ENTER SQLDescribeCol
                HSTMT 0x033DEA90
                UWORD 1
                UCHAR * 0x00000000
                SWORD 0
                SWORD * 0x00000000
                SWORD * 0x05D11868
                SQLULEN * 0x05D11858
                SWORD * 0x05D11848
                SWORD * 0x00000000

      scheduler-st 414-84c     EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 1
                UCHAR * 0x00000000 <null pointer>
                SWORD 0
                SWORD * 0x00000000
                SWORD * 0x05D11868 (8)
                SQLULEN * 0x05D11858 (15)
                SWORD * 0x05D11848 (0)
                SWORD * 0x00000000

      scheduler-st 414-84c     ENTER SQLFetch
                HSTMT 0x033DEA90

      scheduler-st 414-84c     EXIT SQLFetch with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90

      scheduler-st 414-84c     ENTER SQLGetData
                HSTMT 0x033DEA90
                UWORD 1
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x540D9158

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 1
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x540D9158 (8)

      scheduler-st 414-84c     ENTER SQLFetch
                HSTMT 0x033DEA90

      scheduler-st 414-84c     EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
                HSTMT 0x033DEA90

      scheduler-st 414-84c     ENTER SQLGetDiagRecW
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D11358
                SQLINTEGER * 0x05D11820
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D11610

      scheduler-st 414-84c     EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D11358
                SQLINTEGER * 0x05D11820
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D11610

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>

      scheduler-st 414-84c     ENTER SQLExecDirect
                HSTMT 0x033DEA90
                UCHAR * 0x05D11948 [      -3] "SELECT SEQ_ABSENCE_SERVER_ID.NEXTVAL FROM DUAL\ 0"
                SDWORD -3

      scheduler-st 414-84c     EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UCHAR * 0x05D11948 [      -3] "SELECT SEQ_ABSENCE_SERVER_ID.NEXTVAL FROM DUAL\ 0"
                SDWORD -3

      scheduler-st 414-84c     ENTER SQLNumResultCols
                HSTMT 0x033DEA90
                SWORD * 0x05D11B30

      scheduler-st 414-84c     EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                SWORD * 0x05D11B30 (1)

      scheduler-st 414-84c     ENTER SQLDescribeCol
                HSTMT 0x033DEA90
                UWORD 1
                UCHAR * 0x00000000
                SWORD 0
                SWORD * 0x00000000
                SWORD * 0x05D11B28
                SQLULEN * 0x05D11B18
                SWORD * 0x05D11B08
                SWORD * 0x00000000

      scheduler-st 414-84c     EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 1
                UCHAR * 0x00000000 <null pointer>
                SWORD 0
                SWORD * 0x00000000
                SWORD * 0x05D11B28 (8)
                SQLULEN * 0x05D11B18 (15)
                SWORD * 0x05D11B08 (0)
                SWORD * 0x00000000

      scheduler-st 414-84c     ENTER SQLFetch
                HSTMT 0x033DEA90

      scheduler-st 414-84c     EXIT SQLFetch with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90

      scheduler-st 414-84c     ENTER SQLGetData
                HSTMT 0x033DEA90
                UWORD 1
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x540F2BD8

      scheduler-st 414-84c     EXIT SQLGetData with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 1
                SWORD 8 <SQL_C_DOUBLE>
                PTR <unknown type>
                SQLLEN 8
                SQLLEN * 0x540F2BD8 (8)

      scheduler-st 414-84c     ENTER SQLFetch
                HSTMT 0x033DEA90

      scheduler-st 414-84c     EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
                HSTMT 0x033DEA90

      scheduler-st 414-84c     ENTER SQLGetDiagRecW
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D11618
                SQLINTEGER * 0x05D11AE0
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D118D0

      scheduler-st 414-84c     EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
                SQLSMALLINT 3 <SQL_HANDLE_STMT>
                SQLHANDLE 0x033DEA90
                SQLSMALLINT 1
                SQLWCHAR * 0x05D11618
                SQLINTEGER * 0x05D11AE0
                SQLWCHAR * 0x033E09C8
                SQLSMALLINT 500
                SQLSMALLINT * 0x05D118D0

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 2 <SQL_UNBIND>

      scheduler-st 414-84c     ENTER SQLFreeStmt
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>

      scheduler-st 414-84c     EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
                HSTMT 0x033DEA90
                UWORD 0 <SQL_CLOSE>


      Can anyone come up with a way how to trace the communication between the client and the database and see where it breaks? The DBA did not find anything in the server itself.

      Thanks for any help!