用Oracle存储来分析Unix系统的性能

# 检查vmstat_alert.sql的输出结果

# 正常情况应该只包含下面2行

# SQL> @/export/home/oracle/vmstat/vmstat_alert 7

# SQL> spool off;

# 如果超过3行则表示有异常值，那么直接邮件给DBA

#----------------------------------------

check_stat=`cat /tmp/vmstat_$ORACLE_SID.lst|wc -l`;

oracle_num=`expr $check_stat`

if [ $oracle_num -gt 3 ]

then

cat /tmp/vmstat_$ORACLE_SID.lst|mailx -s
"System vmstat alert" kamus@itpub.net
some_other_dba@mail.address.net

fi

1． 创建crontab作业，每天7点定时执行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) 每小时趋势报告

# rpt_vmstat_hr.sql

REM ----------------------------------------

REM 该SQL用于报告Oracle环境中每个服务器一天内小时平均的CPU使用率

REM 接受一个参数，用于指定需要报告的日期，格式为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) 周趋势报告

# rpt_vmstat_dy.sql

REM ----------------------------------------

REM 该SQL用于报告本周内Oracle环境中每个服务器的日平均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');

好了，有了这些报告。我们就只需要编写一些脚本就可以实现自动的后台监控，当有问题的时候再自动发送邮件给DBA。

有关操作系统的更多文章请进：技术无忧