Alert Log Daily Report

This document will show you how to setup an alert log report: an email that sends the alert log trail since the last report was sent. This helps a DBA keep an eye on the alert log of a database and helps the administrators spot any issues pre-emptively

REQUIREMENTS:

1. Access to Task Scheduler (or cron for Linux)
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts
4. Download UnixUtils – the 20+ unix utilities adapted for windows-in particular: wc.exe and tail.exe

INSTALL

1. Open up command prompt (cmd) and execute the following – adjust root location if need be
e:
mkdir E:\oracle\scripts\LogReport
cd E:\oracle\scripts\LogReport


2. Create a file called mail.vbs inside E:\oracle\scripts\LogReport. Copy the following and paste into the file
'Grab the parameters

from_address = WScript.Arguments(0)

to_address = WScript.Arguments(1)

subject = WScript.Arguments(2)

file = WScript.Arguments(3)

'Set the file reading variables

Const ForReading = 1

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties

SMTPServer = "smtprelay.inter.domain.net"

Recipient = to_address

From = from_address

Subject = subject

'Create the mail object

GenericSendmail SMTPserver, From, Recipient, Subject, Message

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)

Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off

set msg = WScript.CreateObject("CDO.Message")

msg.From = From

msg.To = Recipient

msg.Subject = Subject

msg.TextBody = Message

msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msg.Configuration.Fields.Update

msg.Send

End Sub

3. Create a file called logreport_DBNAME.bat and paste the following into it. Set the variables in yellow to what you want:
SET ORACLE_SID=DBNAME

SET ORACLE_BASE=E:\Oracle

SET ALERT_LOG=%ORACLE_BASE%\diag\rdbms\%ORACLE_SID%\%ORACLE_SID%\trace\alert_%ORACLE_SID%.log

SET WORKING_DIR=E:\oracle\scripts\LogReport

SET PATH=%PATH%;%WORKING_DIR%

cd %WORKING_DIR%

E:

SETLOCAL EnableDelayedExpansion

echo Alert Log Difference

`echo --------------------

echo .

echo Load the last line count taken into a variable...

set /p old_linecount=<linecount.txt

echo Get the current line count of the database alert log...

wc -l < %ALERT_LOG% > linecount.txt

set /p new_linecount=<linecount.txt

echo Subtract the latest from the previous...

set /a difference=%new_linecount%-%old_linecount%

echo %difference%

if %difference% geq 3000 set difference=3000

if %difference% geq 1 tail -n %difference% %ALERT_LOG% > alert_%ORACLE_SID%.out

if %difference% equ 0 echo The alert log has not change or is empty, if you receive this message twice, investigate the log file %ALERT_LOG%. > alert_%ORACLE_SID%.out

if %difference% lss 0 echo The alert log %ALERT_LOG% has changed in a way where yesterdays file has a greater line count than todays, if you receive this message twice, investigate the log file. > alert_%ORACLE_SID%.out

mail.vbs servername@domainname.net emailaddress@domainname.net "%ORACLE_SID% DailyAlert" %WORKING_DIR%\alert_%ORACLE_SID%.out

4. Copy cat.exe (unixutils) into E:\oracle\scripts\LogReport or unix utils in your %PATH% location

5. Create a task scheduler to run once a day – I usually set it for 5:30am:

Click start, open task scheduler

Right click task scheduler library

Under the general tab, ensure the task executes with the highest privileges and runs while the user in not logged into the system. Enter a description for the server administrator.

In the action tab, browse to the bat file location created in step 3

Create a schedule – for details on their configuration see the screenshot below.

Schedule the job to run at 2 times – create 2 schedules on daily repeat