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
"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
Great script, very useful. Thanks for sharing it.
ReplyDeletevery nice..thanks!.
ReplyDeletebut if we like to check it for one day prior to present day how to alter this one?....
you can alter the script by replacing the line
Deletedt=$(date '+%a %b %d')
with
dt=$(date --date="1 days ago" '+%a %b %d')
Also I found this neat and easy to use script:
ReplyDeletehttp://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html
@Valiantvimal
ReplyDeleteyou can alter the script by replacing the line
dt=$(date '+%a %b %d')
with
dt=$(date --date="1 days ago" '+%a %b %d')
How to run it from crontab, as your script says to mention the alert log location manually.
ReplyDeleteif your requirement is to give the alertlog file inside the script, do 3 things.
Delete1.
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!
It's good script. How it can be modified to email an hour extract of alert log.
ReplyDeletei 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