这是抄的一个DBA的脚本，在服务器上用shell 将oracle数据库的数据查到，包括查询141主库，和142备库，然后用ftp上传到外网的一个服务器上进行展示，脚本有些长，直接用nodepad++ 打开即可
#!/bin/ksh
time=date +'%Y%m%d%T'
. ~/.bash_profile
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select '空闲空间：'||round(free_mb/1024)||'G，使用率：'||trunc((total_mb-free_mb)/total_mb*100)||'%' from v\\\$asm_diskgroup where name='FRA'; exit; END
echo "电子税务局数据库巡检时间${time}：${VALUE}"> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon session cnt
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select '会话数 '||listagg(info, ',') WITHIN GROUP(ORDER BY info) names from (select '节点' || inst_id || ':' || count(*) info from gv\\\$session group by inst_id); exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon wait cnt
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select /*+rule*/'活动会话数:'||count(*) from gv\\\$session where type='USER' and wait_class<>'Idle' union all select 'CPU使用率 '||listagg(info, ',') WITHIN GROUP(ORDER BY info) names from ( select '节点'||inst_id||': '||round(sum(value)/2,1)||'%' info from gv\\\$sysmetric where metric_name in ('Host CPU Utilization (%)') group by inst_id ); exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon tablespace
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select '表空间使用率超过75%为:'||listagg(used_percent,',') WITHIN GROUP(ORDER BY used_percent) names from (select a.tablespace_name||' '|| round(replace((a.sz-b.sz)/a.sz*100,',','.'),2)||'%' used_percent from (select tablespace_name,sum(bytes)/1048576 sz from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes)/1048576 sz from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name(+) and round(replace((a.sz-b.sz)/a.sz*100,',','.'),2)>=75); exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon lock cnt
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select /*+rule*/'数据库锁数量:'||count(*) from gv\\\$lock where request<>0; exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#disk=df -mP /home|column -t |sed 1d | awk '{print "disk:"$5}'|tr -d %
#echo "，141磁盘空间：/home"${disk}>>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#disk=df -mP /|column -t |sed 1d | awk '{print "disk:"$5}'|tr -d %
#echo "，/："${disk}>>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon memory 141
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
memused=free -m |grep buffers\/ |awk -F ':' '{print $2}' |awk '{print $1}'
available=free -m |grep buffers\/ |awk -F ':' '{print $2}' |awk '{print $2}'
memtotal=free -m |grep Mem |awk '{print $2}'
memrate=expr $memused \* 100 / $memtotal
echo " 服务器141内存使用率:" >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
echo ${memrate}%, >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon memory 142
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
memused=ssh 87.12.74.142 free -m |grep buffers\/ |awk -F ':' '{print $2}' |awk '{print $1}'
available=ssh 87.12.74.142 free -m |grep buffers\/ |awk -F ':' '{print $2}' |awk '{print $2}'
memtotal=ssh 87.12.74.142 free -m |grep Mem |awk '{print $2}'
memrate=expr $memused \* 100 / $memtotal
echo " 服务器142内存使用率:" >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
echo ${memrate}% >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#mon disk
spvalue=70
SPACE=df -hP 2>/dev/null | awk 'int($5)>'$spvalue' {print $6,$5}' | wc -l
if [ $SPACE -gt 0 ]
then
echo 141 文件系统使用率: >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
df -hP 2>/dev/null | awk 'int($5)>'$spvalue' {print $6,$5}' >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
fi
SPACE=ssh gxgs-xwssb-nwyw-db2 df -hP 2>/dev/null | awk 'int($5)>'$spvalue' {print $6,$5}' | wc -l
if [ $SPACE -gt 0 ]
then
echo 142 文件系统使用率: >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
ssh gxgs-xwssb-nwyw-db2 df -hP 2>/dev/null | awk 'int($5)>'$spvalue' {print $6,$5}' >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
fi
#weblogic thread count
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select 'weblogic独占线程超过30的数量:'||count(1) from hlwsb_sjfx.dzswj_monitor_weblogic_thread t where t.mon_date>sysdate- (30/1440) and t.hoggingthreadcount>30; exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
#weblogic thread mingxi
echo >>/home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
VALUE=sqlplus -silent "/ as sysdba" << END set pagesize 0 feedback off verify off heading off echo off select 'weblogic独占线程超过30的服务明细:'|| to_char(wmsys.wm_concat(t.serverport)) from hlwsb_sjfx.dzswj_monitor_weblogic_thread t where t.mon_date>sysdate- (30/1440) and t.hoggingthreadcount>30 group by t.serverport; exit; END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/fra_mon_${time}.log
USER=weblogic #密码 PASSWORD=Hlwsb101#% #下载文件目录1 SRCDIR=/home/weblogic/logstail/oracle_monitor/logs/ #FTP目录(待下载文件目录) DESDIR=/home/oracle/monitor/mon_space/logs/ IP=87.16.16.218 PORT=3522
lftp -u ${USER},${PASSWORD} sftp://${IP}:${PORT}<<EOF lcd ${DESDIR} cd ${SRCDIR} mput mon by EOF
rm -f /home/oracle/monitor/mon_space/logs/*
