Monitor alter log file)

Using shell scripts to monitor and manage Oracle databases will greatly simplify DBA workload, such as common instance monitoring, monitoring, and alarm log monitoring, and database backup, AWR report automatic mail, etc. This section describes how to use the shell script to monitor the Oracle alarm log (monitor alter log file) in Linux ).

Linux Shell:

Linux/Unix shell scripts call SQL and RMAN scripts

Passing variables between Linux/Unix shell SQL statements

Linux Unix shell calls PL/SQL

1. Monitor the Oracle alarm log script

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. 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_sid1__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_sid1__monitor_temp.txt | awk '$1 ~ /Xxxx/{print $2} '>/tmp/zookeeper oracle_sid1__monitor_location.txt
Read ALERT_DIR </tmp/zookeeper oracle_sid1__monitor_location.txt
Rm/tmp/${oracle_sid1__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/zookeeper oracle_sid1__monitor_location.txt>/dev/null

Exit

1 2 3 Next Page