as the title says, I am after a simple script, which will open the Alert log from
an 11.2.0.1 Linux environment and mail the error message and description to a recipient email address.
I can then schedule this job via cron and let it run every 15 minutes.
I have searched online for this - but many of them seem either too complex or
do not work for Oracle 11
Why not submit a sample of each ( too complex, not working with oracle 11), so that we can do some brainstorming, for the benefit of all?
First case would be to understand the complexity and simplify to suit your plan.
Second, to see if the issue can be solved...
I have this particular script, which I have executed - but nothing is extracted from the alert log and therefore no mail is sent.
Can you have a scan through this script and tell me if there any errors - or am I able to schedule this via cron
script:
export SID=$ORACLE_SID
export TIMESTAMP=`date +%M%H%d%m%y`;
export mon_dir=$ORACLE_BASE/admin/DCTMTEST/dbmonitor
export fn=$mon_dir/email_body.tmp
export alertck=$mon_dir/alertck.log
touch $alertck*
touch $fn
touch $ORACLE_BASE/admin/DCTMTEST/dbmonitor/donot_delete.log
EMAIL='name@company.com'
adrci << EOF
spool $mon_dir/alert_$TIMESTAMP.log
set termout off
set homepath diag/rdbms/$ORACLE_SID/$ORACLE_SID
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'" -term
spool off
exit
EOF
export c_log=`wc -l $mon_dir/alert_$TIMESTAMP.log awk '{ print $1 }'`
export c_tmp=`wc -l $mon_dir/donot_delete.log awk '{ print $1 }'`
echo $c_log
echo $c_tmp
if (($(($c_log)) > $(($c_tmp)))); then
comm -23 $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log grep ORA- sort -u > $alertck
exec 3< $alertck
# read until the end of the file
until [ $done ]
do
read <&3 ERR_LINE
if [ $? != 0 ] then
done=1
continue
fi
echo $ERR_LINE >> $fn
export ERR_NO=`echo $ERR_LINE awk '{ print $1 }'awk -F- '{ print $2 }' awk -F: '{ print $1 }'`
echo " Oracle error is : ORA-$ERR_NO"
oerr ora $ERR_NO >> $fn
echo " " >> $fn
echo "-----------------------------------------------------" >> $fn
echo " " >> $fn
'done'
echo "ERRORS: sending mail!"
SUBJECT="$SID - ORA Error Message found in alert.log on `date`"
echo $SUBJECT
# from a Linux server, use following line to send email:
cat $fn mail -s "$SUBJECT" $EMAIL
else
echo "No Oracle error is found in alert.log since last time this script was run."
'fi'
mv $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log
rm $fn
echo "The script was executed successfully."
Thanks for that and I executed the script and these are the results.
Maybe something is not enabled, prior to execution. This was run from the command line, not from cron
However is it possible to only capture that particular day's errors. So for example this will be run on a daily basis, say 3am and I would like the ORA error message for only the previous day. Is this possible?
Yes, this is for Linux (or any other system that has the GNU date installed):
#!/bin/bash
ORACLE_HOME=<your_oracle_home>
ORACLE_SID=<your_oracle_sid>
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
_mailto=<your_email_address>
_mail_subject="Alert for $ORACLE_SID on $HOSTNAME"
_today=$(
date '+%Y-%m-%d 00:00:00'
)
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
case $_my_result in
( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
esac
Thanks for that - now when I execute the script nothing happens.
I would like to capture the script to see what happens but if I execute file.sh > file.log the log file is empty
This is the script you sent me with the 3 necessary changes in bold. Any suggestions?
[oracle@ukedxdtmtdbs01a scripts]$ more test.sh
#!/bin/bash
ORACLE_HOME=ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=DCTMTEST1
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
_mailto=name@company.com
_mail_subject="Alert for $ORACLE_SID on $HOSTNAME"
_today=$(
date '+%Y-%m-%d 00:00:00'
)
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
case $_my_result in
( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
esac
[oracle@ukedxdtmtdbs01a scripts]$
Thanks very much - It worked as there were no errors yesterday, but I would have expected for it to mail me - and say that - or does it only mail on error, i.e if it finds something? output from running with -xv
[oracle@ukedxdtmtdbs01a scripts]$ bash -xv test.sh
#!/bin/bash
ORACLE_HOME=ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
+ ORACLE_HOME=ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=DCTMTEST1
+ ORACLE_SID=DCTMTEST1
ORAENV_ASK=NO
+ ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
+ export ORACLE_HOME ORACLE_SID ORAENV_ASK
_mailto=name@company.com
+ _mailto=name@company.com
_mail_subject="Alert for $ORACLE_SID on $HOSTNAME"
+ _mail_subject='Alert for DCTMTEST1 on ukedxdtmtdbs01a.pearson.com'
_today=$(
date '+%Y-%m-%d 00:00:00'
)
date '+%Y-%m-%d 00:00:00'
++ date '+%Y-%m-%d 00:00:00'
+ _today='2011-06-08 00:00:00'
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
date -d yesterday +'%Y-%m-%d 00:00:00'
++ date -d yesterday '+%Y-%m-%d 00:00:00'
+ _yesterday='2011-06-07 00:00:00'
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
++ adrci 'exec=
set home DCTMTEST1;
show alert -term -P \"MESSAGE_TEXT like '\''%ORA%'\'' and ORIGINATING_TIMESTAMP between '\''2011-06-07 00:00:00'\'' and '\''2011-06-08 00:00:00'\''\"
'
+ _my_result='DIA-48447: Message 48447 not found; No message file for product=RDBMS, facility=DIA; arguments: [DCTMTEST1]
DIA-48494: Message 48494 not found; No message file for product=RDBMS, facility=DIA'
case $_my_result in
( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
esac
+ case $_my_result in
Modified the script as per your requirement (mail in any case).
There was an error in the previous version of the script that I corrected: oraenv wasn't sourced. You may need to adjust the path to oraenv.
#!/bin/bash
ORACLE_HOME=<your_oracle_home>
ORACLE_SID=<your_oracle_sid>
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
# you may need to adjust the path to oraenv
. "$ORACLE_HOME"/bin/oraenv
_mailto=<your_email_address>
_mail_subject_ok="No ORA- for $ORACLE_SID on $HOSTNAME"
_mail_subject_ko="ORA- for $ORACLE_SID on $HOSTNAME"
_today=$(
date '+%Y-%m-%d 00:00:00'
)
_yesterday=$(
date -d yesterday +'%Y-%m-%d 00:00:00'
)
_my_result=$(
adrci exec="
set home $ORACLE_SID;
show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"
"
)
[[ $_my_result == *ORA-* ]] &&
_mail_subject=$_mail_subject_ko ||
_mail_subject=$_mail_subject_ok
mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
Please post the output of bash -xv <script_name> so I can verify the correct execution.
however not sure about the statement 'You may need to adjust the path to oraenv.'
this is my location for oraenv --> /u01/app/oracle/product/11.2.0/db_1/bin/oraenv
now you have a line --> . "$ORACLE_HOME"/bin/oraenv
Now do I add something to the line you have?
I think this is the last part - then I can schedule - again much thanks, and as a novice, I'm learning a lot about scripting, that will come in useful down the line.