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  用戶名/密碼 <<eof

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  用戶名/密碼 <<eof

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  用戶名/密碼 <<eof

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<a.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<a.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 ""