Simple Script to check alert.log file for errors in oracle

We have to check alert log files for errors, but its a time consuming process to read the file using less and check for errors. If you do a grep it will take the whole file and if the log rotation is done by month, you will get undesired output. Here is a script to check alert.log file in oracle for errors for today. We just have to copy this to a file and name it as alertcheck.sh and then from the OS prompt run
 "sh alertcheck.sh <alert log file with complete path>" without quotes.

######################################################################################################
#
#This script can be used to check any ORA- errors are there in the alertlog file for the present day #
#
######################################################################################################
#!/sbin/bash
if ! [ $1 ] ; then
echo Usage:
echo "sh alertcheck.sh 'alertlogFilename'"
else
 alrt=$1
 frm=$(grep -m 1 -n  "`date '+%a %b %d'`" $alrt |awk -F[:] '{print $1}')
 if ! [ $frm ]; then
  echo -e "\033[33mWe cannot find any entry for today."
  echo -e "Please check the file you have given is correct OR check with tail command manually\033[0m"
 else
  lst=$(wc -l $alrt | awk '{print $1}')
  lns=$(awk -v a="$lst" -v b="$frm" 'BEGIN{print a-b+1}')
  dt=$(date '+%a %b %d')
  echo -e "\033[34m..........Checking $lns lines writen today -$dt- out of $lst lines in the alert log.........\033[0m"
  err=$(tail -$lns $alrt | grep ORA-|tail -1|awk '{print $1}')
  if [ $err ]; then
    echo -e "\033[31mErrors found:\033[0m"
    echo ------------
    tail -$lns $alrt |  grep  ORA- |grep -n ORA-
    echo ""
    echo ""
    echo -e "\033[31mCheck the details of the errors below. \033[0m (Details means the surroundig lines of the error message only)"
    echo  "-------------------------------------"
    tail -$lns $alrt |  grep -A 5 -B 2 --color ORA-
  else
    echo -e "\033[32mNo Errors Found. Enjoy \033[33m:)\033[0m"
  fi
 fi
fi
#############################End of the script ##################################

If there are errors it will list you all errors first and then a few lines around the error message with a heading error details.

If no errors it will give you corresponding message

If the script can't find any entry for the date, it will give a message for it and will remind you to check the file.

Enjoy

Thank you,
ALI

9 comments:

  1. Great script, very useful. Thanks for sharing it.

    ReplyDelete
  2. very nice..thanks!.
    but if we like to check it for one day prior to present day how to alter this one?....

    ReplyDelete
    Replies
    1. you can alter the script by replacing the line
      dt=$(date '+%a %b %d')
      with
      dt=$(date --date="1 days ago" '+%a %b %d')

      Delete
  3. Also I found this neat and easy to use script:
    http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html

    ReplyDelete
  4. @Valiantvimal
    you can alter the script by replacing the line
    dt=$(date '+%a %b %d')
    with
    dt=$(date --date="1 days ago" '+%a %b %d')

    ReplyDelete
  5. How to run it from crontab, as your script says to mention the alert log location manually.

    ReplyDelete
    Replies
    1. if your requirement is to give the alertlog file inside the script, do 3 things.
      1.
      Remove the following lines at the beginning
      if ! [ $1 ] ; then
      echo Usage:
      echo "sh alertcheck.sh 'alertlogFilename'"
      else

      2.
      Replace the $1 in the next line (alrt=$1) with your alertlog file name with complete path

      3.
      Remove the last line (fi)

      done!

      Delete
  6. It's good script. How it can be modified to email an hour extract of alert log.

    ReplyDelete
  7. i want script to show only last 2 hours ORA- errors how do i achieve that? pls help. i tool chatGPT help as well but no luck.

    ReplyDelete