Oracle Windows alert log script Oracle Tips by Burleson Consulting

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.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.