背景:要做个业务的巡检,其实就是shell脚本和oracle数据库的结合了,把查到的数据生成个txt文本,然后发短信出来,话不多说,直接上脚本了(这里面有个暗坑要注意下,就是 db_link 数据源的这个xunjian_sql 中,那些sql 不要用分号; 不然会打出两份重复的结果来,千万别用)
#!/bin/bash
#. /etc/profile
. ~/.bash_profile
set serveroutput on
set timing on
dblink=”dzfp/Dzswjdz!1116@151.12.79.106:1521/dzswjcx”
#通过关联生成数据的结束时间判断生成数据的条数
xunjian_sql=”select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)||’ 30分钟内数据同步情况如下:1票’||(select count(*) from dzfp.dzdz_fpxx_zzsfp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录,2票’||(select count(*) from dzfp.dzdz_fpxx_ptfp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录,3票’|| (select count(*) from dzfp.dzdz_fpxx_dzfp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录,4票’|| (select count(*) from dzfp.dzdz_fpxx_dzzp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录,5票’|| (select count(*) from dzfp.dzdz_fpxx_jsfp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录,6票’|| (select count(*) from dzfp.dzdz_fpxx_jdcfp where jssj > sysdate – (30/1440) and KPYF = to_char(trunc(sysdate),’yyyymm’))||’笔记录。’ from dual”
MSG=`/orahome/app/oracle/product/11.2.0/oracle/bin/sqlplus -silent ${dblink}
set pagesize 0 feedback off verify off heading off echo off
${xunjian_sql}
/
exit;
EOF`
echo $MSG > /home/oracle/logstail/quandian_db_xunjian/logs/quandian_db_xunjian.txt
##生成短信需要的格式内容
content=`cat /home/oracle/logstail/quandian_db_xunjian/logs/quandian_db_xunjian.txt`
in=`echo $content | tr -d ‘n’ | xxd -plain | sed ‘s/(..)/%1/g’ `
echo $in > ./tmp
in=`cat ./tmp|tr -d ‘ ‘`
##批量发送短信
echo “http://151.12.71.205:80/mondzswj/index.php?c=operate&a=testsendmsg&msg=$in”
curl “http://151.12.71.205:80/mondzswj/index.php?c=operate&a=testsendmsg&msg=$in”
服务器租用托管,机房租用托管,主机租用托管,https://www.e1idc.com