Back to Scripting | Scan and email ORACLE Alert Log errors

This job searches the ORACLE Alert log for errors. If it finds any errors that have not already been emailed, then it will insert those errors as the message body, and send email to the identified administrators. This script sends new errors only; it does not re-send errors that have already been emailed. Note that this script requires the MAILTO.EXE to send SMTP email. It may also be necessary to verify that your mail server can receive SMTP mail. Some other command line "send mail" program may be used as a substitute for MAILTO.EXE.

FindAlertLogErrors.cmd

REM run the job to search the Oracle Alert Log
cscript d:\scheduled\FindAlertLogErrors.vbs //B

REM reschedule 60 minutes into the future
soon 3600 d:\scheduled\FindAlertLogErrors.cmd

exit

FindAlertLogErrors.vbs

'SCRIPT:  	FindAlertLogErrors.vbs
'AUTHOR:  	D. Sisk
'DATE:    	1998/06/03
'DESC:    	Searches the ORACLE Alert Log for new errors, and mails a file containing the errors if any are found.
'DEPENDENCIES:	FINDSTR.EXE, MAILTO.EXE, WSH Cscript.exe host

'Constants
DblQuote = CHR(34)

'Mail settings
MailServer = "YourMailServer"
FromUser = "YourOracleInstance.WORLD"
ToUser = "dbaEmailAddress@yourdomain.com;otherEmailAddress@yourdomain.com"
Subject = "ALERT: Errors in ORACLE Alert log"
Message = ""

'Other settings
SearchString = "ORA-"  'The string to search for
AlertFile = "d:\orant\bdump\rtppALRT.log"  'The file to search
CurErrFile = "d:\orant\bdump\RTPP_CurErrors.log" 'The output file for found errors
PrevErrFile = "d:\orant\bdump\RTPP_PrevErrors.log" 'Copy of previous output file
NewErrFile = "d:\orant\bdump\RTPP_NewErrors.log" 'output file for new errors

'Main routine
Set WshShell = Wscript.CreateObject("Wscript.Shell") 'Create a Shell object

'Make copy of the current error file
CmdString = "CMD /C COPY " & CurErrFile & " " & PrevErrFile
ReturnCode = WshShell.Run(CmdString, 1, TRUE)

'Execute the FINDSTR command on the ALERT file to get current errors
 CmdString = "CMD /C FINDSTR " & DblQuote & SearchString & DblQuote & " " & AlertFile & " > " & CurErrFile
 CurErrors = WshShell.Run(CmdString, 1, TRUE) '0 = Errors found, 1 = No errors found

'If there were previous errors, *subtract* those from the current errors
 CmdString = "CMD /C FINDSTR /V /G:" & PrevErrFile & " " & CurErrFile & " > " & NewErrFile
 NewErrors = WshShell.Run(CmdString, 1, TRUE) '0 = New errors found, 1 = no new errors found, 2 = no previous errors to subtract

'If there are current errors and new errors, then email only the new error file
 If CurErrors = 0 and NewErrors = 0 Then 'Send the new errors
  MailString = "d:\mailto\mailto -H " & MailServer & " -U " & FromUser & " -D " & ToUser & _
               " -S " & DblQuote & Subject &DblQuote & " -MF " & NewErrFile
  WshShell.Run(MailString)
 End If

'If there are current errors and no previous errors, then email the current error file
 If CurErrors = 0 and NewErrors =2 Then 'Send the current errors
  MailString = "d:\mailto\mailto -H " & MailServer & " -U " & FromUser & " -D " & ToUser & _
               " -S " & DblQuote & Subject &DblQuote & " -MF " & CurErrFile
  WshShell.Run(MailString)
 End If

Wscript.quit  'Exit the script

Adding this job to the scheduler

This job reschedules itself each time it runs, so simply run the job once and the necessary schedule entry will be added automatically.