informix巡检脚本(solaris)
#!/bin/sh[ "$INFORMIXDIR" -a "$INFORMIXSERVER" ] || {
echo 'Please Set Informix env First!'
exit 3
}
onstat - >/dev/null
[ $? -eq 5 ]||{
echo Please start ids First ..
exit 4
}
p1=$1
if [ ! $p1 ];then
p1=10
fi
echo_title()
{
echo >>$OUTPUT
echo ${index}. $* >> $OUTPUT
#((index++))
index=`expr $index \+ 1`
echo >> $OUTPUT
}
echo_back()
{ echo $SHELL|grep bash|wc -l >/dev/null
[ $? -eq 1 ]&&{
echo -n $*
}||{
echo "$*\c"
}
}
DATE=`date '+%Y%m%d'`
DATE=$DATE
DIR=monitor${DATE}/$INFORMIXSERVER
cd /tmp
rm -fr $DIR
mkdir -p ${DIR}
cd $DIR
OUTPUT=monitor.rep
touch $OUTPUT
index=1
DFUS=85
COMNAME=$0
#System Command...
if [ "$ONCONFIG" ];then
onconfig=$ONCONFIG
else
onconfig=onconfig
fi
ONLOG=`cat ${INFORMIXDIR}/etc/${onconfig}|grep -v "^#"|grep MSGPATH|awk '{print $2}'`
onlog=$ONLOG
if [ ${SHELL} = '/bin/bash' ];then
PROFILE=.bash_profile
else
PROFILE=.profile
fi
cp /etc/hosts .
cp /etc/services .
cp $INFORMIXDIR/etc/sqlhosts .
cp $INFORMIXDIR/etc/$onconfig .
cp ${HOME}/${PROFILE} .
cp ${onlog} .
#cp /var/adm/syslog/syslog.log .
echo_back Collecting System Information ...
cpus=`mpstat|wc -l`
cpus=$cpus
#mem=$(prtconf| grep )
osver=`uname`
idsver=`onstat -V`
instance=$INFORMIXSERVER
echo_title System Information
echo succeeded
echo "Number of CPUS:${cpus}" >> $OUTPUT
#echo "Total Memorys :${mem}" >> $OUTPUT
echo "OS Version :${osver}" >> $OUTPUT
echo "IDS Version :${idsver}" >> $OUTPUT
echo "Instance Name :${instance}" >> $OUTPUT
#Informix Command...
#onstat -g seg > onstat-gseg
#onstat -d > onstat-d
#onstat -g seg > onstat-gseg
#onstat -g iov > onstat-giov
#onstat -c > onstat-c
#onstat -l > onstat-l
#onstat -V > idsversion
#[ $(which i4gl 2>/dev/null) ]&&i4gl -V > 4glversion
#[ $(which esql 2>/dev/null) ]&&esql -V > esqlversion
db_list=`dbaccess sysmaster << %% 2>/dev/null
output to pipe "cat" without headings
select name from sysdatabases
where name not in ("sysmaster","sysutils","sysuser","onpload")
%%`
#Informix SQL
echo_back Collecting Informix Extents Information...
dbaccess sysmaster << %% >/dev/null 2>&1
unload to seqscan.txt delimiter ' '
select first 20 dbsname,tabname,round(sum(seqscans)) from sysmaster:sysptprof
where tabname<>'sys' and seqscans > 0
group by 1,2 order by 3 desc;
unload to ext delimiter ' '
select first 20 dbsname,tabname,count(*)
from sysmaster:sysextents
where tabname<>'sys'
group by 1,2
order by 3 desc;
unload to bigtable.txt delimiter ' '
select first 20 dbsname,tabname,name,sum(size),round(sum(size)*100/16775134,2)
from sysextents a,syschunks b,sysdbspaces c
where trunc(a.start/1048576,0)=b.chknum and b.dbsnum=c.dbsnum
group by 1,2,3
order by 4 desc;
unload to bigtable1.txt delimiter ' '
select first 20 dbsname,tabname,name,sum(size),round(sum(size)*100/16775134,2)
from sysextents a,syschunks b,sysdbspaces c
where a.chunk=b.chknum and b.dbsnum=c.dbsnum
group by 1,2,3
order by 4 desc;
%%
echo_title Extents Information
#echo " Database Tableexts nrowspageusednextsize" >> $OUTPUT
echo " Database Table exts nrows pageused nextsize">> $OUTPUT
echo " ---------------- ----------------------------- -------------- --------------- ------------ ------------">>$OUTPUT
#echo " ----------- ----------------- ---- ----------------------" >> $OUTPUT
cat ext|while read dbs tab exts;do
oncheck -pt ${dbs}:${tab}>temp.txt
[ $? -ne 0 ]&&continue
nexts=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of extents"|awk '{sum+=$4}END{printf "%15d",sum}'`
nexts=$nexts
nrows=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of rows"|awk '{sum+=$4}END{printf "%15d",sum}'`
nrows=$nrows
npages=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of pages used"|awk '{sum+=$5}END{printf "%8d", sum}'`
npages=$npages
nextsize=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Next extent size"|awk '{sum+=$4}END{printf "%8d",sum}'`
nextsize=$nextsize
# echo -e "${dbs}\t${tab}\t${nexts}\t${nrows}\t${npages}\t${nextsize}" >> $OUTPUT
#printf "%15s%20s%5d %8d %5d %5d\n" ${dbs} ${tab} ${nexts} ${nrows} ${npages} ${nextsize} >> $OUTPUT
printf "%20s%30s%15d %15d %8d %8d\n" ${dbs} ${tab} ${nexts} ${nrows} ${npages} ${nextsize} >> $OUTPUT
done
echo "succeeded"
#Sequan Scan
echo_back Collecting Informix Sequence Scans Information...
echo_title Seqscan Information
echo " Database Table seqs nrows pageused" >> $OUTPUT
echo " -------------------------- ----------------------------- -------------- ---------------------------------" >> $OUTPUT
cat seqscan.txt|while read dbs tab seqs;do
oncheck -pt ${dbs}:${tab}>temp.txt
[ $? -ne 0 ]&&continue
nexts=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of extents"|awk '{sum+=$4}END{printf "%15d",sum}'`
nexts=$nexts
nrows=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of rows"|awk '{sum+=$4}END{printf "%15d", sum}'`
nrows=$nrows
npages=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of pages used"|awk '{sum+=$5}END{printf "%15d", sum}'`
npages=$npages
nextsize=`cat temp.txt|sed -e '/Extents/,$ d'|grep "Next extent size"|awk '{sum+=$4}END{printf "%15d", sum}'`
nextsize=$nextsize
printf "%30s%30s%15.0f %20d %10d\n" ${dbs} ${tab} ${seqs} ${nrows} ${npages} >> $OUTPUT
done
echo "succeeded"
#Big Tables
echo_back Collecting Informix Big Tables Information...
echo_title Big Tables Information
echo " Database Table name size size(%)" >> $OUTPUT
echo " ------------- ----------------- -------------- ---------------------------" >> $OUTPUT
cat bigtable.txt > bigtable 2>/dev/null
cat bigtable1.txt >> bigtable 2>/dev/null
cat bigtable| while read a b c d e;do
printf "%20s %20s %20s %15.2f %15.2f\n" $a $b $c $d $e>> $OUTPUT
done
echo "succeeded"
#onstat -u
echo_back Collecting Informix Serial Column Informations...
echo_title Serial Information
for db in ${db_list};do
dbaccess ${db} << %% >/dev/null 2>&1
unload to serial_num.$db.txt delimiter ' '
select first 20 b.tabname,max(a.ti_serialv),round(max(a.ti_serialv)*100/2147483647,2)
from sysmaster:systabinfo a,sysmaster:systabnames b,systables c
where b.tabname=c.tabname and b.dbsname="$db" and a.ti_partnum=b.partnum
group by 1 order by 2 desc;
%%
cat serial_num.$db.txt|while read a b c;do
printf "%20s %20s %10.2f %10.2f\n" $db $a $b $c >> $OUTPUT
done
done
echo "succeeded"
echo_title Onstat -u Information
onstat -u|grep -v '^
|tail -1 >> $OUTPUT
#onstat-k
echo_title Onstat -k Information
onstat -k|grep -v '^
|tail -1 >> $OUTPUT
#Total Memory of database
echo_title Total Memory of database
echo_back Total Memory: >> $OUTPUT
onstat -g seg|grep Total|awk '{print $4}' >> $OUTPUT
echo_title Total Space of database \(Page\)
ps=`onstat -b|grep "Buffer pool page size:"|awk -F: '{print $2/1024}'`
ps=$ps
echo " namenchunks size used free ratio" >> $OUTPUT
echo "------- ------- ------------------------------------------ -----">> $OUTPUT
dbaccess sysmaster << %% 1>/dev/null 2>/dev/null
unload to dbinfo delimiter " "
select a.name,count(*),trunc(sum(chksize)),
trunc((sum(chksize)-sum(b.nfree))),
trunc(sum(b.nfree)),trunc((sum(chksize)-sum(b.nfree)))/trunc(sum(chksize))*100
from sysmaster:sysdbspaces a,sysmaster:syschunks b
where a.dbsnum=b.dbsnum
group by 1
%%
cat dbinfo| while read a b c d e f;do
printf "%10s %2.0f %15.2f %15.2f %15.2f %2.2f\n" $a $b $c $d $e $f>> $OUTPUT
done
#onstat -l
echo_title Phy and logical log Information
physize=`onstat -l|sed -n '/physize/{n;p;}'|awk '{print $2}'`
physize=$physize
numlogs=`onstat -l|grep -v "^$"|tail -1`
numlogs=$numlogs
logisizes=`onstat -l|sed -n '/address/,/active/p'|egrep -v "address|active"|awk '{sum+=$6}END{print sum}'`
logisizes=$logisizes
echo Physical log size:$physize >> $OUTPUT
echo Number of Logical log:$numlogs >> $OUTPUT
echo Logical log size:$logisizes >> $OUTPUT
#Database Information
echo_title Database Information
for db in ${db_list};do
all=`dbaccess ${db} << %% 2>/dev/null
unload to ${db}.tmp delimiter " "
select a.name,b.site,a.owner,case when is_logging=0 then 'N' when is_logging=1
then case when is_buff_log=1 then 'B' when is_buff_log=0 then 'U' end end
from sysmaster:sysdatabases a,systables b
where name="$db" and tabname=' GL_COLLATE' and
name not in ("sysmaster","sysutils","sysuser","onpload");
unload to serial_num.$db.txt
select first 20 b.tabname,max(a.ti_serialv),round(max(a.ti_serialv)*100/2147483647,2)
from sysmaster:systabinfo a,sysmaster:systabnames b,systables c
where b.tabname=c.tabname and b.dbsname=$db and a.ti_partnum=b.partnum
group by 1 order by 2 desc;
%%
`
all=$all
# echo -e "${db}\t${all}" >> $OUTPUT
cat ${db}.tmp >> $OUTPUT
echo >> $OUTPUT
dbschema -d ${db} -ss ${db}.sql >/dev/null
done
#onstat-p
echo_back Collecting Database Information...
echo_title Onstat-p Information
readcached=`onstat -p|awk '{print $4}'|sed -n '/cached/{n;p;}'`
readcached=$readcached
writecached=`onstat -p|awk '{print $8}'|sed -n '/cached/{n;p;}'`
writecached=$writecached
ixdara=`onstat -p|awk '{print $1}'|sed -n '/ixda-RA/{n;p;}'`
ixdara=$ixdara
idxra=`onstat -p|awk '{print $2}'|sed -n '/idx-RA/{n;p;}'`
idxra=$idxra
dara=`onstat -p|awk '{print $3}'|sed -n '/da-RA/{n;p;}'`
dara=$dara
rapgsused=`onstat -p|awk '{print $4}'|sed -n '/RA-pgsused/{n;p;}'`
rapgsused=$rapgsused
echo Buffer Read %cached:$readcached >> $OUTPUT
echo Buffer Write %cached:$writecached>> $OUTPUT
echo ixda-RA:${ixdara}>> $OUTPUT
echo idx-RA:${idxra}>> $OUTPUT
echo da-RA:${dara}>> $OUTPUT
echo RA-pgsused:${rapgsused}>> $OUTPUT
$DF|grep -v Filesystem|grep -v "-"|while read filesystem kb used ava usepe mp;do
ud=`echo $usepe|sed 's/%//g'`
[ $ud -gt ${DFUS} ] && echo Directory $mp \'s usage ratio is more than ${DFUS}% >> $OUTPUT||continue
done
echo "succeeded"
#onstat -g seg
echo_title Onstat -g seg Information
segc=`onstat -g seg|grep V|wc -l`
segc=`expr $segc \- 2`
frbk=`onstat -g seg|grep Total|awk '{print $8}'`
frbk=$frbk
echo 'Num of VP is:'$segc >> $OUTPUT
echo 'blkfree is:'$frbk >> $OUTPUT
echo_back Collecting VP Information...
touch monitor.tmp
i=1
#for((i=1;i<=${p1};i++));do
while [ $i -le ${p1} ];do
echo "vmstat-${i}" >> monitor.tmp
vmstat 2 10 >> monitor.tmp
echo "sar-r-${i}" >> monitor.tmp
echo "onstat-g-glo-${i}" >> monitor.tmp
onstat -g glo >> monitor.tmp
echo "onstat-D-${i}" >> monitor.tmp
i=`expr $i \+ 1`
done
#VP
echo_title Virtual Class Information
sed -n '/^onstat-g-glo-1$/,/^onstat-D-1$/ p' monitor.tmp|sed -n '/Virtual processor summary:/,/Individual virtual processors:/ p' >onstat-g-glo-1
sed -n "/^onstat-g-glo-${p1}$/,/^onstat-D-${p1}$/ p" monitor.tmp|sed -n '/Virtual processor summary:/,/Individual virtual processors:/ p' >onstat-g-glo-${p1}
echo 'class vpsusercpusyscpu total' >>$OUTPUT
echo '--------- ------------ ------'>>$OUTPUT
for j in "cpu" "aio" "lio" "pio" "adm" "msc" "soc";do
cat onstat-g-glo-1|grep -w ${j} >/dev/null
[ $? -ne 0 ]&&continue
vps1=`cat onstat-g-glo-1|grep -w ${j}|awk '{print $2}'`
vps1=$vps1
usercpu1=`cat onstat-g-glo-1|grep -w ${j}|awk '{print $3}'`
usercpu1=$usercpu1
syscpu1=`cat onstat-g-glo-1|grep -w ${j}|awk '{print $4}'`
syscpu1=$syscpu1
vpsi=`cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $2}'`
vpsi=$vpsi
usercpui=`cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $3}'`
usercpui=$usercpui
syscpui=`cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $4}'`
syscpui=$syscpui
usercpu=`echo ${usercpui}-${usercpu1}|bc`
usercpu=$usercpu
syscpu=`echo ${syscpui}-${syscpu1}|bc`
syscpu=$syscpu
total=`echo ${usercpu}+${syscpu}|bc`
total=$total
# echo -e " $j\t${vpsi}\t${usercpu}\t${syscpu}\t${total}" >> $OUTPUT
printf "%5s %3d %6.2f%6.2f %7.2f\n" $j ${vpsi} ${usercpu} ${syscpu} ${total} >> $OUTPUT
done
echo "succeeded"
#CPU
echo_back Collecting DB CPU Information...
echo_title CPU Information
dbcpu=`dbaccess sysmaster << %% 2>/dev/null
output to pipe "cat" without headings
SELECT
(SELECT SUM(usercpu)+SUM(syscpu) FROM sysvpprof)/
(SELECT (dbinfo('UTC_CURRENT') - sh_pfclrtime) FROM sysshmvals)/
(SELECT cf_effective FROM sysconfig WHERE cf_name = 'NUMCPUVPS')*100
AS CPU_used_ratio
FROM systables
WHERE tabid=1
%%
`
dbcpu=$dbcpu
echo Database CPU used ratio :$dbcpu >>$OUTPUT
echo >>$OUTPUT
sar 2 10>>$OUTPUT
echo "succeeded"
echo_back Deleting temp files...
rm -fr ext cpu.tmp onstat-d.tmponstat-g-glo-1onstat-g-glo-2seqscan.txttemp.txt
echo "succeeded"
echo "Completed!"
echo >> $OUTPUT
echo "The End ..." >> $OUTPUT
页:
[1]