Saturday, July 6, 2013

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

[oracle@igrexandc001d03 ~]$ sh alertcheck.sh <alert log file with complete path>

######################################################################################################
#
#This script can be used to check any ORA- errors are there in the alertlog file for the present day #
#
######################################################################################################
#!/sbin/bash
#alrt=/data/app/oracle/diag/rdbms/sec01/sec01/trace/alert_sec01.log
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}')
  lns=250
  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.

########################### Output ####################################


[oracle@igrexandc001d03 ~]$ sh alertcheck.sh /u01/app/oracle/diag/rdbms/irebsprd/IREBSPRD1/trace/alert_IREBSPRD1.log
..........Checking 250 lines writen today -Sat Jul 06- out of 1341211 lines in the alert log.........
Errors found:
------------
1:ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
2:ORA-03149: Invalid Oracle error code
3:ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
4:ORA-03149: Invalid Oracle error code


Check the details of the errors below.  (Details means the surroundig lines of the error message only)
-------------------------------------
Sat Jul 06 07:27:51 2013
Errors in file /u01/app/oracle/diag/rdbms/irebsprd/IREBSPRD1/trace/IREBSPRD1_ora_66056.trc  (incident=964624):
ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
ORA-03149: Invalid Oracle error code
Incident details in: /u01/app/oracle/diag/rdbms/irebsprd/IREBSPRD1/incident/incdir_964624/IREBSPRD1_ora_66056_i964624.trc
Errors in file /u01/app/oracle/diag/rdbms/irebsprd/IREBSPRD1/trace/IREBSPRD1_ora_66056.trc  (incident=964625):
ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
ORA-03149: Invalid Oracle error code
Incident details in: /u01/app/oracle/diag/rdbms/irebsprd/IREBSPRD1/incident/incdir_964625/IREBSPRD1_ora_66056_i964625.trc
Sat Jul 06 07:27:54 2013
Dumping diagnostic data in directory=[cdmp_20130706072754], requested by (instance=1, osid=66056), summary=[incident=964624].
Sat Jul 06 07:27:56 2013
Sweep [inc][964625]: completed
[oracle@igrexandc001d03 ~]$

############################################################################

Enjoy


I've not prepared  this script and got it from somewhere but found useful and hence wanted to share with you.

Thank you,
Manish

4 comments:

  1. can you please append with it a one script for trigering a mail to dba .

    ReplyDelete
  2. [oracle@bbdb1 Scripts]$ vi main_alertcheck.sh
    sh /home/oracle/Scripts/alertcheck.sh /u01/app/oracle/diag/rdbms/bbdb/bbprod2/trace/alert_bbprod2.log >/home/oracle/Scripts/error_log.log
    grep 'ORA-' /home/oracle/Scripts/error_log.log > /dev/null
    # If found, email the Oracle user with the contents of the alert log
    if [ $? = 0 ]
    then
    echo "Dear DBA team, kindly check the attached files." | mutt -a "error_log.log" -s "alert error" -- my_email@domian.com
    fi

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete