informix巡检脚本（Linux，aix)

admin

1. #!/bin/sh
   
2. #Author & Date :Xilai 09-08-11
   
3. #Version:0.35
   
4. #Modify by xilai 2011-10-17 fixed bug
   
5. 
   
6. usage(){
   
7. echo Usage:$(basename $0) + n
   
8. }
   
9. 
   
10. [ $# -lt 1 ]&&{
    
11. usage
    
12. exit 1
    
13. }
    
14. echo $1|grep -qv '[^0-9]'
    
15. rt=$?
    
16. [ $rt -ne 0 ] && {
    
17. usage
    
18. exit 5
    
19. }
    
20. 
    
21. check_os(){
    
22. (uname -a|grep Linux)&&return 1;
    
23. (uname -a|grep AIX)&&return 2;
    
24. (uname -a|grep HP)&&return 3;
    
25. }
    
26. 
    
27. [ `id -g` -eq `id -g informix` ] || {
    
28. echo 'Permission Denied!'
    
29. exit 2
    
30. }
    
31. #Check env
    
32. 
    
33. [ "$INFORMIXDIR" -a "$INFORMIXSERVER" ] || {
    
34. echo 'Please Set Informix env First!'
    
35. exit 3
    
36. }
    
37. 
    
38. onstat - >/dev/null
    
39. [ $? -eq 5 ]||{
    
40. echo Please start ids First ..
    
41. exit 4
    
42. }
    
43. p1=$1
    
44. if [ ! $p1 ];then
    
45.         p1=1000
    
46. fi
    
47. 
    
48. echo_title()
    
49. {
    
50.         echo >>$OUTPUT
    
51.         echo ${index}. $* >> $OUTPUT
    
52.         #((index++))
    
53.         let index=$index+1
    
54.         echo >> $OUTPUT
    
55. }
    
56. echo_back()
    
57. {
    
58.   [ $(echo $SHELL|grep bash|wc -l) -eq 1 ]&&{
    
59.         echo -n $*
    
60. }||{
    
61.         echo "$*\c"
    
62. }
    
63. }
    
64. 
    
65. DATE=$(date '+%Y%m%d')
    
66. DIR=monitor${DATE}/$INFORMIXSERVER
    
67. cd /tmp
    
68. rm -fr $DIR
    
69. mkdir -p ${DIR}
    
70. cd $DIR
    
71. OUTPUT=monitor.rep
    
72. touch $OUTPUT
    
73. index=1
    
74. DFUS=85
    
75. COMNAME=$0
    
76. DF="df -v"
    
77. if [ $(uname|grep HP|wc -l) -eq 1 ];then
    
78.        DF="bdf"
    
79. else
    
80.        DF="df -v"
    
81. fi
    
82. 
    
83. #System Command...
    
84. 
    
85. if [ "$ONCONFIG" ];then
    
86.         onconfig=$ONCONFIG
    
87. else
    
88.         onconfig=onconfig
    
89. fi
    
90. 
    
91. onlog=$(cat ${INFORMIXDIR}/etc/${onconfig}|grep -v "^#"|grep MSGPATH|awk '{print $2}')
    
92. if [ ${SHELL} = '/bin/bash' ];then
    
93.          PROFILE=.bash_profile
    
94. else
    
95.          PROFILE=.profile
    
96. fi
    
97. cp /etc/hosts .
    
98. cp /etc/services .
    
99. cp $INFORMIXDIR/etc/sqlhosts .
    
100. cp $INFORMIXDIR/etc/$onconfig .
     
101. cp ${HOME}/${PROFILE} .
     
102. cp ${onlog} .
     
103. #cp /var/adm/syslog/syslog.log .
     
104. 
     
105. echo_back Collecting System Information ...
     
106. #cpus=$(cat /proc/cpuinfo |grep processor|wc -l)
     
107. check_os>/dev/null
     
108. os=$?
     
109. case $os in
     
110.   1)
     
111.         cpus=$(cat /proc/cpuinfo |grep processor|wc -l)
     
112.         #mem=$(vmstat|sed -n '/swpd/{n;p;}'|awk '{print $4+$5+$6}')
     
113.         mem=$(free |grep Mem|awk '{print $2}')
     
114.         ;;
     
115.   2)
     
116.         #cpus=$(vmstat|grep "System configuration"|awk -F"[ |=]" '{print $4}')
     
117.         cpus=$(lsdev -Cc processor|wc -l)
     
118.         mem=$(vmstat|grep -i "System configuration"|awk -F"[ |=]" '{print $6}')
     
119.         ;;
     
120.   3)
     
121.         cpus=$(machinfo|grep "logical processors"|awk '{print $1}')
     
122.         mem=$(machinfo|grep "Memory"|awk '{print $2 $3}')
     
123.         ;;
     
124. esac
     
125. osver="$(uname)"
     
126. idsver="$(onstat -V)"
     
127. instance=$INFORMIXSERVER
     
128. echo_title System Information
     
129. echo succeeded
     
130. echo "Number of CPUS:${cpus}" >> $OUTPUT
     
131. echo "Total Memorys :${mem}" >> $OUTPUT
     
132. echo "OS Version    :${osver}" >> $OUTPUT
     
133. echo "IDS Version   :${idsver}" >> $OUTPUT
     
134. echo "Instance Name :${instance}" >> $OUTPUT
     
135. 
     
136. #Informix Command...
     
137. 
     
138. #onstat -g seg > onstat-gseg
     
139. #onstat -d > onstat-d
     
140. #onstat -g seg > onstat-gseg
     
141. #onstat -g iov > onstat-giov
     
142. #onstat -c > onstat-c
     
143. #onstat -l > onstat-l
     
144. #onstat -V > idsversion
     
145. #[ $(which i4gl 2>/dev/null) ]&&i4gl -V > 4glversion
     
146. #[ $(which esql 2>/dev/null) ]&&esql -V > esqlversion
     
147. 
     
148. db_list=$(dbaccess sysmaster << %% 2>/dev/null
     
149.       output to pipe "cat" without headings
     
150.       select name from sysdatabases
     
151.       where name not in ("sysmaster","sysutils","sysuser","onpload")
     
152. %%
     
153. )
     
154. db_list=$(echo $db_list|grep -v '^
     
155. 
     
156. 
     
157. )
     
158. 
     
159. 
     
160. #Informix SQL
     
161. 
     
162. echo_back Collecting Informix Extents Information...
     
163. 
     
164. dbaccess sysmaster << %% >/dev/null 2>&1
     
165. unload to seqscan.txt delimiter ' '
     
166. select first 20 dbsname,tabname,round(sum(seqscans)) from sysmaster:sysptprof
     
167. where tabname[1,3]<>'sys' and seqscans > 0
     
168. group by 1,2 order by 3 desc;
     
169. unload to ext delimiter ' '
     
170. select first 20 dbsname,tabname,count(*)
     
171. from sysmaster:sysextents
     
172. where tabname[1,3]<>'sys'
     
173. group by 1,2
     
174. order by 3 desc;
     
175. unload to bigtable.txt delimiter ' '
     
176. select first 20 dbsname,tabname,name,sum(size),round(sum(size)*100/16775134,2)
     
177. from sysextents a,syschunks b,sysdbspaces c
     
178. where trunc(a.start/1048576,0)=b.chknum and b.dbsnum=c.dbsnum
     
179. group by 1,2,3
     
180. order by 4 desc;
     
181. unload to bigtable1.txt delimiter ' '
     
182. select first 20 dbsname,tabname,name,sum(size),round(sum(size)*100/16775134,2)
     
183. from sysextents a,syschunks b,sysdbspaces c
     
184. where a.chunk=b.chknum and b.dbsnum=c.dbsnum
     
185. group by 1,2,3
     
186. order by 4 desc;
     
187. %%
     
188. echo_title Extents Information
     
189. #echo "      Database               Table  exts   nrows  pageused  nextsize" >> $OUTPUT
     
190. echo "            Database                         Table           exts           nrows     pageused     nextsize">> $OUTPUT
     
191. echo "    ---------------- ----------------------------- -------------- --------------- ------------ ------------">>$OUTPUT
     
192. #echo "    -----------   ----------------- ----   ------  --------  --------" >> $OUTPUT
     
193. cat ext|while read dbs tab exts;do
     
194.         oncheck -pt ${dbs}:${tab}>temp.txt
     
195.         [ $? -ne 0 ]&&continue
     
196.         nexts=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of extents"|awk '{sum+=$4}END{printf "%15d",sum}')
     
197.         nrows=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of rows"|awk '{sum+=$4}END{printf "%15d",sum}')
     
198.         npages=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of pages used"|awk '{sum+=$5}END{printf "%8d", sum}')
     
199.         nextsize=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Next extent size"|awk '{sum+=$4}END{printf "%8d",sum}')
     
200. #       echo -e "${dbs}\t${tab}\t${nexts}\t${nrows}\t${npages}\t${nextsize}" >> $OUTPUT
     
201.         #printf "%15s%20s%5d %8d     %5d     %5d\n" ${dbs} ${tab} ${nexts} ${nrows} ${npages} ${nextsize} >> $OUTPUT
     
202.         printf "%20s%30s%15d %15d     %8d     %8d\n" ${dbs} ${tab} ${nexts} ${nrows} ${npages} ${nextsize} >> $OUTPUT
     
203. done
     
204. echo "succeeded"
     
205. #Sequan Scan
     
206. echo_back Collecting Informix Sequence Scans Information...
     
207. echo_title Seqscan Information
     
208. echo "                      Database                         Table            seqs               nrows       pageused" >> $OUTPUT
     
209. echo "    -------------------------- ----------------------------- -------------- --------------------  -------------" >> $OUTPUT
     
210. cat seqscan.txt|while read dbs tab seqs;do
     
211.         oncheck -pt ${dbs}:${tab}>temp.txt
     
212.         [ $? -ne 0 ]&&continue
     
213.         nexts=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of extents"|awk '{sum+=$4}END{printf "%15d",sum}')
     
214.         nrows=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of rows"|awk '{sum+=$4}END{printf "%15d", sum}')
     
215.         npages=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Number of pages used"|awk '{sum+=$5}END{printf "%15d", sum}')
     
216.         nextsize=$(cat temp.txt|sed -e '/Extents/,$ d'|grep "Next extent size"|awk '{sum+=$4}END{printf "%15d", sum}')
     
217.         printf "%30s%30s%15.0f %20d     %10d\n" ${dbs} ${tab} ${seqs} ${nrows} ${npages} >> $OUTPUT
     
218. done
     
219. echo "succeeded"
     
220. #Big Tables
     
221. echo_back Collecting Informix Big Tables Information...
     
222. echo_title Big Tables Information
     
223. echo "            Database                   Table                 name            size         size(%)" >> $OUTPUT
     
224. echo "       -------------       -----------------       --------------   -------------  --------------" >> $OUTPUT
     
225. cat bigtable.txt > bigtable 2>/dev/null
     
226. cat bigtable1.txt >> bigtable 2>/dev/null
     
227. cat bigtable| while read a b c d e;do
     
228. printf "%20s    %20s %20s %15.2f %15.2f\n" $a $b $c $d $e>> $OUTPUT
     
229. done
     
230. echo "succeeded"
     
231. #onstat -u
     
232. echo_back Collecting Informix Serial Column Informations...
     
233. echo_title Serial Information
     
234. for db in ${db_list};do
     
235.        dbaccess ${db} << %% >/dev/null 2>&1
     
236.        unload to serial_num.$db.txt delimiter ' '
     
237.        select first 20 b.tabname,max(a.ti_serialv),round(max(a.ti_serialv)*100/2147483647,2)
     
238.        from sysmaster:systabinfo a,sysmaster:systabnames b,systables c
     
239.        where b.tabname=c.tabname and b.dbsname="$db" and a.ti_partnum=b.partnum
     
240.        group by 1 order by 2 desc;
     
241. %%
     
242.     cat serial_num.$db.txt|while read a b c;do
     
243.         printf "%20s %20s %10.2f %10.2f\n" $db $a $b $c >> $OUTPUT
     
244.     done
     
245. done
     
246. 
     
247. echo "succeeded"
     
248. echo_title Onstat -u Information
     
249. onstat -u|grep -v '^
     
250. 
     
251. 
     
252. |tail -n 1 >> $OUTPUT
     
253. 
     
254. #onstat-k
     
255. echo_title Onstat -k Information
     
256. onstat -k|grep -v '^
     
257. 
     
258. 
     
259. |tail -n 1 >> $OUTPUT
     
260. 
     
261. #Total Memory of database
     
262. 
     
263. echo_title Total Memory of database
     
264. echo_back Total Memory: >> $OUTPUT
     
265. onstat -g seg|grep Total|awk '{print $4}' >> $OUTPUT
     
266. 
     
267. echo_title Total Space of database \(Page\)
     
268. ps=$(onstat -b|grep "Buffer pool page size:"|awk -F: '{print $2/1024}')
     
269. echo "     name  nchunks          size            used         free    ratio" >> $OUTPUT
     
270. echo "  ------- ------- --------------  --------------  -------------- -----">> $OUTPUT
     
271. 
     
272. dbaccess sysmaster << %% 1>/dev/null 2>/dev/null
     
273. unload to dbinfo delimiter " "
     
274. select a.name,count(*),trunc(sum(chksize)),
     
275. trunc((sum(chksize)-sum(b.nfree))),
     
276. trunc(sum(b.nfree)),trunc((sum(chksize)-sum(b.nfree)))/trunc(sum(chksize))*100
     
277. from sysmaster:sysdbspaces a,sysmaster:syschunks b
     
278. where a.dbsnum=b.dbsnum
     
279. group by 1
     
280. %%
     
281. cat dbinfo| while read a b c d e f;do
     
282. printf "%10s    %2.0f %15.2f %15.2f %15.2f %2.2f\n" $a $b $c $d $e $f>> $OUTPUT
     
283. done
     
284. #onstat -l
     
285. echo_title Phy and logical log Information
     
286. 
     
287. physize=$(onstat -l|sed -n '/physize/{n;p;}'|awk '{print $2}')
     
288. numlogs=$(onstat -l|grep -v "^$"|tail -n 1)
     
289. logisizes=$(onstat -l|sed -n '/address/,/active/p'|grep -E -v "address|active"|awk '{sum+=$6}END{print sum}')
     
290. 
     
291. echo Physical log size:$physize >> $OUTPUT
     
292. echo Number of Logical log:$numlogs >> $OUTPUT
     
293. echo Logical log size:$logisizes >> $OUTPUT
     
294. #Database Information
     
295. echo_title Database Information
     
296. 
     
297. for db in ${db_list};do
     
298.        all=$(dbaccess ${db} << %% 2>/dev/null
     
299.        unload to ${db}.tmp delimiter " "
     
300.        select a.name,b.site,a.owner,case when is_logging=0 then 'N' when is_logging=1
     
301.        then case when is_buff_log=1 then 'B' when is_buff_log=0 then 'U' end end
     
302.        from sysmaster:sysdatabases a,systables b
     
303.        where name="$db" and tabname=' GL_COLLATE' and
     
304.        name not in ("sysmaster","sysutils","sysuser","onpload");
     
305.        unload to serial_num.$db.txt
     
306.        select first 20 b.tabname,max(a.ti_serialv),round(max(a.ti_serialv)*100/2147483647,2)
     
307.        from sysmaster:systabinfo a,sysmaster:systabnames b,systables c
     
308.        where b.tabname=c.tabname and b.dbsname=$db and a.ti_partnum=b.partnum
     
309.        group by 1 order by 2 desc;
     
310. %%
     
311. )
     
312. #       echo -e "${db}\t${all}" >> $OUTPUT
     
313.        cat ${db}.tmp >> $OUTPUT
     
314.        echo >> $OUTPUT
     
315.        dbschema -d ${db} -ss ${db}.sql >/dev/null
     
316. done
     
317. 
     
318. #onstat-p
     
319. 
     
320. echo_back Collecting Database Information...
     
321. 
     
322. echo_title Onstat-p Information
     
323. 
     
324. readcached=$(onstat -p|awk '{print $4}'|sed -n '/cached/{n;p;}')
     
325. writecached=$(onstat -p|awk '{print $8}'|sed -n '/cached/{n;p;}')
     
326. ixdara=$(onstat -p|awk '{print $1}'|sed -n '/ixda-RA/{n;p;}')
     
327. idxra=$(onstat -p|awk '{print $2}'|sed -n '/idx-RA/{n;p;}')
     
328. dara=$(onstat -p|awk '{print $3}'|sed -n '/da-RA/{n;p;}')
     
329. rapgsused=$(onstat -p|awk '{print $4}'|sed -n '/RA-pgsused/{n;p;}')
     
330. 
     
331. echo Buffer Read %cached:$readcached >> $OUTPUT
     
332. echo Buffer Write %cached:$writecached>> $OUTPUT
     
333. echo ixda-RA:${ixdara}>> $OUTPUT
     
334. echo idx-RA:${idxra}>> $OUTPUT
     
335. echo da-RA:${dara}>> $OUTPUT
     
336. echo RA-pgsused:${rapgsused}>> $OUTPUT
     
337. 
     
338. $DF|grep -v Filesystem|grep -v "-"|while read filesystem kb used ava usepe mp;do
     
339.        ud=$(echo $usepe|sed 's/%//g')
     
340.        [ $ud -gt ${DFUS} ] && echo Directory $mp \'s usage ratio is more than ${DFUS}% >> $OUTPUT||continue
     
341. done
     
342. echo "succeeded"
     
343. 
     
344. #onstat -g seg
     
345. echo_title Onstat -g seg Information
     
346. 
     
347. segc=$(($(onstat -g seg|grep V|wc -l)-2))
     
348. frbk=$(onstat -g seg|grep Total|awk '{print $8}')
     
349. 
     
350. echo 'Num of VP is:'$segc >> $OUTPUT
     
351. echo 'blkfree is:'$frbk >> $OUTPUT
     
352. 
     
353. echo_back Collecting VP Information...
     
354. touch monitor.tmp
     
355. i=1
     
356. #for((i=1;i<=${p1};i++));do
     
357. while [ $i -le ${p1} ];do
     
358. 
     
359.         echo "vmstat-${i}" >> monitor.tmp
     
360.         vmstat 1 10 >> monitor.tmp
     
361.         echo "sar-r-${i}" >> monitor.tmp
     
362.         echo "onstat-g-glo-${i}" >> monitor.tmp
     
363.         onstat -g glo >> monitor.tmp
     
364.         echo "onstat-D-${i}" >> monitor.tmp
     
365.         i=$((i+1))
     
366.         sleep 30
     
367. done
     
368. 
     
369. #VP
     
370. 
     
371. echo_title Virtual Class Information
     
372. sed -n '/^onstat-g-glo-1$/,/^onstat-D-1$/ p' monitor.tmp|sed -n \
     
373. '/Virtual processor summary:/,/Individual virtual processors:/ p' >onstat-g-glo-1
     
374. sed -n "/^onstat-g-glo-${p1}$/,/^onstat-D-${p1}$/ p" monitor.tmp|sed -n \
     
375. '/Virtual processor summary:/,/Individual virtual processors:/ p' >onstat-g-glo-${p1}
     
376. echo 'class   vps  usercpu  syscpu     total' >>$OUTPUT
     
377. echo '-----  ----   ------  ------    ------'>>$OUTPUT
     
378. for j in "cpu" "aio" "lio" "pio" "adm" "msc" "soc";do
     
379. 
     
380. 
     
381. 
     
382. 
     
383.       cat onstat-g-glo-1|grep -w ${j} >/dev/null
     
384.       [ $? -ne 0 ]&&continue
     
385.       vps1=$(cat onstat-g-glo-1|grep -w ${j}|awk '{print $2}')
     
386.       usercpu1=$(cat onstat-g-glo-1|grep -w ${j}|awk '{print $3}')
     
387.       syscpu1=$(cat onstat-g-glo-1|grep -w ${j}|awk '{print $4}')
     
388. 
     
389.       vpsi=$(cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $2}')
     
390.       usercpui=$(cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $3}')
     
391.       syscpui=$(cat onstat-g-glo-${p1}|grep -w ${j}|awk '{print $4}')
     
392. 
     
393.       usercpu=$(echo ${usercpui}-${usercpu1}|bc)
     
394.       syscpu=$(echo ${syscpui}-${syscpu1}|bc)
     
395.       total=$(echo ${usercpu}+${syscpu}|bc)
     
396. #       echo -e " $j\t${vpsi}\t${usercpu}\t${syscpu}\t${total}" >> $OUTPUT
     
397.       printf "%5s   %3d   %6.2f  %6.2f   %7.2f\n" $j ${vpsi} ${usercpu} ${syscpu} ${total} >> $OUTPUT
     
398. done
     
399. echo "succeeded"
     
400. 
     
401. #CPU
     
402. echo_back Collecting System CPU Information...
     
403. 
     
404. echo_title CPU Information
     
405. 
     
406. if [ $(uname|grep AIX|wc -l) -eq 1 ];then
     
407.       sed -n '/vmstat/,/sar-r/ p' monitor.tmp|grep -E -v "procs|swpd|sar|vmstat|id|faults|[a-z-]|^$"|\
     
408.       awk '{print 0" "$4" "$16}' > cpu.tmp
     
409. else
     
410.   if [ $(uname|grep HP|wc -l) -eq 1 ];then
     
411.       sed -n '/vmstat/,/sar-r/ p' monitor.tmp|grep -E -v "procs|swpd|sar|vmstat|id|faults|[a-z-]|^$"|\
     
412.       awk '{print 0" "$5" "$18}' > cpu.tmp
     
413.   else
     
414.       sed -n '/vmstat/,/sar-r/ p' monitor.tmp|grep -E -v "procs|swpd|sar|vmstat|id|faults|[a-z-]|^$"|\
     
415.       awk '{print $3" "$4" "$15}' > cpu.tmp
     
416.   fi
     
417. fi
     
418. 
     
419. max=$(awk 'BEGIN{max=0}{max=(max>$3)?max:$3}END{print max}' cpu.tmp)
     
420. min=$(awk 'BEGIN{min=100}{min=(min<$3)?min:$3}END{print min}' cpu.tmp)
     
421. cpuv=$(awk '{sum+=$3}END{printf "%5.2f\n",sum/NR}' cpu.tmp)
     
422. dbcpu=$(dbaccess sysmaster << %% 2>/dev/null
     
423.       output to pipe "cat" without headings
     
424. SELECT
     
425.         (SELECT SUM(usercpu)+SUM(syscpu) FROM sysvpprof)/
     
426.         (SELECT (dbinfo('UTC_CURRENT') - sh_pfclrtime) FROM sysshmvals)/
     
427.         (SELECT cf_effective FROM sysconfig WHERE cf_name = 'NUMCPUVPS')*100
     
428.        AS CPU_used_ratio
     
429. FROM systables
     
430. WHERE tabid=1
     
431. %%
     
432. )
     
433. swap=$(awk '{sum+=$1}END{printf "%5.2f\n",sum/NR}' cpu.tmp)
     
434. echo Average of OS CPU Idle:$cpuv >> $OUTPUT
     
435. echo Database CPU used ratio :$dbcpu >>$OUTPUT
     
436. echo The Max CPU Idel is     :$max >> $OUTPUT
     
437. echo The Min CPU Idel is     :$min >> $OUTPUT
     
438. echo Swap Spaces usage is    :$swap >> $OUTPUT
     
439. 
     
440. echo_back Deleting temp files...
     
441. 
     
442. rm -fr ext cpu.tmp onstat-d.tmp  onstat-g-glo-1  onstat-g-glo-2  seqscan.txt  temp.txt
     
443. echo "succeeded"
     
444. echo "Completed!"
     
445. echo >> $OUTPUT
     
446. echo "The End ..." >> $OUTPUT

复制代码