UNIX系統高負載郵件報警指令碼

###########################
#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`

[@more@]

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" < $file.log

exit 0