shell脚本:数据库业务监控

#!/bin/bash

#先写一个简单的脚本，后续使用脚本调用存储过程

#by：亚信-张颜

export ORACLE_HOME=/opt/oracle/app/oracle_base/product/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

#DATE_STR保存了从数据库中返回的三个值：1小时之前的年月，12小时前的时间串，以及当前时间串

DATE_STR=(`sqlplus -s 用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

select to_char(TRUNC(SYSDATE - 1 / 24, 'HH'), 'YYYYMM'),TO_CHAR(TRUNC(SYSDATE - 1 / 2, 'HH'), 'YYYYMMDDHH24MISS'),

TO_CHAR(TRUNC(SYSDATE, 'HH'), 'YYYYMMDDHH24MISS') from dual;

exit;

eof`)

echo "`date` 开始校验程控退订数据......"

echo "`date` 开始提取程控功能营业送开通表退订数据"

#通过监控营业送开通工单历史表，查询程控功能退订的数据，当前设定为12个小时运行1次

#

for REGION_ID in {891..897}

do

sqlplus -s 用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

insert into zyan_jiankongchengkong

select bill_id, 'MBELL', create_date, sysdate

from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]}

where action_id = 5

and OLD_PS_PARAM like '%MBELL=1%'

and (PS_PARAM is null or PS_PARAM not like '%MBELL=1%')

and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS')

and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

insert into zyan_jiankongchengkong

select bill_id, 'CFNRCF', create_date, sysdate

from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]}

where action_id = 5

and OLD_PS_PARAM like '%CFNRCF=1%'

and (PS_PARAM is null or PS_PARAM not like '%CFNRCF=1%')

and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS')

and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

exit;

eof

done

#校验程控功能订购在退订功能时是否存在订购关系，如果存在则表示是异常，需要进一步核查:

echo "`date` 开始校验程控退订记录营业侧订购关系:"

for REGION_ID in {891..897}

do

sqlplus -s 用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

delete from zyan_jiankongchengkong a

where create_date <> (select max(create_date)

from zyan_jiankongchengkong b

where a.bill_id = b.bill_id

and a.func_name = b.func_name)

and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

delete from zyan_jiankongchengkong a

where not exists (select 1

from so.ins_user_${REGION_ID} b,

so.ins_prod_${REGION_ID} c,

so.ins_off_ins_user_${REGION_ID} f

where a.bill_id = b.bill_id

and c.prod_id in (121030512001, 121030512002)

and b.user_id = c.user_id

and c.user_id = f.user_id

and c.offer_inst_id = f.offer_inst_id

and b.region_id=f.region_id

and f.expire_date > sysdate

and c.expire_date > sysdate

and c.effective_date < a.create_date

and c.create_date

and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id)

and a.func_name = 'MBELL'

and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

delete from zyan_jiankongchengkong a

where not exists (select 1

from so.ins_user_${REGION_ID} b,

so.ins_prod_${REGION_ID} c,

so.ins_off_ins_user_${REGION_ID} f

where a.bill_id = b.bill_id

and c.prod_id in (121030505101)

and b.user_id = c.user_id

and c.user_id = f.user_id

and c.offer_inst_id = f.offer_inst_id

and b.region_id=f.region_id

and f.expire_date > sysdate

and c.expire_date > sysdate

and c.effective_date < a.create_date

and c.create_date

and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id)

and a.func_name = 'CFNRCF'

and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

exit;

eof

done

echo "`date` 本次校验完成！"

echo ""