每隔半小时检查表空间增长

每隔半小时检查表空间增长

 

输入文件  dbfmondblist 
输出文件  ${DATABASE}_dbfmon_statistics,TMP1,TMP2,TMP3,login,dbname,run_dt,compare_flag
输入参数： db_name,minutes
输出参数：  
流程序：3if 。2for 
$?  代表上一个命令执行是否成功的标志，如果执行成功则$? 为0，否则不为0


echo "\n"
date
. $HOME/utility/macro/macro.env

cd $SCRIPT_DIR

export LOGIN=oper/stat_4102

EMAIL_LST=$ADMIN_DIR/email.dba
#EMAIL_LST=$ADMIN_DIR/email_rpt.lst
lower_bound=2000000

export LOGFILE=$TMP_DIR/dbfmon.lst_`date '+%y%m%d'` 
rm -f $LOGFILE


grep -v '^#' $SCRIPT_DIR/dbfmondblist | while read dbname incr_percent max_threshold pct_free 
do
     export DB_CON_STR=${login}
     export DATABASE=${dbname}
     export TMP1=$TMP_DIR/chk_dbfmon.${DATABASE}_tmp1
     export TMP2=$TMP_DIR/chk_dbfmon.${DATABASE}_tmp2
     export TMP3=$TMP_DIR/chk_dbfmon.${DATABASE}_tmp3

$ORACLE_HOME/bin/sqlplus ${LOGIN} <<!
spool $TMP1
desc ${DATABASE}_dbfmon_statistics
spool off
exit
!
        grep -i "ORA-04043" ${TMP1}
        if [ $? -eq 0 ]; then
                # Build the last statistics table, then wait for next checking 
$ORACLE_HOME/bin/sqlplus ${LOGIN} <<!!
        create table ${DATABASE}_dbfmon_statistics
        as
        select trunc(sysdate,'MI') run_date, 
                b.tablespace_name,b.file_id,file_name,bytes,
                maxbytes,'N' compare_flag,
                b.free_bytes
        from dba_data_files@${DATABASE} a 
        , (select tablespace_name,file_id,sum(bytes) free_bytes 
                from dba_free_space@${DATABASE}
          group by tablespace_name,file_id) b
        where a.tablespace_name = b.tablespace_name(+) 
        and   a.file_id = b.file_id(+) 
        ;
        exit
!!
        rm ${TMP1}

        else
$ORACLE_HOME/bin/sqlplus ${LOGIN} <<!!!

variable run_dt varchar2(17);
 
begin
        select to_char(max(run_date), 'DD-MON-YYYY HH24:MI') 
        into :run_dt
        from ${DATABASE}_dbfmon_statistics
        where compare_flag = 'N'
        ;
end;
/

set echo off termout off pagesize 1000
spool ${TMP2}
select 'Last Run Time : '  || :run_dt
from dual;
spool off

col 
set linesize 120 
set pagesize 10000
set linesize 120
spool ${TMP1} 

col file_name format a50
col "Last_Bytes" format 9999999999
col "Current_Bytes" format 9999999999
col "Last_Bytes" format 9999999999
col "%chg" format 9999.99
col "%free" format 999.99

select
f1.tablespace_name, f1.file_name,
        round ((f3.free_bytes/f4.bytes) *100,2) "%free",
       f1.bytes "Last_Bytes",
        f2.bytes "Current_Bytes",
        f2.maxbytes "Max.Bytes",
        round(((f2.bytes - f1.bytes)/f1.bytes)*100,2)  "%chg"
from ${DATABASE}_dbfmon_statistics f1, 
     dba_data_files@${DATABASE} f2, 
        (select tablespace_name,
        sum(bytes) free_bytes
        from dba_free_space@${DATABASE}
        group by tablespace_name) f3,
        (select tablespace_name,
        sum(bytes) bytes
        from dba_data_files@${DATABASE}
        group by tablespace_name) f4
where f1.file_id = f2.file_id
and 
--(
 (((f2.bytes - f1.bytes)/f1.bytes)*100) > ${incr_percent} 
--or
--(f2.bytes/decode(f2.maxbytes,0,2097152000,f2.maxbytes)*100) 
-- > ${max_threshold}
--)
and f1.tablespace_name = f4.tablespace_name
and f1.tablespace_name = f3.tablespace_name
--and (f3.free_bytes/f4.bytes) *100 <= ${pct_free} 
and f1.tablespace_name not in ('RBS','TEMP')
and f1.compare_flag = 'N' 
and f1.run_date = to_date(:run_dt,'DD-MON-YYYY HH24:MI') 
and f1.tablespace_name = f2.tablespace_name
;
spool off

update ${DATABASE}_dbfmon_statistics
set compare_flag = 'Y'
where run_date = to_date(:run_dt,'DD-MON-YYYY HH24:MI') 
;

insert into ${DATABASE}_dbfmon_statistics
select trunc(sysdate,'MI') run_date, 
a.tablespace_name,a.file_id,file_name,bytes,maxbytes,'N'
, b.free_bytes
from dba_data_files@${DATABASE} a 
, (select tablespace_name,file_id,sum(bytes) free_bytes 
from dba_free_space@${DATABASE}
 group by tablespace_name,file_id) b
where a.tablespace_name = b.tablespace_Name (+) 
and a.file_id = b.file_id(+) 
/
exit
!!!

    grep -i "^Last RUN Time :" ${TMP2} > $TMP3 

    grep -i "no rows selected" ${TMP1}
    if [ $? -ne 0 ]; then

        echo "\nDatabase : "${DATABASE} >> $LOGFILE
        echo "Threshold : >"${incr_percent}"% Growth" >> $LOGFILE
        echo "Current Size / Max. Bytes : <="${max_threshold}"%" >> $LOGFILE
        cat ${TMP3} >> $LOGFILE
        echo "Current RUN Time :"`date '+%d-%b-%y'` >> $LOGFILE

        lno=`grep -n ";" ${TMP1} | cut -d":" -f 1 | more`
        sed -e "1,${lno}d" ${TMP1} >> $LOGFILE
     fi
#     rm -f $TMP1 $TMP2 $TMP3 
fi
done

if [ -f $LOGFILE ]; then
        echo "Tablespace Growth Monitoring " > $TMP1 
        cat $LOGFILE >> $TMP1
        mv $TMP1 $LOGFILE
        echo "Information: Tablespace size is " ${max_threshold} "% of its maxsize" >> $LOGFILE
        echo "Impact: No Immediate Impact to Users" >> $LOGFILE 
        echo "Action: Please inform DBA to perform checking," >> $LOGFILE
        echo "and make sure the above tablespace(s) not reaching its maxsize" >> $LOGFILE
        for iname in `cat $EMAIL_LST` 
        do
        /bin/mailx -s "Tablespace Growth Monitoring " $iname < $LOGFILE 
        done
else

      echo "No Problem found. " > $TMP1 
      for iname in `cat $EMAIL_LST`
       do
       /bin/mailx -s "Tablespace Growth Monitoring " $iname < $TMP1 
       done
fi


rm -f $TMP1 $TMP2 $TMP3