Sunday, March 3, 2013

ORACLE - Hang Analyze dump for a blocking session

1.Find the oracle pid for the session:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID

2. Collect the dump using oradebug for blocking session :
$ sqlplus "/as sysdba"
    oradebug setorapid <ora pid> 
    oradebug unlimit
    oradebug dump errorstack 3
    <wait 1 minute>
    oradebug dump errorstack 3
    <wait 1 minute>
    oradebug dump errorstack 3
   <wait 1 minute>
    oradebug dump errorstack 3
    exit

If a blocking process is found, please also dump 3-4 errorstacks for the blocking process.

3. Generate RAC aware systemstate and hanganalyze :
Execute the following steps to take a hang analyze dump and a system state dump:
       $ sqlplus '/ as sysdba'
        oradebug setmypid
        oradebug unlimit
        oradebug setinst all
        oradebug -g all hanganalyze 4
        oradebug -g all dump systemstate 10
        oradebug tracefile_name
        quit
(ii) Wait 1-2 minutes and then repeat step 1 to take a second set of dumps.

4. Generate the truss output:
truss -o /tmp/mytruss_23150 -p 23150

5. systemstate and hanganalyze:
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3';
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

No comments:

Post a Comment