Shell script to capture ORA errors from Alert Log

Hi,

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

Thanks for your assistance

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...

OK, thanks,

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."

What does adrci in the line

adrci << EOF

do?

adrci - is the new 11g feature that reads the alert log

Automatic Diagnostic Repository Command Interface

Something like this should work with bash:

#!/bin/bash


ORACLE_HOME=<your_oracle_home>
ORACLE_SID=<your_oracle_sid>
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
_my_first_date='1990-01-01 00:00:00'
_my_last_date_file=_my_last_date

[ -f "$_my_last_date_file" ] ||
  printf > ./"$_my_last_date_file" '%s\n' "$_my_first_date"
  
_last_date=$(<./"$_my_last_date_file")

printf > ./"$_my_last_date_file" '%s\n' "$(
  date '+%Y-%m-%d %H:%M:%S'
  )"

_mailto=<your_email_address>
_mail_subject="Alert for $ORACLE_SID on $HOSTNAME"


_my_result=$(
  adrci exec="
    set home $ORACLE_SID;
    show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP >= '$_last_date'\\\"
   "
  )

case $_my_result in 
  ( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"  
esac

Hi,

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

[oracle@ukedxdtmtdbs01a bin]$ #!/bin/bash
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ukedxdtmtdbs01a bin]$ ORACLE_SID=DCTMTEST1
[oracle@ukedxdtmtdbs01a bin]$ ORAENV_ASK=NO
[oracle@ukedxdtmtdbs01a bin]$ export ORACLE_HOME ORACLE_SID ORAENV_ASK
[oracle@ukedxdtmtdbs01a bin]$ _my_first_date='1990-01-01 00:00:00'
[oracle@ukedxdtmtdbs01a bin]$ _my_last_date_file=_my_last_date
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ [ -f "$_my_last_date_file" ] ||
>   printf > ./"$_my_last_date_file" '%s\n' "$_my_first_date"
-bash: ./_my_last_date: Permission denied
[oracle@ukedxdtmtdbs01a bin]$
_l[oracle@ukedxdtmtdbs01a bin]$ _last_date=$(<./"$_my_last_date_file")
-bash: ./_my_last_date: No such file or directory
[oracle@ukedxdtmtdbs01a bin]$
p[oracle@ukedxdtmtdbs01a bin]$ printf > ./"$_my_last_date_file" '%s\n' "$(
>   date '+%Y-%m-%d %H:%M:%S'
 >   )"
-bash: ./_my_last_date: Permission denied
[oracle@ukedxdtmtdbs01a bin]$
_m[oracle@ukedxdtmtdbs01a bin]$ _mailto=name@company.com
[oracle@ukedxdtmtdbs01a bin]$ _mail_subject="Alert for $ORACLE_SID on $HOSTNAME"

[oracle@ukedxdtmtdbs01a bin]$ _my_result=$(
>   adrci exec="
>     set home $ORACLE_SID;
>     show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP >= '$_last_date'\\\"
>    "
>   )
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ case $_my_result in
 >   ( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
> esac
[oracle@ukedxdtmtdbs01a bin]$

save the file with .sh extension and assign the execute permission and run the shell script

Hi,

that's perfect, thanks.

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?

Many thanks for your assistance

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]$

This is correct. If you want to see what happens just run the script with -xv:

bash -xv <script_file>

If there are no errors for the specified period (yesterday), nothing happens,
otherwise you'll receive the mail with the errors.

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.

OK Thanks,

this time it mail'd me,

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.

Thanks

It's OK, no need to modify anything.

OK, thanks,

I've scheduled it via cron - and it now works perfect - thanks again for all your assiatance