Oracle UNIX Alert Log Administration Oracle UNIX/Linux Tips by Burleson Consulting

Alert for bad messages in the alert log

This section of the script inspects the last 400 lines of the alert log and displays any messages that are listed in the parm_alert_log.ora file.  The parm file is used because we do not want to disturb the DBA unless we have an important error.  Hence, this parm file should be periodically updated to show the latest important errors.

Note that this script uses the UNIX diff command to compare the last error listing to the latest listing.  This technique prevents the script from e-mailing the same alert error, over and over again.

Also note that we use the UNIX remote shell command called rsh to copy all alert log messages into a centralized directory (Figure 9-1)

Figure 1: Using rsh to create a centralized alert file

Having a central location for error alerts in a large UNIX environment allows the DBA manager to see exactly what problems are occurring on each UNIX database server.

#***********************************************************
# Check alert ora for ORA-600 and other ORA errors
# The list of ORA messages is in the file called parm_alert_log.ora
#***********************************************************

for MSG in `cat ${ora_unix_home_dir}/mon/parm_alert_log.ora`
do
  tail -400 $ALERT_DIR/alert_$ORACLE_SID.log|grep $MSG \
    >> /tmp/ora600_${ORACLE_SID}.ora
done

#*************************************************************
# Only send the alert if there is an error in the output . . . .
#*************************************************************
check_stat=`cat /tmp/ora600_${ORACLE_SID}.ora|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
   #*************************************************************
   # Only send the alert if there is a change to the output . . . .
   #*************************************************************
   newm=`diff /tmp/ora600_${ORACLE_SID}.ora \
       /tmp/ora600_${ORACLE_SID}.old|wc -l`
   chgflg=`expr $newm`
   if [ $chgflg -ne 0 ]
   then
      #*************************************************************
      # Mail the message to the DBA's in $dbalist
      #*************************************************************
      cat /tmp/ora600_${ORACLE_SID}.ora|\
          mailx -s "$ORACLE_SID alert log message detected" $dbalist
      #*************************************************************
      # Copy the message to the master log file on sp3db
      #*************************************************************
      echo "$ORACLE_SID ===> `date` - ORA alert log error found." \
        `cat tmp/ora600_${ORACLE_SID}.ora`> logbook/temp$ORACLE_SID
      rcp -p temp$ORACLE_SID  sp3db:/u/oracle/mon/logbook
      rsh sp3db "cat /u/oracle/mon/logbook/temp$ORACLE_SID \
        >> u/oracle/mon/logbook/alert_mon.log"
   fi
fi

cp /tmp/ora600_${ORACLE_SID}.ora /tmp/ora600_${ORACLE_SID}.old
rm -f /tmp/oracheck_${ORACLE_SID}.ora

# Here we write a blank line to the ora file . . .
echo `date` > /tmp/oracheck_${ORACLE_SID}.ora

#*************************************************************
# Now we run the check, writing errors to the oracheck.ora file
#*************************************************************
~oracle/mon/oracheck.ksh ${ORACLE_SID} ${TS_FREE} ${NUM_EXTENTS} >>
tmp/oracheck_${ORACLE_SID}.ora

#**************************************************************
# This section checks the Oracle mount points
#**************************************************************

#*************************************************************
# Get the Oracle users home directory from /etc/passwd
#*************************************************************
ora_unix_home_dir=`cat /etc/passwd|grep ^oracle|cut -f6 -d':'`
#echo home dir = $ora_unix_home_dir

Mark the dialect differences for file space commands

This section of the script makes the script generic for any UNIX environment.  By using the uname ?a command, we gather the dialect of UNIX and then set an environment variable called $dialect_df according to the appropriate space command for that UNIX dialect.

This allows the same script to be run in a variety of environments.

#*************************************************************
# Set-up the dialect changes for HP/UX and AIX (df -k) vs (bdf)
#*************************************************************
os=`uname -a|awk '{ print $1 }'`
if [ $os = "OSF1" ]
then
    dialect_df="df -k"
fi
if [ $os = "AIX" ]
then
    dialect_df="df -k"
fi
if [ $os = "IRIX64" ]
then
    dialect_df="df -k"
fi
if [ $os = "HP-UX" ]
then
    dialect_df="bdf"
fi

Check the free space in the archived redo log directory

This section of the script checks to see if the database is running in ARCHIVELOG mode, and if so, it uses the UNIX location from log_archive_dest, as gathered earlier by the get_dict_parm.sql script.

#*************************************************************
# Get the free space from the archived redo log directory
#*************************************************************
LOG_ARCHIVE_START=\
  `cat /tmp/log_archive_start_${ORACLE_SID}.ora|awk '{print $1}'`
export LOG_ARCHIVE_START

#echo $LOG_ARCHIVE_START
if [ $LOG_ARCHIVE_START = 'TRUE' ]
then

   LOG_ARCHIVE_DEST=`cat /tmp/log_archive_dest_${ORACLE_SID}.ora|\
      awk '{print 1}'`
   export LOG_ARCHIVE_DEST  

   nohup ${dialect_df} $LOG_ARCHIVE_DEST > \
       /tmp/arch_${ORACLE_SID}.ora 2>&1

   # The above could be not found . . .

   flag1=`cat /tmp/arch_${ORACLE_SID}.ora|grep find|wc -l`
   #*************************************************************
   # If the log archive dest is not found, truncate last entry
   #*************************************************************
   free_space_num=`expr ${flag1}`
   if [ $free_space_num -eq 1 ]
   then
     echo $LOG_ARCHIVE_DEST|sed 's/\/arch//g' > /tmp/arch1_$ORACLE_SID.ora
     LOG_ARCHIVE_DEST=`cat /tmp/arch1_$ORACLE_SID.ora`
   fi

   #  This ugly code is because bdf and df -k
   #  have free space in different columns
   if [ $os = "IRIX64" ]
   then
      arch_dir_mp=`${dialect_df} $LOG_ARCHIVE_DEST|\
          grep -v kbytes|awk '{ print $7 }'`
      arch_free_space=`${dialect_df} ${arch_dir_mp}|\
          grep -v kbytes|awk '{ print $3 }'`
   fi
   if [ $os = "AIX" ]
   then
      arch_dir_mp=`${dialect_df} $LOG_ARCHIVE_DEST|\
         grep -v blocks|awk '{ print $7 }'`
      arch_free_space=`${dialect_df} ${arch_dir_mp}|\
         grep -v blocks|awk '{ print $3 }'`
   fi
   if [ $os = "OSF1" ]
   then
      arch_dir_mp=`${dialect_df} $LOG_ARCHIVE_DEST|\
        grep -v blocks|awk '{ print $7 }'`
      arch_free_space=`${dialect_df} ${arch_dir_mp}|\
        grep -v blocks|awk '{ print $3 }'`
   fi
   if [ $os = "HP-UX" ]
   then
      arch_dir_mp=`${dialect_df} $LOG_ARCHIVE_DEST|\
         grep -v kbytes|awk '{ print $6 }'`
      arch_free_space=`${dialect_df} ${arch_dir_mp}|\
           grep -v kbytes|awk '{ print $4 }'`
   fi  

   #echo $LOG_ARCHIVE_DEST
   #echo $arch_dir_mp
   #echo $arch_free_space

   #*************************************************************
   # Now, display if free space is < ${KB_FREE}
   #*************************************************************
   free_space_num=`expr ${arch_free_space}`
   kb_free_num=`expr ${KB_FREE}`
   #echo $free_space_num
   if [ $free_space_num -lt ${kb_free_num} ]
    then
      #*************************************************************
      # Display a message on the operations console
      #*************************************************************
         echo "NON-EMERGENCY ORACLE ALERT. \
           Mount point ${ora_unix_home_mp1}\
           has less than ${KB_FREE} K-Bytes free."
      #*************************************************************
         # Copy the message to the master log on sp3db
      #*************************************************************
         echo "$ORACLE_SID ===> `date` - Moint point \
          ${ora_unix_home_mp1} has less than \
          ${KB_FREE} K-bytes free." > logbook/temp$ORACLE_SID
      rcp -p temp$ORACLE_SID  sp3db:/u/oracle/mon/logbook
      rsh sp3db "cat /u/oracle/mon/logbook/temp$ORACLE_SID >> \
          /u/oracle/mon/alert_mon.log"
      exit 67
   fi
fi  

Check the free space in the UNIX home directory

This section of the script gathers the location of the oracle user home directory, and then executes the appropriate command, placing the output in a variable called $ora_unix_home_fr.  This variable can then be interrogated and compared with the allowed value in the parameter file.

#*************************************************************
# get the mount point associated with the home directory
#*************************************************************
ora_unix_home_mp=`${dialect_df} ${ora_unix_home_dir}|awk '{ print $7}'`
#echo mp1 = $ora_unix_home_mp
ora_unix_home_mp1=`echo ${ora_unix_home_mp}|awk '{ print $2 }'`

#*************************************************************
# Get the free space for the mount point for UNIX home directory
#*************************************************************
#  This ugly code is because bdf and df -k
#  have free space in different columns
if [ $os = "IRIX64" ]
then
   ora_unix_home_fr=`${dialect_df} ${ora_unix_home_mp1}|awk '{ print $3}'`
fi
if [ $os = "OSF1" ]
then
   ora_unix_home_fr=`${dialect_df} ${ora_unix_home_mp1}|awk '{ print $3}'`
fi
if [ $os = "AIX" ]
then
   ora_unix_home_fr=`${dialect_df} ${ora_unix_home_mp1}|awk '{ print $3}'`
fi
if [ $os = "HP-UX" ]
then
   ora_unix_home_fr=`${dialect_df} ${ora_unix_home_mp1}|awk '{ print $4}'`
fi
ora_unix_home_fr1=`echo ${ora_unix_home_fr}|awk '{ print $2 }'`
#echo free = $ora_unix_home_fr1

#*************************************************************
# Now, display if free space is < ${KB_FREE}
#*************************************************************
free_space_num=`expr ${ora_unix_home_fr1}`
kb_free_num=`expr ${KB_FREE}`
#echo $free_space_num
if [ $free_space_num -lt ${kb_free_num} ]
 then
   #*************************************************************
   # Display a message on the operations console
   #*************************************************************
   echo "NON-EMERGENCY ORACLE ALERT. Mount point ${ora_unix_home_mp1} \
       has less than ${KB_FREE} K-Bytes free."
   #*************************************************************
   # Copy the message to the master log on sp3db
   #*************************************************************
   echo "$ORACLE_SID ===> `date` - Moint point ${ora_unix_home_mp1}\
       has less than ${KB_FREE} K-bytes free." > logbook/temp$ORACLE_SID
   rcp -p temp$ORACLE_SID  sp3db:/u/oracle/mon/logbook
   rsh sp3db "cat /u/oracle/mon/logbook/temp$ORACLE_SID >>
u/oracle/mon/logbook/alert_mon.log"
   exit 67
fi

Check other UNIX mount points

This section of the script executes the commands that we generated in the /tmp/dump1_$ORACLE_SID.ora file.  We then loop through the output from this command and send the e-mail alert for any mount point that has less than the accepted value.

chmod +x /tmp/dump1_$ORACLE_SID.ora

#*************************************************************
# Now we execute this file to get the free space in the filesystem
#*************************************************************
/tmp/dump1_$ORACLE_SID.ora > /tmp/dump2_$ORACLE_SID.ora

loop=1
#*************************************************************
# Here we loop to get all free space numbers and check each
#*************************************************************
for free_num in `cat /tmp/dump2_$ORACLE_SID.ora`
do
    #echo loop = $loop
    mp=`cat /tmp/dump1_$ORACLE_SID.ora|awk '{print $2'}`
    mp1=`echo $mp|awk '{print$'$loop'}'`
    #echo point = $mp1
    free_space_num=`expr ${free_num}`
    #echo $free_space_num
    if [ $free_space_num -lt $kb_free_num ]
    then
      #*************************************************************
      # Display a message on the operations console
      #*************************************************************
       echo "NON-EMERGENCY ORACLE ALERT. The mount point for $mp1 has\
          less than ${KB_FREE} K-Bytes free."
      #*************************************************************
      # Copy the message to the master log on sp3db server
      #*************************************************************
       echo "$ORACLE_SID ===> `date` - Moint point ${mp1} less than \
             {KB_FREE} K-Bytes free." > logbook/temp$ORACLE_SID
       rcp -p temp$ORACLE_SID  sp3db:/u/oracle/mon/logbook
       rsh sp3db "cat /u/oracle/mon/logbook/temp$ORACLE_SID >>\
          u/oracle/mon/logbook/alert_mon.log"
       loop="`expr $loop + 1`"
    fi

done

#*************************************************************
# If errors messages exist (2 or more lines), then go on . . .
#*************************************************************
if [ `cat /tmp/oracheck_${ORACLE_SID}.ora|wc -l` -ge 2 ]
then
   #*************************************************************
   # Display a message on the operations console
   #*************************************************************
   echo "NON-EMERGENCY ORACLE ALERT. Contact DBA and report error\
       ==>"` cat /tmp/oracheck_${ORACLE_SID}.ora`
   #*************************************************************
   # Copy the message to the master log on sp3db
   #*************************************************************
   echo "$ORACLE_SID ===>" `cat /tmp/oracheck_${ORACLE_SID}.ora`\
      >logbook/temp$ORACLE_SID
   rcp -p temp$ORACLE_SID  sp3db:/u/oracle/logbook
   rsh sp3db "cat /u/oracle/mon/logbook/temp$ORACLE_SID\
      >>u/oracle/mon/logbook/alert_mon.log"
   exit 69
fi

Now, let?s wrap-up this chapter with a review of the main concepts.
 

Hypercharge Oracle on Linux!  The landmark book "Linux for the Oracle DBA" is a complete guidebook of expert tips and secrets for automating Oracle database administration. It's packed with working Linux scripts and tools to make any DBA look like a guru.