#!/bin/bash
# This script is to send mail for alerting ALERT LOG Monitoring
# Assumption smtb confirgured,created DIR's
# Note : you have to have SSH connectivity between monitoring server and the db server,Enter the ALERT log details in alertfile_detail.txt:
as format "+ASM1 10.95.10.101 oracle /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log"
WORK_DIR=/u01/app/oracle/util/scripts
LOG_DIR=/u01/app/oracle/util/log
log_file=/u01/app/oracle/util/scripts/alertfile_detail.txt
file_header=$WORK_DIR/alert_header.log
temp_file=$WORK_DIR/alert_report_temp.log
final_report=$WORK_DIR/alertlog_full_report.html
export PATH=$PATH:/usr/sbin
CURR_DATE=`date '+%m/%d/%y_%H:%M'`
if [ -e $temp_file ]
then
`rm $temp_file `
fi
if [ -e $final_report ]
then
`rm $final_report `
fi
if [ -e $file_header ]
then
`rm $file_header `
fi
touch $file_header
echo $CURR_DATE
html_head="Subject: Alert log report for ${CURR_DATE}
\nMIME-Version: 1.0 \nContent-Type: text/html \nContent-Disposition: inline \n
<html>
<head>
<meta http-equiv=\"Content-Type\" content=text/html;
charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
Alert Log error Details:
</title>
</head><body><br>Report Generated On ${CURR_DATE}<br>"
html_tail='<br><h2></h2><a><br>
</a></body></html>'
table_header='<p>
<table border='"'"'1'"'"' width='"'"'90%'"'"' align='"'"'center'"'"' summary='"'"'Script output'"'"'>
<col width="20">
<col width="100">
<tr><th scope="col">
Timestamp
</th>
<th scope="col"> Error Log </th>
</tr>'
table_trailer='</table></p>'
error_found=0
touch $final_report
while read t
do
ALERT_INST=`echo $t|awk '{print $1}'`
OS_USER=`echo $t|awk '{print $3}'`
OS_IP=`echo $t|awk '{print $2}'`
ALERT_FILE=`echo $t|awk '{print $4}'`
ssh -o StrictHostKeyChecking=no -n $OS_USER@$OS_IP 'if [ -e '$ALERT_FILE' ]; then echo "true"; fi' > $temp_file
ssh_works=`grep 'true' $temp_file|wc -l`
if test $ssh_works -ne 1
then
echo 'Server Not Reachable or Alert File Does not exist for '$ALERT_INST'@'$OS_IP'<br>' > $file_header
error_found=1
fi
echo 'processing for instance '$ALERT_INST
ssh -o StrictHostKeyChecking=no -n $OS_USER@$OS_IP 'tail -50000 '$ALERT_FILE'|gawk --re-interval '"'"'BEGIN { RS = "[[:upper:]]{1}[[:lower:]]{2} [[:upper:]]{1}[[:lower:]]{2} [[:digit:]]{1,2} [[:digit:]]{1,2}:[[:digit:]]{1,2}:[[:digit:]]{1,2} [[:digit:]]{4}";
m=split("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec",d,"|")
for(o=1;o<=m;o++){
months[d[o]]=sprintf("%02d",o)
}
currtime=systime()
}
{if ((match($0,"ORA-") > 0 ||
match($0,"Shutting down instance") > 0 ||
match($0,"Starting ORACLE instance") > 0 ||
match($0,"ALTER DATABASE") > 0 ||
match($0,"error") > 0 ||
match($0,"Error") > 0) &&
(currtime-rectime < 7300) && (match($0,"Fatal NI connect error 12170") == 0))
{
gsub("\n","\n<br>");
print "<tr><td>",occurtime,"</td><td>",$0,"</td></tr>";
}
}
{
occurtime=RT;
savedRT = RT;
gsub(":"," ",savedRT);
split(savedRT,time," ");
mon=time[2];
date=time[3];
hour24=time[4];
min=time[5];
seconds=time[6];
year=time[7];
daterec = (year " " months[mon] " " date " " hour24 " " min " " seconds)
rectime=mktime(daterec)
}'"' " > $temp_file
recpresent=0
recpresent=`grep '<tr><td>' $temp_file |wc -l`
if test $recpresent -gt 0
then
echo -e "<a name=\"$ALERT_INST$OS_IP\"><BR><BR><h2> ALERT LOG REPORT FOR $ALERT_INST in server $OS_IP :</h2><br><BR></a>" >> $final_report
echo $table_header >> $final_report
cat $temp_file >> $final_report
echo $table_trailer >> $final_report
echo '<a href="#top">Back to top</a>' >> $final_report
echo -e "<a href=\"#$ALERT_INST$OS_IP\">$ALERT_INST on $OS_IP</a><br>" >> $file_header
error_found=1
fi
done < $log_file
echo -e $html_head > $temp_file
echo '<br><h2> List of instances with alert log error</h2><br>' >> $temp_file
cat $file_header >> $temp_file
cat $final_report >> $temp_file
echo -e $html_tail >> $temp_file
cat $temp_file > $final_report
if test $error_found -gt 0
then
sendmail -v username@mailid.com < $final_report;
fi
####################
Sample output
# This script is to send mail for alerting ALERT LOG Monitoring
# Assumption smtb confirgured,created DIR's
# Note : you have to have SSH connectivity between monitoring server and the db server,Enter the ALERT log details in alertfile_detail.txt:
as format "+ASM1 10.95.10.101 oracle /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log"
WORK_DIR=/u01/app/oracle/util/scripts
LOG_DIR=/u01/app/oracle/util/log
log_file=/u01/app/oracle/util/scripts/alertfile_detail.txt
file_header=$WORK_DIR/alert_header.log
temp_file=$WORK_DIR/alert_report_temp.log
final_report=$WORK_DIR/alertlog_full_report.html
export PATH=$PATH:/usr/sbin
CURR_DATE=`date '+%m/%d/%y_%H:%M'`
if [ -e $temp_file ]
then
`rm $temp_file `
fi
if [ -e $final_report ]
then
`rm $final_report `
fi
if [ -e $file_header ]
then
`rm $file_header `
fi
touch $file_header
echo $CURR_DATE
html_head="Subject: Alert log report for ${CURR_DATE}
\nMIME-Version: 1.0 \nContent-Type: text/html \nContent-Disposition: inline \n
<html>
<head>
<meta http-equiv=\"Content-Type\" content=text/html;
charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
Alert Log error Details:
</title>
</head><body><br>Report Generated On ${CURR_DATE}<br>"
html_tail='<br><h2></h2><a><br>
</a></body></html>'
table_header='<p>
<table border='"'"'1'"'"' width='"'"'90%'"'"' align='"'"'center'"'"' summary='"'"'Script output'"'"'>
<col width="20">
<col width="100">
<tr><th scope="col">
Timestamp
</th>
<th scope="col"> Error Log </th>
</tr>'
table_trailer='</table></p>'
error_found=0
touch $final_report
while read t
do
ALERT_INST=`echo $t|awk '{print $1}'`
OS_USER=`echo $t|awk '{print $3}'`
OS_IP=`echo $t|awk '{print $2}'`
ALERT_FILE=`echo $t|awk '{print $4}'`
ssh -o StrictHostKeyChecking=no -n $OS_USER@$OS_IP 'if [ -e '$ALERT_FILE' ]; then echo "true"; fi' > $temp_file
ssh_works=`grep 'true' $temp_file|wc -l`
if test $ssh_works -ne 1
then
echo 'Server Not Reachable or Alert File Does not exist for '$ALERT_INST'@'$OS_IP'<br>' > $file_header
error_found=1
fi
echo 'processing for instance '$ALERT_INST
ssh -o StrictHostKeyChecking=no -n $OS_USER@$OS_IP 'tail -50000 '$ALERT_FILE'|gawk --re-interval '"'"'BEGIN { RS = "[[:upper:]]{1}[[:lower:]]{2} [[:upper:]]{1}[[:lower:]]{2} [[:digit:]]{1,2} [[:digit:]]{1,2}:[[:digit:]]{1,2}:[[:digit:]]{1,2} [[:digit:]]{4}";
m=split("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec",d,"|")
for(o=1;o<=m;o++){
months[d[o]]=sprintf("%02d",o)
}
currtime=systime()
}
{if ((match($0,"ORA-") > 0 ||
match($0,"Shutting down instance") > 0 ||
match($0,"Starting ORACLE instance") > 0 ||
match($0,"ALTER DATABASE") > 0 ||
match($0,"error") > 0 ||
match($0,"Error") > 0) &&
(currtime-rectime < 7300) && (match($0,"Fatal NI connect error 12170") == 0))
{
gsub("\n","\n<br>");
print "<tr><td>",occurtime,"</td><td>",$0,"</td></tr>";
}
}
{
occurtime=RT;
savedRT = RT;
gsub(":"," ",savedRT);
split(savedRT,time," ");
mon=time[2];
date=time[3];
hour24=time[4];
min=time[5];
seconds=time[6];
year=time[7];
daterec = (year " " months[mon] " " date " " hour24 " " min " " seconds)
rectime=mktime(daterec)
}'"' " > $temp_file
recpresent=0
recpresent=`grep '<tr><td>' $temp_file |wc -l`
if test $recpresent -gt 0
then
echo -e "<a name=\"$ALERT_INST$OS_IP\"><BR><BR><h2> ALERT LOG REPORT FOR $ALERT_INST in server $OS_IP :</h2><br><BR></a>" >> $final_report
echo $table_header >> $final_report
cat $temp_file >> $final_report
echo $table_trailer >> $final_report
echo '<a href="#top">Back to top</a>' >> $final_report
echo -e "<a href=\"#$ALERT_INST$OS_IP\">$ALERT_INST on $OS_IP</a><br>" >> $file_header
error_found=1
fi
done < $log_file
echo -e $html_head > $temp_file
echo '<br><h2> List of instances with alert log error</h2><br>' >> $temp_file
cat $file_header >> $temp_file
cat $final_report >> $temp_file
echo -e $html_tail >> $temp_file
cat $temp_file > $final_report
if test $error_found -gt 0
then
sendmail -v username@mailid.com < $final_report;
fi
####################
Sample output
ALERT LOG REPORT FOR DBSID1 in server 10.96.10.101 :
Timestamp
|
Error Log
|
Thu Jul 17 03:19:30 2014
|
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP |
Thu Jul 17 03:19:35 2014
|
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP |
No comments:
Post a Comment