Help trace ODBC and network connection issues
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!