Oracle 9.2.0.5 "10046 " Tracing Causes Server Crash on Windows 2000? 2004-08-03 - By Freeman, Donald
I am finally getting to practice some of the tracing functionality that is discussed here and ran into something I haven 't seen dicussed here. We had a complaint about a query that performs inconsistently and I enabled my session trigger on user logon to capture it. The server failed at a point shown below. The first entry is the alert log entry. The second is my trigger. The third is an error that is appearing frequently in my trace and lastly is the error that crashed the server. We have turned off tracing and haven 't had any other errors. Any thoughts? I haven 't submitted a TAR yet because I would like to know that it 's not something I am doing. I will submit one if advised because I want to be able to confidently use the tracing methodology. I
Errors in file d:\oracle\admin\nedssarp\udump\nedssarp_ora_2676_cognosop.trc:
Thu Jul 29 13:23:14 2004
Errors in file d:\oracle\admin\nedssarp\udump\nedssarp_ora_688_cognosop.trc:
ORA-12801 (See ORA-12801.ora-code.com): error signaled in parallel query server P002
ORA-27153 (See ORA-27153.ora-code.com): wait operation failed
ORA-27300 (See ORA-27300.ora-code.com): OS system dependent operation:spwat failed with status: 6
ORA-27301 (See ORA-27301.ora-code.com): OS failure message: The handle is invalid.
ORA-27302 (See ORA-27302.ora-code.com): failure occurred at: skgpwopwait
***********************************************************************************************************************************************
*** SESSION ID:(20.3685) 2004-07-29 13:23:08.828
APPNAME mod= 'impserver.exe ' mh=0 act= ' ' ah=0
=====================
PARSING IN CURSOR #5 len=68 dep=2 uid=0 oct=42 lid=0 tim=18446744069886947494 hv=2212335334 ad= '4f558260 '
alter session set events '10046 trace name context forever, level 8 '
END OF STMT
EXEC #5:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=18446744069886840263
=====================
PARSING IN CURSOR #3 len=345 dep=1 uid=0 oct=47 lid=0 tim=18446744069886993066 hv=3240680083 ad= '48cc978c '
BEGIN
IF USER = 'COGNOSOP ' THEN
EXECUTE IMMEDIATE 'alter session set timed_statistics=true ';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size= UNLIMITED ';
EXECUTE IMMEDIATE 'alter session set tracefile_identifier= COGNOSOP ';
EXECUTE IMMEDIATE 'alter session set events ' '10046 trace name context forever, level 8 ' ' ';
END IF;
END;
*************************************************************************************************************************************************
Error 1 ( non-fatal) frequently reoccurs throughout our tracing sessions.
PARSE ERROR #3:len=144 dep=0 uid=44 oct=3 lid=44 tim=18446744069888019367 err=942
select decode( VALUE, : "SYS_B_0 ", : "SYS_B_1 ", : "SYS_B_2 ", : "SYS_B_3 ", : "SYS_B_4 " ) from V$PARAMETER where UPPER(NAME) = : "SYS_B_5 "
WAIT #3: nam= 'SQL*Net break/reset to client ' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net break/reset to client ' ela= 561 p1=1413697536 p2=0 p3=0
WAIT #3: nam= 'SQL*Net message to client ' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net message from client ' ela= 590 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net message to client ' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net message from client ' ela= 552 p1=1413697536 p2=1 p3=0
XCTEND rlbk=0, rd_only=1
WAIT #0: nam= 'SQL*Net message to client ' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #0: nam= 'SQL*Net message from client ' ela= 158902 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net message to client ' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #3: nam= 'SQL*Net message from client ' ela= 705 p1=1413697536 p2=1 p3=0
***********************************************************************************************************************************************
Error 1 (fatal) caused us to have to reboot the server
EXEC #5:c=0,e=1038304,p=0,cr=15,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744069892576177
ERROR #5:err=12801 tim=15068345
WAIT #5: nam= 'enqueue ' ela= 493 p1=1347616774 p2=1 p3=3
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 28 p1=1413697536 p2=0 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 9 p1=1413697536 p2=0 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 9 p1=1413697536 p2=0 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 8 p1=1413697536 p2=0 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #5: nam= 'SQL*Net break/reset to client ' ela= 8 p1=1413697536 p2=0 p3=0
FATAL ERROR IN TWO-TASK SERVER: error = 12152
*** 2004-07-29 13:23:14.562
ksedmp: internal or fatal error
ORA-12801 (See ORA-12801.ora-code.com): error signaled in parallel query server P002
ORA-27153 (See ORA-27153.ora-code.com): wait operation failed
ORA-27300 (See ORA-27300.ora-code.com): OS system dependent operation:spwat failed with status: 6
ORA-27301 (See ORA-27301.ora-code.com): OS failure message: The handle is invalid.
ORA-27302 (See ORA-27302.ora-code.com): failure occurred at: skgpwopwait
Current SQL statement for this session:
select "c22 " "c1 ", "c21 " "c2 ", "c20 " "c3 ", "c16 " "c4 ", "c14 " "c5 ", "c18 " "c6 ", "c19 " "c7 ", "c18 " "c8 ", "c18 " "c9 ", "c17 " "c10 ", "c16 " "c11 ", "c15 " "c13 " from (select count(T3. "IDR_PARTY_ID ") "c14 " from "PADOHSNAP ". "PERSON_NAME " T1, "PADOHSNAP ". "LOCAL_ADDRESS_CODE " T2, "PADOHSNAP ". "PARTY " T3, "PADOHSNAP ". "PARTY_JURISDICTION " T7, "PADOHSNAP ". "PARTY_PERMISSIONS " T4, "PADOHSNAP ". "PARTY_LOCATION_PARTICIPAT " T8, "PADOHSNAP ". "LOCATION " T9, "PADOHSNAP ". "TELECOMMUNICATION_LOCATION " T5, "PADOHSNAP ". "PARTY_ROLE " T6 where T3. "IDR_PARTY_ID "=T1. "IDR_PARTY_ID " and T7. "PARTY_ID "=T3. "IDR_PARTY_ID " and T7. "JURISDICTION_CODE_VALUE "=T2. "CODE_VALUE " and T4. "IDR_PARTY_ID "=T1. "IDR_PARTY_ID " and T8. "IDR_PARTY_ID "=T3. "IDR_PARTY_ID " and T8. "IDR_LOCATION_ID "=T9. "IDR_LOCATION_ID " and T5. "IDR_LOCATION_ID "=T9. "IDR_LOCATION_ID " and T1. "IDR_PARTY_ID "=T6. "PARTY_ID " and T6. "ROLE_TYPE_CODE_VALUE "=: "SYS_B_00 " and T2. "PRINT_NAME "=: "SYS_B_01 " and T4. "PERMISSION_CODE_VALUE "=: "SYS_B_02 " and T3. "ROW_STATUS " in (: "SYS_B_03 ", : "SYS_B_04 ") and T7. "ROW_STATUS "=: "SYS_B_05 " and T7. "ROW_STATUS "=: "SYS_B_06 " and T5. "TELECOM_TYPE_CODE_VALUE "=: "SYS_B_07 ") D3, (select upper (T5. "URI ") "c15 ", T4. "PERMISSION_CODE_VALUE " "c16 ", T3. "ROW_STATUS " "c17 ", T1. "IDR_PERSON_ID " "c18 ", T1. "IDR_PARTY_ID " "c19 ", T3. "IDR_PARTY_ID " "c20 ", T2. "PRINT_NAME " "c21 ", ((upper (T1. "SURNAME ") || : "SYS_B_08 ") || upper (upper (T1. "GNAME "))) "c22 " from "PADOHSNAP ". "PERSON_NAME " T1, "PADOHSNAP ". "LOCAL_ADDRESS_CODE " T2, "PADOHSNAP ". "PARTY " T3, "PADOHSNAP ". "PARTY_JURISDICTION " T7, "PADOHSNAP ". "PARTY_PERMISSIONS " T4, "PADOHSNAP ". "PARTY_LOCATION_PARTICIPAT " T8, "PADOHSNAP ". "LOCATION " T9, "PADOHSNAP ". "TELECOMMUNICATION_LOCATION " T5, "PADOHSNAP ". "PARTY_ROLE " T6 where T3. "IDR_PARTY_ID "=T1. "IDR_PARTY_ID " and T7. "PARTY_ID "=T3. "IDR_PARTY_ID " and T7. "JURISDICTION_CODE_VALUE "=T2. "CODE_VALUE " and T4. "IDR_PARTY_ID "=T1. "IDR_PARTY_ID " and T8. "IDR_PARTY_ID "=T3. "IDR_PARTY_ID " and T8. "IDR_LOCATION_ID "=T9. "IDR_LOCATION_ID " and T5. "IDR_LOCATION_ID "=T9. "IDR_LOCATION_ID " and T1. "IDR_PARTY_ID "=T6. "PARTY_ID " and T6. "ROLE_TYPE_CODE_VALUE "=: "SYS_B_09 " and T2. "PRINT_NAME "=: "SYS_B_10 " and T4. "PERMISSION_CODE_VALUE "=: "SYS_B_11 " and T3. "ROW_STATUS " in (: "SYS_B_12 ", : "SYS_B_13 ") and T7. "ROW_STATUS "=: "SYS_B_14 " and T7. "ROW_STATUS "=: "SYS_B_15 " and T5. "TELECOM_TYPE_CODE_VALUE "=: "SYS_B_16 ") D2 order by "c5 " asc, "c1 " asc
-- -- Call Stack Trace -- --.........................................................
Don Freeman
Database Administrator 1
Pennsylvania Dept of Health
Bureau of Information Technology
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
