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
Linux/Unix shell monitoring Oracle instance (Monitor instance)
Linux/Unix shell monitoring Oracle listener (Monitor listener)

 

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 1fi# -------------------------------#  Set environment here # ------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiexport ORACLE_SID=$1export MACHINE=`hostname`export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56export 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 1fi;# --------------------------------------#  Get the location of alert log file# --------------------------------------sqlplus '/ as sysdba' << EOF > /tmp/${ORACLE_SID}_monitor_temp.txtcolumn xxxx format a10column value format a80set lines 132SELECT 'xxxx' ,value FROM  v\$parameter WHERE  name = 'background_dump_dest'/exitEOFcat /tmp/${ORACLE_SID}_monitor_temp.txt | awk '$1 ~ /xxxx/ {print $2}' > /tmp/${ORACLE_SID}_monitor_location.txtread ALERT_DIR < /tmp/${ORACLE_SID}_monitor_location.txtrm /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_DIRfiORIG_ALERT_LOG=${ALERT_DIR}/alert_${ORACLE_SID}.logNEW_ALERT_LOG=${ARCH_DIR}/alert_${ORACLE_SID}.log.${DT}TEMP_ALERT_LOG=${ORIG_ALERT_LOG}.tempAWK_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.logif [ -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     exitfirm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/nullrm /tmp/${ORACLE_SID}_monitor_location.txt > /dev/nullexit

2. Filter Oracle alarm log error messages

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. Aging Oracle warning log script

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 1fi# -------------------------------#  Set environment here# ------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiexport MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56export 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 1fi# -----------------------------------# Find bdump directory for database# -----------------------------------DUMP_DIR=`sqlplus -S '/ as sysdba' << EOFset pagesize 0 feedback off verify off heading off echo offSELECT value FROM  v\\$parameter WHERE  name = 'background_dump_dest';exitEOF`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 1else    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}.logOLD_ARC_DIR=${DUMP_DIR}/${OLD_DIR}NEW_ARC_DIR=${DUMP_DIR}/${NEW_DIR}if [ ! -d "${NEW_ARC_DIR}" ] ; then    mkdir ${NEW_ARC_DIR}fiif [ "${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}ficat ${ORIG_ALERT_LOG} >>${ARC_LOG}cat /dev/null>${ORIG_ALERT_LOG}exit

4. Deploy the script to crontab

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

5. Supplement
A. The preceding script monitors Oracle alarm logs in real time. Once an error is detected, an email is sent.
B. logs that have been checked and found errors will be moved as archives, that is, Oracle errors will not be repeatedly detected.
C. All filtering conditions related to error detection are placed in the filter file check_alert.awk.
D. 3rd scripts are used for aging alarm logs. We recommend that you set the aging time to every day. In this way, the daily alarm logs will be retained every day.
E. For the aging alert date value, it is stored by year and month, that is, the year and month naming folder. The alert logs for the current day are stored in the current month folder.
F. The sendemail mail program is used to send emails. See: Indispensable sendemail
G. The script is only available in Oracle 10g testing, and Oracle 11G should be modified accordingly.

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)