News

  1. Home
  2. All posts
All posts, Linux, Oracle, Unix by Team Ajara

Script to monitor Alert log in Oracle

 Alert log monitoring in Oracle

Shell script to monitor alertlog file in Oracle databases on a server .  for example if there are 10 databases running on the server ,this script verifies alerts in all those 10 databases. This script can be scheduled in crontab.

Script name: alertlog_mon.sh

calling script: alert_log_mon.sql

#!/bin/ksh
#set -x
#set -v
err() {
test -n “$@” && echo “ERROR: $@” >&2 # print error message if given
mail_msg=$@
echo $mail_msg >> $LOGFILE}
set_env() {
umask 177
TIME=`date +%H`
export SCRIPT_DIR=/u00/app/oracle/scripts/ALERT_LOG_MON
CONN_STR=”sys/abc123 as sysdba”
export LOGFILE=$SCRIPT_DIR/alert_log_file_mon.log
SQL_FILE=$SCRIPT_DIR/alert_log_mon.sql
S_MSG1=”Script to capture errors in alert_log file failed”
export V_MAILEES=”dba@ajara.com”
export CONTENT=”alert_log_file_mon.log”
#PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:
#export PATH=$PATH
}
main()
{
set_env

for ORACLE_SID in `ps -ef|grep pmon|grep -v grep|grep -v ASM |grep -v root|cut -f3 -d _`
do

#ORACLE_BASE=/u00/app/oracle
export ORACLE_BASE
export SUBJECT=”Errors in alert_log file: ${ORACLE_SID}”
ORA_HOME=`cat /etc/oratab|grep -w “^$ORACLE_SID” |cut -d “:” -f2`
ORACLE_HOME=${ORA_HOME}

#export ORAENV_ASK=NO
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
export ORACLE_HOME
#. /usr/local/bin/oraenv <<EOF
#$ORACLE_SID
#EOF
echo “ORACLE_HOME: $ORACLE_HOME”>>abc.log
echo “ORACLE_SID = $ORACLE_SID”>>abc.log
PATH=$ORACLE_HOME/bin:$PATH:.

rm -f $LOGFILE
touch $LOGFILE || err “Error in creating mail file”
echo ”
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT 2;
CONNECT $CONN_STR
@$SQL_FILE
EXIT;
” | $ORACLE_HOME/bin/sqlplus -s /nolog || err “$S_MSG1”
echo “Start …..Date and Time:`date`”>>alert_log_file_mon_${ORACLE_SID}.log
DT=`date +%b%d_%Y`
cat $LOGFILE>>${ORACLE_SID}_alert_log_file_mon_${DT}.log
ls -l $SCRIPT_DIR/alert_log_file_mon.log>>log_alert_log_file_mon_${ORACLE_SID}.log
echo “END …..Date and Time:`date`”>>alert_log_file_mon_${ORACLE_SID}.log
if [ ! -s $LOGFILE ]
then
echo “in the exit”
continue
else
echo ” in the if …..”
echo ” “>>$LOGFILE
echo ” “>>$LOGFILE
echo ” “>>$LOGFILE

mailx -s “${SUBJECT}” $V_MAILEES < $LOGFILE

fi

done
}
main “$@”

 

SQL file name: alert_log_mon.sql

Copy this SQL file under SCRIPT_DIR . i.e) /u00/app/oracle/scripts/ALERT_LOG_MON

SET TRIMOUT ON
SET TRIMSPOOL ON
— SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGESIZE 99
SET LINESIZE 800
SET ECHO OFF
SET TERM OFF
SET VERIFY OFF
SET WRAP ON
PROMPT
PROMPT alert log mon
PROMPT ==========================
col TABLESPACE_NAME format a35
col FILE_NAME format a75
col AUTOEXTENSIBLE format a7
col BYTES format 999999– alter session set nls_date_format=’dd-mon-yy hh24:mi:ss’ ;

spool $LOGFILE
select
rownum “line”,
message_text “error”,
originating_timestamp
from
sys.x$dbgalertext
where
–originating_timestamp > (sysdate – 20/1440)
originating_timestamp > (sysdate – 60/1440)
and
message_text like ‘%ORA-%’
order by
originating_timestamp;
CLEAR COLUMNS
SPOOL OFF
SET TERMOUT ON

See also