###########################
#OS: HP-UX 11.11
###########################

#!/bin/sh

###########################
#Input your env parameters
###########################

sourcepath=/dbashell/top_sql_trace_log
user=test
pass=test
file=$sourcepath/topsql_`date +%E%m%d_%H%M%S`

top -d 1 -f $sourcepath/tmp_top.log
grep Load $sourcepath/tmp_top.log|awk '{print $3}'|read lav
expr substr $lav 1 1|read p1
expr substr $lav 3 2|read p2
echo $p1$p2|read lav

if test $lav -lt 150; then    #当load>1.5报警,可以自行修改
  rm $sourcepath/tmp_top.log
  exit 0
fi

cp $sourcepath/tmp_top.log $file.log
grep "oraclehpehr2" $sourcepath/tmp_top.log|head -10|awk '{print $3}' > $sourcepath/tmp_top.list
cat $sourcepath/tmp_top.list | while read pid
do
echo "select b.spid, a.sid, a.program, a.machine, c.event from v\$session a, v\$process b, v\$session_wait c" >> $file.sql
echo "where a.paddr=b.addr and b.spid="$pid" and c.sid=a.sid;" >> $file.sql
echo "select a.sid,b.sql_text from v\$session a, v\$sqltext b, v\$process c " >> $file.sql
echo "where c.spid="$pid" and a.paddr=c.addr and b.hash_value=a.sql_hash_value order by a.sid,b.piece;" >> $file.sql
done

sqlplus -s $user/$pass <$sourcepath/tmp_top.log
set linesize 200
set trims on
col spid for a5
col program for a30
col machine for a30
col event for a30
spool $file.tmp
start $file.sql
spool end
exit;
EOF

cat $file.tmp >> $file.log
rm $file.tmp
rm $file.sql
rm $sourcepath/tmp_top.log
rm $sourcepath/tmp_top.list
rm end.lst

mailx -s "Server_OverLoad" aluworlds@163.com < $file.log

exit 0
Trackback

no comment untill now

Add your comment now

切换到手机版