今天接到一个需求,希望将服务器的日常巡检指标给存储在数据库中,这样可以方便查询历史记录以及巡检报表的输出。这里我打算使用外部表来实现,以下简要记录实现过程:
1.编写巡检脚本
#!/bin/bash
echo `/sbin/ifconfig|grep "inet addr:10.10"|cut -f 2 -d ":"|cut -f 1 -d " "`,`date '+%m/%d %H:%M'`,`uptime|sed -r 's/(.*)(average: )(.*)/\3/'|sed 's/ //g'`,`vmstat -S m 3 1|tail -1|awk '{print $1","$2","$4","$7","$8","$13","$14","$15","$16}'` >>/LOG_REPORT/sysstat.txt
说明一下每部分命令代表的含义
#获取服务器IP
/sbin/ifconfig|grep "inet addr:10.10"|cut -f 2 -d ":"|cut -f 1 -d " "
#时间
date '+%F %T'
#CPU负载(1,5,15分钟负载)
uptime|sed -r 's/(.*)(average: )(.*)/\3/'|sed 's/ //g'
#进程队列
vmstat -S m 3 1|tail -1|awk '{print $1","$2}'
#可用内存
vmstat -S m 3 1|tail -1|awk '{print $4}'
#swap换页
vmstat -S m 3 1|tail -1|awk '{print $7","$8}'
#CPU使用率
vmstat -S m 3 1|tail -1|awk '{print $13","$14","$15","$16}'
2.在数据库中创建监控用户
sqlplus / as sysdba
create user monitor identified by oracle;
grant dba to monitor;
3.创建外部表
CREATE TABLE sysstat
(
ip varchar2(40),
create_time varchar2(40),
load_1 number(5,2),
load_5 number(5,2),
load_15 number(5,2),
run_p number(5),
block_p number(5),
freemem number(10),
swap_in number(5),
swap_out number(5),
cpu_usr number(3),
cpu_sys number(3),
cpu_idle number(3),
cpu_wait number(3)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY log_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile log_dir:'sysstat%a_%p.bad'
logfile log_dir:'sysstat%a_%p.log'
fields terminated by ','
missing field values are null
( ip,create_time,
load_1, load_5, load_15,run_p,block_p,
freemem, swap_in, swap_out, cpu_usr, cpu_sys, cpu_idle, cpu_wait
)
)
LOCATION ('sysstat.txt')
)
REJECT LIMIT UNLIMITED;
4.展示一下查询结果
select * from sysstat order by 1,2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20801486/viewspace-1161995/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20801486/viewspace-1161995/