oracle会话挂死时的数据采集

#!/bin/sh

function get_oracle_info
{
   SERIAL=$1
su - oracle -c sqlplus /nolog << EOF
connect as sysdba
--hang analyze
oradebug setmypid
oradebug hanganalyze 3
set linesize 1000
--session
spool $DIR/session_$SERIAL.log
select * from v\$session;
spool off
--session wait
spool $DIR/session_wait_$SERIAL.log
SELECT * FROM v\$session_wait ORDER BY sid;
spool off
--lock
spool $DIR/lock_$SERIAL.log
select al.owner "owner",al.object_name "tname",al.object_type "ttype",lo.SESSION_ID "sid",
s.OSUSER "user",s.program "program",l.CTIME "ctime",l.block "=block",
l.type "ltype",p.SPID "spid",s.SERIAL#,c.sql_text "SQL"
from v\$locked_object lo,all_objects al,v\$session s,v\$lock l,v\$process p,v\$sqltext c
where  al.object_id=lo.OBJECT_ID and
    s.SID=lo.SESSION_ID and
    l.SID=lo.SESSION_ID and
    p.ADDR=s.PADDR  and
    c.hash_value =s.sql_hash_value;
spool off
exit
EOF
}
DIR=/tmp/orahang
rm -r $DIR 2>/dev/null
mkdir -p $DIR
chmod -R 777 $DIR

#OS
vmstat 2 10 >$DIR/vmstat.out
iostat 2 10 >$DIR/iostat.out
ps -ef|grep ora > $DIR/ora_process.out
ps -ef > $DIR/all_process.out
ps auxw --sort=%cpu > $DIR/cpu.out
df -k > $DIR/df.out

#DB
cp /oracle/admin/sid/bdump/alert_sid.log $DIR/
cp /oracle/admin/sid/udump/* $DIR/

get_oracle_info 1
sleep 5
get_oracle_info 2

#tar
tar -cvzf /tmp/orahang.tar.Z -P $DIR

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35510/viewspace-680183/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35510/viewspace-680183/