oracle数据库常用的监控脚本分享

1:检查alter日志文件，输出常见的报错信息

#!/bin/bash
source /home/oracle/.bash_profile
ORACLE_SID=orcldb ##sid
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ##oracle home
export ORACLE_HOME
HOST_NAME=`uname -n`
export HOST_NAME
JOB_HOME=$HOME/jobs
export JOB_HOME
DUMP_DEST=/u01/app/oracle/diag/rdbms/btmesdb/"$ORACLE_SID"/trace
export DUMP_DEST
IPADDR=`ifconfig |grep "inet"|awk -F: 'NR==1{print substr($1,13,13)}'` ##不通版本os 这里可能需要修改
export IPADDR
LOG_NAME="alert_$ORACLE_SID.log"
CHECK_LINES=50
ERR_MSG="ORA-|Failure|alter|unusable|shutdown|startup"
ERR_EXC="ORA-02068|ORA-03113|ORA-02050|ORA-12012|ORA-06512|ORA-12541|ORA-12005|ORA-03113|ORA-02396|ORA-02063|ORA-01012|ORA-000060|ORA-02054|ORA-12535|ORA-02053|ORA-01013|ORA-3136|ORA-03135"
OLD_LOG_1=`echo "old_1_$ORACLE_SID.log"`
OLD_LOG_2=`echo "old_2_$ORACLE_SID.log"`
export LOG_NAME CHECK_LINES ERR_MSG OLD_LOG_1 OLD_LOG_2 ERR_EXC
tail -$CHECK_LINES $DUMP_DEST/$LOG_NAME|egrep -i $ERR_MSG|egrep -v $ERR_EXC|egrep -v "ALTER DATABASE BACKUP CONTROLFILE"|egrep -v "ALTER SYSTEM ARCHIVE LOG" > /tmp/new.log
num=`cat /tmp/new.log |wc -l`
if [ $num -gt 0 ]
then
if [ ! -f /tmp/$OLD_LOG_1 ]
then
touch /tmp/$OLD_LOG_1
fi
if [ ! -f /tmp/$OLD_LOG_2 ]
then
touch /tmp/$OLD_LOG_2
fi
if [ `cat /tmp/$OLD_LOG_2|wc -l` -eq 0 ]
then
var=$num
else
cmp -s /tmp/new.log /tmp/$OLD_LOG_2
var=$?
fi
if [ $var -ge 1 ]
then
# echo "**********************************************************************"
# echo "There are alerts"
tail -$CHECK_LINES $DUMP_DEST/$LOG_NAME|mail -s "$IPADDR btmes_81 Check Alert Log" `cat $JOB_HOME/dba_mail.addr|grep -v \#` ##dba_mail.addr存放邮件地址
echo "**********************************************************************"
cp /tmp/$OLD_LOG_1 /tmp/$OLD_LOG_2
cp /tmp/new.log /tmp/$OLD_LOG_1
fi
else
>/tmp/$OLD_LOG_1
>/tmp/$OLD_LOG_2
fi
shif

2:监控数据库实例状态,异常的时候mail告警

#!/bin/bash
source /home/oracle/.bash_profile
HOST_NAME=`hostname`
#IPADDR=`ifconfig |grep "inet addr"|awk -F: 'NR==1{print substr($2,1,14)}'` ##不同版本os 这里需要调整
ORACLE_SID=orcldb ##oracle sid
JOB_PWD=$HOME/jobs
export JOB_PWD
vin=`ps -ef|grep pmon|grep $ORACLE_SID|wc -l`
if [ $vin -eq 0 ]
then
echo " $ORACLE_SID Instance down"|mailx -s "$ORACLE_SID instance down" `cat $JOB_PWD/dba_mail.addr|grep -v \#`
fi

3:表空间mail告警

#!/bin/bash
  rm -f /tmp/check_ts_usage.html
  ORACLE_SID=orcl  ##oracle sid
  export ORACLE_SID
  ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1  ##oracle home
  export ORACLE_HOME
  HOST_NAME=`uname -n`
  export HOST_NAME
  JOB_PWD=$HOME/jobs
  export JOB_PWD
  $ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<!
    @$JOB_PWD/check_ts_usage 
    exit
!
  var=`cat /tmp/check_ts_usage.html|wc -l`


  if [[ $var -gt 11 ]];
  then
    # echo "**********************************************************************"
    # echo "There are alerts"
    echo "Check Tablespace LWM Alert "|mailx -s "dbname Check Tablespace LWM Alert"  -a  /tmp/check_ts_usage.html `cat $JOB_PWD/dba_mail.addr|grep -v \#` 
    # echo "**********************************************************************"
    # exit
  fi
  shift
done


check_ts_usage.sql 如下：
SET markup html ON spool ON entmap OFF


SET echo OFF
SET verify OFF
SET feedback OFF
SET termout OFF
SET pagesize 25
SET appinfo 'check_ts_usage.sql' 
COLUMN hostname         format a10    heading "HOSTNAME"
COLUMN instance_name    format a13    heading "INSTANCE_NAME"
COLUMN tablespace_name  format a15    heading "TABLESPACE_NAME"
COLUMN free_size        format 99,999 heading  "FREE_SIZE"
COLUMN work_time        format a13    heading "WORK_TIME"
spool /tmp/check_ts_usage.html


ttitle center "List the tablespace that fall into low water mark"  skip 2
 
SELECT t.*
  FROM (SELECT a.tablespace_name
              ,a.unalloc_size
              ,nvl(f.free_size, 0) free_size
              ,a.used_size - nvl(f.free_size, 0) used_size
              ,round((a.used_size - nvl(f.free_size, 0)) /
                     (a.unalloc_size + a.used_size)
                    ,2) capacity
          FROM (SELECT tablespace_name
                      ,round(SUM(bytes) / 1024 / 1024) free_size
                  FROM dba_free_space
                 GROUP BY tablespace_name) f
              ,(SELECT tablespace_name
                      ,round(SUM(user_bytes) / 1024 / 1024) used_size
                      ,round(SUM(decode(autoextensible
                                       ,'YES'
                                       ,decode(sign(maxbytes - user_bytes)
                                              ,-1
                                              ,0
                                              ,maxbytes - user_bytes)
                                       ,0)) / 1024 / 1024) unalloc_size
                  FROM dba_data_files
                 GROUP BY tablespace_name) a
         WHERE 1 = 1
           AND a.tablespace_name = f.tablespace_name(+)) t
 WHERE  capacity >= 0.85    ##阈值根据需要调整
   AND (unalloc_size + free_size) < 4000
   AND (unalloc_size + free_size) < used_size / 2
 ORDER BY capacity DESC;


SET markup html OFF
spool OFF
SET pagesize 14
ttitle OFF
SET feedback ON
SET termout ON
SET appinfo OFF
SET echo ON




EXIT