linux oracle 上如何用shell脚本来将oracle的各项性能数据查询出来,并上传

2021/02/08 11:48
阅读数 102

这是抄的一个DBA的脚本,在服务器上用shell 将oracle数据库的数据查到,包括查询141主库,和142备库,然后用ftp上传到外网的一个服务器上进行展示,脚本有些长,直接用nodepad++ 打开即可

#!/bin/ksh
time=date +'%Y%m%d%T'
. ~/.bash_profile
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END <br/>set pagesize 0 feedback off verify off heading off echo off <br/>select '空闲空间:'||round(free_mb/1024)||'G,使用率:'||trunc((total_mb-free_mb)/total_mb*100)||'%' from v\\\$asm_diskgroup where name='FRA';<br/>exit; <br/>END
echo "电子税务局数据库巡检时间${time}:${VALUE}"> /home/oracle/monitor/mon_space/logs/framon${time}.log



#mon session cnt
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select '会话数 '||listagg(info, ',') WITHIN GROUP(ORDER BY info) names<br/>from (select '节点' || inst_id || ':' || count(*) info<br/>from gv\\\$session<br/>group by inst_id);<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${time}.log


#mon wait cnt
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select /*+rule*/'活动会话数:'||count(*) from gv\\\$session where type='USER' and wait_class&lt;&gt;'Idle'<br/>union all<br/>select 'CPU使用率 '||listagg(info, ',') WITHIN GROUP(ORDER BY info) names<br/>from (<br/>select '节点'||inst_id||': '||round(sum(value)/2,1)||'%' info from gv\\\$sysmetric where metric_name in ('Host CPU Utilization (%)') group by inst_id<br/>);<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${time}.log


#mon tablespace
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select '表空间使用率超过75%为:'||listagg(used_percent,',') WITHIN GROUP(ORDER BY used_percent) names<br/>from<br/>(select a.tablespace_name||' '||<br/>round(replace((a.sz-b.sz)/a.sz*100,',','.'),2)||'%' used_percent<br/>from (select tablespace_name,sum(bytes)/1048576 sz<br/>from dba_data_files group by tablespace_name) a,<br/>(select tablespace_name,sum(bytes)/1048576 sz<br/>from dba_free_space group by tablespace_name) b<br/>where a.tablespace_name=b.tablespace_name(+) <br/>and round(replace((a.sz-b.sz)/a.sz*100,',','.'),2)&gt;=75);<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${time}.log


#mon lock cnt
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select /*+rule*/'数据库锁数量:'||count(*) from gv\\\$lock where request&lt;&gt;0;<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${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/framon${time}.log
#disk=df -mP /|column -t |sed 1d | awk '{print "disk:"$5}'|tr -d %
#echo ",/:"${disk}>>/home/oracle/monitor/mon_space/logs/framon${time}.log


#mon memory 141
echo >>/home/oracle/monitor/mon_space/logs/framon${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/framon${time}.log
echo ${memrate}%, >>/home/oracle/monitor/mon_space/logs/framon${time}.log






#mon memory 142
echo >>/home/oracle/monitor/mon_space/logs/framon${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/framon${time}.log
echo ${memrate}% >>/home/oracle/monitor/mon_space/logs/framon${time}.log






#mon disk
spvalue=70
SPACE=df -hP 2&gt;/dev/null | awk 'int($5)&gt;'$spvalue' {print $6,$5}' | wc -l
if [ $SPACE -gt 0 ]
then
echo 141 文件系统使用率: >>/home/oracle/monitor/mon_space/logs/framon${time}.log
df -hP 2>/dev/null | awk 'int($5)>'$spvalue' {print $6,$5}' >>/home/oracle/monitor/mon_space/logs/framon${time}.log
fi






SPACE=ssh gxgs-xwssb-nwyw-db2 df -hP 2&gt;/dev/null | awk 'int($5)&gt;'$spvalue' {print $6,$5}' | wc -l
if [ $SPACE -gt 0 ]
then
echo 142 文件系统使用率: >>/home/oracle/monitor/mon_space/logs/framon${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/framon${time}.log
fi




#weblogic thread count
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select 'weblogic独占线程超过30的数量:'||count(1) from hlwsb_sjfx.dzswj_monitor_weblogic_thread t where t.mon_date&gt;sysdate- (30/1440) and t.hoggingthreadcount&gt;30;<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${time}.log


#weblogic thread mingxi
echo >>/home/oracle/monitor/mon_space/logs/framon${time}.log
VALUE=sqlplus -silent "/ as sysdba" &lt;&lt; END<br/>set pagesize 0 feedback off verify off heading off echo off<br/>select 'weblogic独占线程超过30的服务明细:'|| to_char(wmsys.wm_concat(t.serverport)) from hlwsb_sjfx.dzswj_monitor_weblogic_thread t where t.mon_date&gt;sysdate- (30/1440) and t.hoggingthreadcount&gt;30<br/>group by t.serverport;<br/>exit;<br/>END
echo "${VALUE}">> /home/oracle/monitor/mon_space/logs/framon${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/*

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部