Oracle Windows alert log script Oracle Database Tips by Donald Burleson

Check Oracle Alert Logs for ORA errors and e-mail messages

For complete Oracle Windows scripts, see the book Windows for the Oracle DBA.

This script scans the Oracle Alert Log for the string ORA- and sends an email if it is found.  Rather than emailing the entire Alert Log, it has logic to strip the first ORA- error it finds and sends that.  If it does not find ORA-, then it drops to the next line, which sends it to the eof tag to exit.
 
This is extremely useful for other things, such as scanning application logs for particular errors or strings and reporting when they are found. 
 
Ideally, such a script would run as a recurring Windows Scheduled Task at an interval of however often you wish to check, such as every 10 minutes or every hour.
 
::
::  check_alert.bat
::
::  checks for ORA errors in alert log and emails them off
::
::  look for ORA- in alertlog. If found call send_mail routine else drop thru
::  to next line which is a goto the eof tag
::
@findstr "ORA-" C:\oracle10g\admin\orcl\bdump\alert_orcl.log && call :send_mail

see code depot for full script
goto :eof
 
::  Subroutine to send mail
 
:send_mail
del /Q c:\adminscripts\checkdb\error.txt
 
find "ORA-" < C:\oracle10g\admin\orcl\bdump\alert_orcl.log > c:\adminscripts\checkdb\error.txt
 
Blat  c:\adminscripts\checkdb\error.txt -t dba@email.com -s "ORCL error"
 
goto :eof
 
:eof
exit

---

Extract specific messages from a Windows alert log

You can easily monitor the Oracle alert log for error messages and send those messages to the DBA via e-mail.   The Oracle Windows bat file below reads the alert log using the following logic flow:

• Read the alert log extracting all of the Oracle error messages into an alert_log_err.txt file
   

• Compare the alert_log_err.txt file to a file containing previously extracted errors (alert_log_old.txt)
   

• Send any differences (new errors) to the DBA via e-mail

 

@ECHO OFF

REM +---------------------------------

REM |  ALERT_LOG.BAT

REM +-------------------------------------

REM | Set up client specific variables

REM +-------------------------------------

 

set BC_DIR=C:\BC

set FROM=Oracle@Client.com

set TO=Client@remote-dba.net

set SUBJECT=Client Alert Log

 

set ORACLE_SID=MYDB

set ORACLE_BASE=C:\ORACLE

set ORACLE_HOME=C:\ORACLE\ORA102

 

REM +-------------------------------------------------------------

REM | Now let's go get all the errors from the Alert Log

REM +-------------------------------------------------------------

 

egrep -h -e ORA- -e Errors %ORACLE_BASE%\admin\%ORACLE_SID%\bdump\ALERT_%ORACLE_SID%.LOG > alert_log_err.txt

 

REM +-------------------------------------------------------------

REM | If we don't have an old file to compare with, then

REM | create the old file

REM +-------------------------------------------------------------

 

if exist %BC_DIR%\Report\alert_log_old.txt goto COMPARE

touch %BC_DIR%\Report\alert_log_old.txt

 

:COMPARE

REM +-------------------------------------------------------------

REM | Compare the errors that we got this time with errors we

REM | got last time, keeping only new errors.

REM +-------------------------------------------------------------

 

comm -23 alert_log_err.txt %BC_DIR%\Report\alert_log_old.txt > To_e-Mail.txt

 

:MOVE

REM +-------------------------------------------------------------

REM | Save all the current errors for next time.

REM +-------------------------------------------------------------

 

mv alert_log_err.txt %BC_DIR%\Report\alert_log_old.txt

 

REM +-------------------------------------------------------------

REM | If new errors then e-mail them else exit

REM +-------------------------------------------------------------

 

getlines 1 1 To_e-mail.txt > del.it

if errorlevel 1 goto END

 

REM +-------------------------------------------------------------

REM | Mail the Alert Log to the DBA staff

REM +-------------------------------------------------------------

 

banner Alert Log > message.txt

echo. >> message.txt

udate >> message.txt

echo. >> message.txt

cat message.txt To_e-mail.txt >> xxx.txt

 

sendmail -messagefile=xxx.txt -from=%FROM% -subject="%SUBJECT%" %TO%

 

:END

rm -s del.it

rm -s message.txt

rm -s To_e-mail.txt

rm -s xxx.txt

 

exit

---

This blog also has an interesting technique for numbering and extracting Windows alert log messages:

in order to assign line numbers to each line in the Oracle alert log you need only issue the following command. And then redirect the output to another file for future searching.

DOS> TYPE alert_db10.log | FIND /N /V "" > numbered_alert_db10.log

AND here is a snippet of what you will get:

[214]Stopping background process MMON

 . . .

Now onto the command to strip these out and create a list. Here is the command:

FINDSTR /G:alertstrings numbered_alert_db10.log | FIND /N /V "" > numbered_alert_errors

AND here is what it produces:

[1][116]ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
 . . .

so now say we are looking for the 6th error in our alert log. Just issue the following command:

FINDSTR /B /C:[6] numbered_alert_errors

AND here is what you get:

[6][235]ORA-00312: online log 2 thread 1: '/oradata/db10/redo02.log'

Also, see these additional resources for Oracle Windows scripts:

• Dr. Hall has a great collection of downloadable Oracle scripts in his book "Oracle Job Scheduling".
   
• For UNIX and Linux Oracle scripts, see Jon Emmon's book "Oracle Shell Scripts".
   
• For scheduling Windows jobs, see Windows Oracle Job Schedulers examples
   
• For more Oracle Windows scripts, see my notes on Oracle scripts for Windows.

Windows for the Oracle DBA The landmark book Windows for the Oracle DBA is a comprehensive overview of everything an Oracle DBA needs to know to manage Oracle on Windows.  Order directly from Rampant and save 30%.