Linux/Unix shell 监控Oracle告警日志(monitor alter log file)

    使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份,AWR report的自动邮件等。本文给出Linux 下使用 shell 脚本来监控 Oracle 告警日志(monitor alter log file)。

    Linux Shell的相关参考: Linux/Unix shell 脚本中调用SQL,RMAN脚本 Linux/Unix shell sql 之间传递变量 Linux/Unix shell 调用 PL/SQL Linux/Unix shell 监控Oracle实例(monitor instance) Linux/Unix shell 监控Oracle监听器(monitor listener)

1、监控Oracle告警日志脚本

robin@SZDB:~/dba_scripts/custom/bin> more ck_alert.sh
#!/bin/bash
# --------------------------------------------------------------------------+
#                  CHECK ALERT LOG FILE                                     |
#   Filename: ck_alert.sh                                                   |
#   Desc:                                                                   |
#       The script use to check alert log file.                             |
#       Once any error was caught, a mail alert will be sent.               |   
#       Deploy it by crontab. e.g. per 15 min                               |  
#   Usage:                                                                  |
#       ./ck_alert.sh $ORACLE_SID                                           |  
#                                                                           |
#   Author : Robinson                                                       | 
#   Blog   : http://blog.csdn.net/robinson_0612                             |
# --------------------------------------------------------------------------+
#
# --------------------------
#   Check SID
# --------------------------

if [ -z "${1}" ];then
    echo "Usage: "
    echo "      `basename $0` ORACLE_SID"
    exit 1
fi

# -------------------------------
#  Set environment here 
# ------------------------------

if [ -f ~/.bash_profile ]; then
    . ~/.bash_profile
fi

export ORACLE_SID=$1
export MACHINE=`hostname`
export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56
export MAIL_LIST='Robinson.cheng@12306.com'
export MAIL_FM='oracle@szdb.com'

# ----------------------------------------------
# check the database is running, if not exit
# ----------------------------------------------

db_stat=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep| cut -f3 -d_`
if [ -z "$db_stat" ]; then
    date >/tmp/db_${ORACLE_SID}_stauts.log
    echo " $ORACLE_SID is not available on ${MACHINE} !!!" >>/tmp/db_${ORACLE_SID}_stauts.log 
    MAIL_SUB=" $ORACLE_SID is not available on ${MACHINE} !!!"
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -o message-file=/tmp/db_${ORACLE_SID}_stauts.log
    exit 1
fi;

# --------------------------------------
#  Get the location of alert log file
# --------------------------------------

sqlplus '/ as sysdba' << EOF > /tmp/${ORACLE_SID}_monitor_temp.txt
column xxxx format a10
column value format a80
set lines 132
SELECT 'xxxx' ,value FROM  v\$parameter WHERE  name = 'background_dump_dest'
/
exit
EOF

cat /tmp/${ORACLE_SID}_monitor_temp.txt | awk '$1 ~ /xxxx/ {print $2}' > /tmp/${ORACLE_SID}_monitor_location.txt
read ALERT_DIR < /tmp/${ORACLE_SID}_monitor_location.txt
rm /tmp/${ORACLE_SID}_monitor_temp.txt 2>/dev/null

# ----------------------------------------
#  Define archive directory and log file
# ----------------------------------------

DT=`date +%Y%m%d`
DT_DIR=`date +%Y%m`
ARCH_DIR=${ALERT_DIR}/${DT_DIR}

if [ ! -d "${ARCH_DIR}" ] ; then
    mkdir $ARCH_DIR
fi

ORIG_ALERT_LOG=${ALERT_DIR}/alert_${ORACLE_SID}.log
NEW_ALERT_LOG=${ARCH_DIR}/alert_${ORACLE_SID}.log.${DT}
TEMP_ALERT_LOG=${ORIG_ALERT_LOG}.temp
AWK_DIR=/users/robin/dba_scripts/custom/bin

# -------------------------------------
#  Check alert log file and send email
# -------------------------------------
cat ${ORIG_ALERT_LOG} | awk -f $AWK_DIR/check_alert.awk > /tmp/${ORACLE_SID}_check_monitor_log.log
if [ -s "/tmp/${ORACLE_SID}_check_monitor_log.log" ];
   then 
     echo "Found errors in sid ${ORACLE_SID}, mailed errors"
     echo -e "The following errors were found in the alert log for ${ORACLE_SID} \n" > /tmp/${ORACLE_SID}_check_monitor_log.mail
     echo -e "Alert log was copied into ${NEW_ALERT_LOG} \n">> /tmp/${ORACLE_SID}_check_monitor_log.mail
     date >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo "--------------------------------------------------------------">>/tmp/${ORACLE_SID}_check_monitor_log.mail
     echo " "
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     cat /tmp/${ORACLE_SID}_check_monitor_log.log >>  /tmp/${ORACLE_SID}_check_monitor_log.mail
     MAIL_SUB="Found errors in ${ORACLE_SID} on ${MACHINE}"
     $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -o message-file=/tmp/${ORACLE_SID}_check_monitor_log.mail

# --------------------------------
#  Backup current alert log file
# --------------------------------
    mv ${ORIG_ALERT_LOG} ${TEMP_ALERT_LOG}
    cat ${TEMP_ALERT_LOG} >> ${NEW_ALERT_LOG}
    #touch ${ORIG_ALERT_LOG}
    cat /dev/null > ${ORIG_ALERT_LOG}
       rm /tmp/${ORACLE_SID}_check_monitor_log.log 
       rm /tmp/${ORACLE_SID}_check_monitor_log.mail
       rm ${TEMP_ALERT_LOG} > /dev/null 
    exit
fi

rm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/null
rm /tmp/${ORACLE_SID}_monitor_location.txt > /dev/null

exit

2、过滤Oracle告警日志错误信息

robin@SZDB:~/dba_scripts/custom/bin> more check_alert.awk
$0 ~ /Errors in file/ {print $0}
$0 ~ /PMON: terminating instance due to error 600/ {print $0}
$0 ~ /Started recovery/{print $0}
$0 ~ /Archival required/{print $0}
$0 ~ /Instance terminated/ {print $0}
$0 ~ /Checkpoint not complete/ {print $0}
$1 ~ /ORA-/ { print $0; flag=1 }
$0 !~ /ORA-/ {if (flag==1){print $0; flag=0;print " "} }
$0 ~ /ERROR_AUDIT/ {print $0}

3、老化Oracle告警日志脚本

robin@SZDB:~/dba_scripts/custom/bin> more age_alert.sh
#!/bin/bash
# ------------------------------------------------------------+
#                 Age the alert log file                      |
#   FileName: age_alert.sh                                    |
#   Desc:                                                     | 
#        The script use to age the alert log file             |
#   Usage:                                                    |
#        ./age_alert.sh $ORACLE_SID                           |
#                                                             |
#   Authror : Robinson                                        |
#   Blog    : http://blog.csdn.net/robinson_0612              |
# ------------------------------------------------------------+

# --------------------------
#   Check SID
# --------------------------

if [ -z "${1}" ];then
    echo "Usage: "
    echo "      `basename $0` ORACLE_SID"
    exit 1
fi

# -------------------------------
#  Set environment here
# ------------------------------

if [ -f ~/.bash_profile ]; then
    . ~/.bash_profile
fi

export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56
export MAIL_LIST='Robinson.cheng@12306.com'
export MAIL_FM='oracle@szdb.com'
ORACLE_SID=$1;  export ORACLE_SID

# ----------------------------------------------
# check if the database is running, if not exit
# ----------------------------------------------

db_stat=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep| cut -f3 -d_`
if [ -z "$db_stat" ]; then
    echo " $ORACLE_SID is not available on `hostname` !!!"  
    MAIL_SUB=" $ORACLE_SID is not available on `hostname` !!!"
    MAIL_MSG="$ORACLE_SID is not available on `hostname` before age alert log file, exit, please check !"
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_MSG
    exit 1
fi

# -----------------------------------
# Find bdump directory for database
# -----------------------------------

DUMP_DIR=`sqlplus -S '/ as sysdba' << EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM  v\\$parameter WHERE  name = 'background_dump_dest';
exit
EOF`
if [ -z ${DUMP_DIR} ]; then
    echo "The bdump directory was not found for ${ORACLE_SID}"
    MAIL_SUB="The bdump directory was not found for ${ORACLE_SID}"
    MAIL_MSG="The bdump directory was not found for ${ORACLE_SID} on `hostname` before age log file,exit,please check !"
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_MSG
    exit 1
else
    echo ${DUMP_DIR}
fi

# -------------------------------
#  Archive alert log file
# -------------------------------

DT=`date +%Y%m%d -d '-1 day'`
OLD_DIR=${DT:0:6}
NEW_DIR=`date +%Y%m`
ORIG_ALERT_LOG=${DUMP_DIR}/alert_${ORACLE_SID}.log
OLD_ARC_DIR=${DUMP_DIR}/${OLD_DIR}
NEW_ARC_DIR=${DUMP_DIR}/${NEW_DIR}

if [ ! -d "${NEW_ARC_DIR}" ] ; then
    mkdir ${NEW_ARC_DIR}
fi

if [ "${OLD_DIR}" \< "${NEW_DIR}" ];then
    ARC_LOG=${OLD_ARC_DIR}/alert_${ORACLE_SID}.log.${DT}
else
    ARC_LOG=${NEW_ARC_DIR}/alert_${ORACLE_SID}.log.${DT}
fi

cat ${ORIG_ALERT_LOG} >>${ARC_LOG}
cat /dev/null>${ORIG_ALERT_LOG}
exit

4、部署脚本到crontab

*/15 * * * * /users/robin/dba_scripts/custom/bin/ck_alert.sh MMBOTST
0 0 * * * /users/robin/dba_scripts/custom/bin/age_alert.sh MMBOTST

5、补充   a、上面脚本用于实时监控Oracle告警日志,一旦检测到错误,将发送邮件。   b、对于已经检查过且发现错误的日志将被移动作为归档,也就是Oracle错误不会被重复检测。   c、所有有关错误检测的过滤条件被放置到过滤文件check_alert.awk中。   d、第3个脚本用于老化告警日志,建议设置老化的时间为每天0点,这样子,每天将会保留当天的告警日志。   e、对于老化的告警日值,按年月来存放,也即是以年月命名文件夹,当天告警日志会存放在当月文件夹。   f、使用了sendEmail邮件发送程序来发送邮件。参阅:不可或缺的 sendEmail   g、该脚本仅在Oracle 10g测试可用,Oracle 11g应做相应修改。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Linux/Unix shell 自动导入Oracle数据库

      使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份...

2072
来自专栏乐沙弥的世界

Linux/Unix shell 自动发送AWR report

     观察Oracle数据库性能,Oracle自带的awr 功能为我们提供了一个近乎完美的解决方案,通过awr特性我们可以随时从数据库提取awr报告。不过...

833
来自专栏杨建荣的学习笔记

海量数据迁移之外部表加载(100天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

3348
来自专栏杨建荣的学习笔记

归档问题导致的数据库无法启动 (80天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

33210
来自专栏杨建荣的学习笔记

关于两个简单问题的分析(r9笔记第10天)

工作中碰到问题当然是见怪不怪了,而处理这些问题也是我们的价值所在。 今天处理了几个看起来比较有意思的小问题,当然究其原因,要不是不规范,要不就是基本功不够扎实。...

3004
来自专栏数据库新发现

Use Nid to Change dbname

http://www.eygle.com/faq/Use.Nid.to.Change.Your.dbname.htm

873
来自专栏乐沙弥的世界

Oracle 基于 RMAN 的不完全恢复(incomplete recovery by RMAN)

      Oracle 数据库可以实现数据库不完全恢复与完全恢复。完全恢复是将数据库恢复到最新时刻,也就是无损恢复,保证数据库无丢失的恢复。而不完全恢复则是根...

1101
来自专栏互联网技术栈

APM开源产品Pinpoint——Hbase表设计文档

2033
来自专栏杨建荣的学习笔记

数据库11g升级中一次奇怪的问题 (30天)

客户的测试环境已经从10g升级到11g了。但是没过几天,数据hang住了,登都登不了了,而且通过sys,system,普通用户连接的错误都不一样 首先通过 一下...

3234
来自专栏乐沙弥的世界

Linux/Unix shell 自动导出Oracle数据库

       使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备...

1032

扫码关注云+社区

领取腾讯云代金券