作者簡介：
張樂奕，通常使用的網(wǎng)名為kamus，也曾用過seraphim，現(xiàn)在任職于北京某大型軟件公司，Oracle數(shù)據(jù)庫DBA，主要負(fù)責(zé)證券行業(yè)的核心交易系統(tǒng)數(shù)據(jù)庫治理及維護(hù)工作。
熱切關(guān)注Oracle技術(shù)和相關(guān)操作系統(tǒng)技術(shù)，出沒于各大數(shù)據(jù)庫技術(shù)論壇，目前是中國最大的Oracle技術(shù)論壇www.itpub.net的數(shù)據(jù)庫治理版版主。
我的郵件地址：kamus@itpub.net
我的MSN: kamusis@hotmail.com
期望與各位朋友討論Oracle相關(guān)技術(shù)。
本人所有文章，不經(jīng)許可，不得轉(zhuǎn)載。
目錄
一、 前言... 4
二、 思路... 4
三、 vmstat腳本及步驟... 4
1. 安裝statspack. 4
2. 創(chuàng)建stats$vmstat表... 4
3. 創(chuàng)建vmstat目錄... 6
4. 創(chuàng)建get_vmstat.ksh腳本... 6
5. 創(chuàng)建run_vmstat.ksh腳本... 8
6. 創(chuàng)建crontab作業(yè)，定時(shí)執(zhí)行run_vmstat.ksh腳本... 9
7. 分析數(shù)據(jù)... 9
1) 異常報(bào)告... 9
2) 每小時(shí)趨勢(shì)報(bào)告... 13
3) 周趨勢(shì)報(bào)告... 14
4) 長期趨勢(shì)報(bào)告... 14
四、 使用Excel生成趨勢(shì)圖... 15
五、 參考資料... 15
一、 前言
作為UNIX系統(tǒng)中的Oracle數(shù)據(jù)庫治理員，時(shí)刻監(jiān)控操作系統(tǒng)的性能無疑是非常重要的，幸運(yùn)的是UNIX系統(tǒng)提供了大量的監(jiān)控命令，比如vmstat, iostat, sar, top等等，這些監(jiān)控命令均以字符界面輸出結(jié)果，再加上UNIX系統(tǒng)中shell的強(qiáng)大分析功能，這樣我們只需要編寫一些腳本就可以實(shí)現(xiàn)自動(dòng)的后臺(tái)監(jiān)控，當(dāng)有問題的時(shí)候再自動(dòng)發(fā)送郵件給DBA。
其實(shí)相類似的一些監(jiān)控腳本可能已經(jīng)隨處可見了，但是本文提到的一個(gè)新的思路，就是利用Oracle數(shù)據(jù)庫的statspack空間來存儲(chǔ)監(jiān)控的結(jié)果，再利用數(shù)據(jù)庫天生的檢索優(yōu)勢(shì)，這樣比以往靠shell分析甚或是人工分析生成的監(jiān)控結(jié)果文件要更加輕松，智能，同時(shí)也能實(shí)現(xiàn)更持久和更廣泛的監(jiān)控。
本文大部分素材來源自Donald K. Burleson的Oracle9i UNIX Administration Handbook，但是對(duì)于腳本中的一些錯(cuò)誤和不合理的地方作了修改，并且添加了一些功能，本文的測(cè)試環(huán)境全部基于Sun Solaris 8 Sparc 64bit + Oracle9.2.0.5。
本文第一版主要以監(jiān)控內(nèi)存消耗和CPU等待的vmstat命令為例，將陸續(xù)添加iostat等其它命令的監(jiān)控腳本。
二、 思路
首先安裝statspack，然后手工創(chuàng)建一個(gè)用于存儲(chǔ)vmstat命令輸出結(jié)果的表，再作一個(gè)shell定時(shí)執(zhí)行vmstat，將結(jié)果全部插入數(shù)據(jù)庫表中，最后就是通過SQL定時(shí)分析表中的數(shù)據(jù)，超過預(yù)先指定的門限值就告警。同時(shí)根據(jù)表中的數(shù)據(jù)，我們還能作出系統(tǒng)性能的趨勢(shì)報(bào)告。
三、 vmstat腳本及步驟1. 安裝statspack
sqlplus “/ as sysdba”
SQL> @?/rdbms/admin/spcreate.sql
按照提示設(shè)定perfstat用戶的密碼之后，由于是10g，我選擇了SYSAUX表空間作為perfstat用戶的默認(rèn)表空間，而沒有另行創(chuàng)建表空間。
2. 創(chuàng)建stats$vmstat表
SQL> connect perfstat/perfstat
SQL> @create_vmstat_tab.sql
# create_vmstat_tab.sql
drop table stats$vmstat;
create table stats$vmstat
(
start_date date,
duration number,
server_name varchar2(20),
running_queue number,
waiting_queue number,
swap_in number,
swap_out number,
kbytes_page_in number,
Kbytes_page_out number,
page_scan number,
user_cpu number,
system_cpu number,
idle_cpu number,
wait_cpu number
)
tablespace sysaux
storage (initial 10m
next 1m
pctincrease 0)
;
comment on column stats$vmstat.start_date
is '監(jiān)控時(shí)間';
comment on column stats$vmstat.duration
is '監(jiān)控時(shí)長';
comment on column stats$vmstat.server_name
is '服務(wù)器名稱';
comment on column stats$vmstat.running_queue
is '執(zhí)行隊(duì)列';
comment on column stats$vmstat.waiting_queue
is '等待隊(duì)列';
comment on column stats$vmstat.swap_in
is '每秒平均交換進(jìn)Lwps數(shù)';
comment on column stats$vmstat.swap_out
is '交換出進(jìn)程數(shù)';
comment on column stats$vmstat.kbytes_page_in
is '換頁進(jìn)字節(jié)(K)';
comment on column stats$vmstat.kbytes_page_out
is '換頁出字節(jié)(K)';
comment on column stats$vmstat.page_scan
is '換頁守護(hù)進(jìn)程掃描頁數(shù)';
comment on column stats$vmstat.user_cpu
is '用戶操作占用的CPU';
comment on column stats$vmstat.system_cpu
is '系統(tǒng)操作占用的CPU';
comment on column stats$vmstat.idle_cpu
is 'CPU空閑率';
comment on column stats$vmstat.wait_cpu
is 'CPU等待率(AIX獨(dú)有)';
3. 創(chuàng)建vmstat目錄
在oracle用戶主目錄下創(chuàng)建用于存放所有相關(guān)腳本的vmstat目錄。
$ cd
$ mkdir vmstat
4. 創(chuàng)建get_vmstat.ksh腳本
改腳本用于定時(shí)執(zhí)行vmstat命令并且將結(jié)果存入數(shù)據(jù)庫。
# get_vmstat.ksh
#!/bin/ksh
#----------------------------------------
# 首先設(shè)定環(huán)境變量，根據(jù)實(shí)際環(huán)境修改. . . .
#----------------------------------------
ORACLE_SID=kamusdb
eXPort ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratabgrep /^$ORACLE_SID:cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SERVER_NAME=`uname -aawk '{PRint $2}'`
typeset -u SERVER_NAME
export SERVER_NAME
#----------------------------------------
# 每5分鐘運(yùn)行一次vmstat(300秒)，可以根據(jù)實(shí)際情況修改 . . . .
#----------------------------------------
SAMPLE_TIME=300
#----------------------------------------
# 腳本一旦運(yùn)行將不會(huì)停止，除非關(guān)閉操作系統(tǒng) . . . .
# -S參數(shù)表示監(jiān)控swap空間的情況，報(bào)告si，so列
# msg$$中的$$表示一個(gè)任意2位數(shù)字，系統(tǒng)自動(dòng)生成
#----------------------------------------
while true
do
vmstat –S ${SAMPLE_TIME} 2 > /tmp/msg$$
#----------------------------------------
# Solaris系統(tǒng)的vmstat沒有wait CPU統(tǒng)計(jì)，所以我們?cè)谀且涣兄写嫒?
# $1, $2, $6, $7等數(shù)字分別表示vmstat輸出中的第幾列，
# 每個(gè)Unix系統(tǒng)中的vmstat輸出可能都不一樣，
# 所以修改這些列號(hào)，就可以應(yīng)對(duì)不同的操作系統(tǒng)。
#----------------------------------------
cat /tmp/msg$$sed 1,3d awk '{ printf("%s %s %s %s %s %s %s %s %s %s /n", $1, $2, $6, $7, $8, $9, $12, $20, $21, $22) }' while read RUNQUE WAITQUE SWAPIN SWAPOUT PAGE_IN PAGE_OUT PAGE_SCAN USER_CPU SYSTEM_CPU IDLE_CPU
do
$ORACLE_HOME/bin/sqlplus perfstat/perfstat <<EOF
insert into perfstat.stats/$vmstat
values (
SYSDATE,
$SAMPLE_TIME,
'$SERVER_NAME',
$RUNQUE,
$WAITQUE,
$SWAPIN,
$SWAPOUT,
$PAGE_IN,
$PAGE_OUT,
$PAGE_SCAN,
$USER_CPU,
$SYSTEM_CPU,
$IDLE_CPU,
0
);
commit;
EXIT
EOF
done
done
#----------------------------------------
#刪除臨時(shí)文件
#----------------------------------------
rm /tmp/msg$$
5. 創(chuàng)建run_vmstat.ksh腳本
該腳本放在crontab中，用來定時(shí)檢查get_vmstat.ksh腳本有沒有正常運(yùn)行，假如在運(yùn)行，那么不作任何動(dòng)作，假如不在運(yùn)行，那么就運(yùn)行g(shù)et_vmstat.ksh腳本。
這個(gè)腳本的意義在于防止重新啟動(dòng)操作系統(tǒng)之后忘記運(yùn)行g(shù)et_vmstat.ksh腳本。
# run_vmstat.ksh
#!/bin/ksh
#----------------------------------------
# 首先設(shè)定環(huán)境變量，根據(jù)實(shí)際環(huán)境修改. . . .
#----------------------------------------
SCRipT_PATH=`echo ~oracle/vmstat`
export SCRIPT_PATH
ORACLE_SID=kamusdb
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratabgrep $ORACLE_SID:cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
#----------------------------------------
# 作更進(jìn)一步地控制，在系統(tǒng)沒有操作的時(shí)間內(nèi)停止監(jiān)控
# 晚上8點(diǎn)到第二天凌晨8點(diǎn)之間停止監(jiān)控
# 其它時(shí)間假如get_vmstat不在運(yùn)行，就運(yùn)行它
#----------------------------------------
HOUR=`date +"%H"`
check_stat=`ps -efgrep get_vmstatgrep -v grepwc -l`
vmstat_num=`expr $check_stat`
if [ $HOUR -gt 19 ] [ $HOUR -lt 8 ]
then
if [ $vmstat_num -gt 0 ]
then kill -9 `ps -efgrep get_vmstatgrep -v grepawk '{print $2 }'` > /dev/null
fi
else
if [ $vmstat_num -le 0 ]
then nohup $SCRIPT_PATH/get_vmstat.ksh > /dev/null 2>&1 &
fi
fi
6. 創(chuàng)建crontab作業(yè)，定時(shí)執(zhí)行run_vmstat.ksh腳本
該作業(yè)每半小時(shí)運(yùn)行一次。
$ crontab –l > oracle.cron
$ echo ’00,30 * * * * /export/home/oracle/vmstat/run_vmstat.ksh >> /export/home/oracle/vmstat/run.lst 2>&1’ >> oracle.cron
$ crontab oracle.cron
7. 分析數(shù)據(jù)
至此已經(jīng)完成了定時(shí)運(yùn)行vmstat和在數(shù)據(jù)庫中存儲(chǔ)vmstat結(jié)果的步驟。自然，僅僅是搜集了統(tǒng)計(jì)信息是遠(yuǎn)遠(yuǎn)不夠的，下面我們要分析搜集來的信息，產(chǎn)生操作系統(tǒng)的性能報(bào)告。
將報(bào)告分為以下幾類，分別用腳本實(shí)現(xiàn)。
異常報(bào)告：顯示超過了門限值的時(shí)間段（vmstat_alert.ksh + vmstat_alert.sql）
每小時(shí)趨勢(shì)報(bào)告：顯示一天內(nèi)每小時(shí)的系統(tǒng)平均利用情況（rpt_vmstat_hr.sql）
周趨勢(shì)報(bào)告：顯示天天的系統(tǒng)平均利用情況（rpt_vmstat_dy.sql）
長期趨勢(shì)報(bào)告：顯示系統(tǒng)性能的一個(gè)長期趨勢(shì)線（rpt_vmstat.sql））
1) 異常報(bào)告
# vmstat_alert.sql
REM ----------------------------------------
REM 該SQL用于報(bào)告Oracle環(huán)境中每個(gè)服務(wù)器的異常情況。
REM 根據(jù)get_vmstat.ksh腳本得到的信息，報(bào)告每個(gè)小時(shí)的平均值。
REM DBA發(fā)現(xiàn)異常時(shí)間段，則可以深入檢查每5分鐘的具體數(shù)據(jù)。
REM 在這個(gè)腳本中接受一個(gè)參數(shù)，表示需要報(bào)告的時(shí)間跨度。
REM 對(duì)于runing queue的門限值應(yīng)該設(shè)置為CPU數(shù)，
REM 表示假如出現(xiàn)大量等待執(zhí)行的任務(wù)就報(bào)警，這通常表示CPU負(fù)載過重。
REM 對(duì)于page scan(sr)的門限值設(shè)置為1，
REM 表示只要出現(xiàn)page daemon掃描頁就報(bào)警，這通常表示內(nèi)存不足。
REM 對(duì)于CPU利用率，設(shè)置為70，表示超過70%以上的利用率才報(bào)警。
REM 比如運(yùn)行vmstat_alert 7
REM 表示輸出當(dāng)前日期之前7天之內(nèi)的執(zhí)行隊(duì)列大于4，
REM sr大于1，CPU利用率超過70%的按照小時(shí)統(tǒng)計(jì)的報(bào)告。
REM ----------------------------------------
set lines 80;
set pages 999;
set feedback off;
set verify off;
column my_date heading 'date hour' format a20
column c2 heading waitq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
ttitle 'run queue > CPUsMay indicate an overloaded CPUWhen runqueue exceeds the number of CPUs on the server, tasks are waiting for service.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
running_queue > 4
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
ttitle 'page_scan > 1May indicate overloaded memoryWhenever Unix performs a page-in, the RAM memory on the server has been exhausted and swap pages are being used.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
page_scan > 1
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
ttitle 'user+system CPU > 70%Indicates periods with a fully-loaded CPU subssystem.Periods of 100% utilization are only a concern when runqueue values exceeds the number of CPs on the server.';
select
server_name,
to_char(start_date,'YY/MM/DD HH24') my_date,
avg(running_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from
perfstat.stats$vmstat
WHERE
(user_cpu + system_cpu) > 70
and start_date > sysdate-&1
group by
server_name,
to_char(start_date,'YY/MM/DD HH24')
ORDER BY
server_name,
to_char(start_date,'YY/MM/DD HH24')
;
# vmstat_alert.ksh
#----------------------------------------
# 可以將此shell加入cron中，天天7點(diǎn)運(yùn)行
#----------------------------------------
#!
/bin/ksh
#----------------------------------------
# 首先設(shè)定環(huán)境變量，根據(jù)實(shí)際環(huán)境修改. . . .
# 接受一個(gè)參數(shù)輸入，表示當(dāng)前要報(bào)告的數(shù)據(jù)庫SID
#----------------------------------------
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratabgrep $ORACLE_SID:cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
SCRIPT_PATH=`echo ~oracle/vmstat`
export SCRIPT_PATH
sqlplus perfstat/perfstat<<!
spool /tmp/vmstat_$ORACLE_SID.lst
@$SCRIPT_PATH/vmstat_alert 7 4
spool off;
exit;
!
#----------------------------------------
# 檢查vmstat_alert.sql的輸出結(jié)果
# 正常情況應(yīng)該只包含下面2行
# SQL> @/export/home/oracle/vmstat/vmstat_alert 7
# SQL> spool off;
# 假如超過3行則表示有異常值，那么直接郵件給DBA
#----------------------------------------
check_stat=`cat /tmp/vmstat_$ORACLE_SID.lstwc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -gt 3 ]
then
cat /tmp/vmstat_$ORACLE_SID.lstmailx -s "System vmstat alert" kamus@itpub.net some_other_dba@mail.address.net
fi
1． 創(chuàng)建crontab作業(yè)，天天7點(diǎn)定時(shí)執(zhí)行vmstat_alert.ksh腳本
$ crontab –l > oracle.cron
$ echo ’00 7 * * * /export/home/oracle/vmstat/vmstat_alert.ksh kamusdb >> /export/home/oracle/vmstat/runalert.lst 2>&1’ >> oracle.cron
$ crontab oracle.cron
2) 每小時(shí)趨勢(shì)報(bào)告
# rpt_vmstat_hr.sql
REM ----------------------------------------
REM 該SQL用于報(bào)告Oracle環(huán)境中每個(gè)服務(wù)器一天內(nèi)小時(shí)平均的CPU使用率
REM 接受一個(gè)參數(shù)，用于指定需要報(bào)告的日期，格式為YYYYMMDD
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading 'server' format a10
column my_hour heading 'hour' format a10
column c2 heading runq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
select server_name,
to_char(start_date, 'HH24') my_hour,
avg(runing_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu + system_cpu) c5,
avg(system_cpu) c6,
avg(idle_cpu) c7
from stats$vmstat
where trunc(start_date) = to_date(&1,'yyyymmdd')
group BY server_name,to_char(start_date, 'HH24')
order by server_name,to_char(start_date, 'HH24');
3) 周趨勢(shì)報(bào)告
# rpt_vmstat_dy.sql
REM ----------------------------------------
REM 該SQL用于報(bào)告本周內(nèi)Oracle環(huán)境中每個(gè)服務(wù)器的日平均CPU使用率
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading 'server' format a10
column my_day heading 'day' format a20
column c2 heading runq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
select server_name,
to_char(start_date, 'day') my_day,
avg(runing_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu + system_cpu) c5,
avg(idle_cpu) c7
from stats$vmstat
where trunc(start_date) >= trunc(next_day(sysdate, 'MONDAY')) - 7
and trunc(start_date) < trunc(next_day(sysdate, 'MONDAY'))
group BY server_name, to_char(start_date, 'day')
order by server_name, to_char(start_date, 'day');
4) 長期趨勢(shì)報(bào)告
# rpt_vmstat.sql
REM ----------------------------------------
REM 該SQL用于報(bào)告Oracle環(huán)境中每個(gè)服務(wù)器日平均的CPU使用率
REM 報(bào)告范圍為已搜集的所有數(shù)據(jù)
REM ----------------------------------------
connect perfstat/perfstat;
set pages 9999;
set feedback off;
set verify off;
column server_name heading 'server' format a10
column my_date heading 'date' format a20
column c2 heading runq format 9999
column c3 heading pg_in format 9999
column c4 heading pg_ot format 9999
column c5 heading usr format 9999
column c6 heading sys format 9999
column c7 heading idl format 9999
column c8 heading wt format 9999
select server_name,
trunc(start_date) my_date,
avg(runing_queue) c2,
avg(kbytes_page_in) c3,
avg(kbytes_page_out) c4,
avg(user_cpu + system_cpu) c5,
avg(idle_cpu) c7
from stats$vmstat
group BY server_name, trunc(start_date)
order by server_name, trunc(start_date);
