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脚本 http://www.linuxidc.com/Linux/2012-09/70310.htmLinux/Unix shell sql 之间传递变量 http://www.linuxidc.com/Linux/2013-01/78811.htmLinux Unix shell 调用 PL/SQL http://www.linuxidc.com/Linux/2013-01/78810.htm1、监控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
fiexport ORACLE_SID=$1
export MACHINE=`hostname`
export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56
export MAIL_LIST="Robinson.chen@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
EOFcat /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
fiORIG_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} " > /tmp/${ORACLE_SID}_check_monitor_log.mail
echo -e "Alert log was copied into ${NEW_ALERT_LOG} ">> /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
firm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/null
rm /tmp/${ORACLE_SID}_monitor_location.txt > /dev/nullexit

利用NFS远程备份报 ORA-27054 错误解决办法PL/SQL中long变量只允许存储32768字节相关资讯 Linux Shell Oracle告警日志 monitor alter log file

Linux基础知识：shell基础命令（今 10:39）
Linux系统监控shell脚本（04月09日）
Linux shell脚本编程（03月01日）

Linux shell数组（07月26日）
Linux Shell实现批量关闭局域网中（03月10日）
Linux系统入门教程：如何知道当前（11/27/2015 08:36:09）

本文评论查看全部评论（0）