Capturing the status when database hangs is useful for getting Oracle Support
CHECK FOR WAITING SESSION
set lines 132 pages 999
column event format a30
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
See: Note 43718.1 VIEW “V$SESSION_WAIT” Reference Note
TRACE 10046
10046 trace gathers tracing information about a session.
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events ‘10046 trace name context forever,level 12′;
TAKE HANGANALYZE DUMP
SQL>sqlplus “/ as sysdba”
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug hanganalyze 3
SQL>oradebug tracefile_name
[or]
SQL>alter session set max_dump_file_size = unlimited;
SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3′;
TAKE SYSTEMSTATE DUMP
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump systemstate 266
SQL>oradebug tracefile_name
[or]
SQL>alter session set max_dump_file_size = unlimited;
SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266′;
For Oracle 9.2.0.5 and less use level 10 instead of 266
alter session set events ‘10998 trace name context forever, level 1′;
alter session set events ‘immediate trace name systemstate level 10′;
Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.
In 10g login using sqlplus -prelim “/ as sysdba” and run systemstate or hanganalyze
In Solaris
(gdb) file $ORACLE_HOME/bin/oracle
(gdb) attach your_pid
(gdb) call ksudss(10)
(gdb) detach
TAKE ERRORSTACK DUMP
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
http://askdba.org/weblog/?p=81
