Standby Trail Report

This post will show you how to setup a standby database trail report.

The setup is based on 3 standby databases, each one checked and reported on how far behind they trail their live production counter part.

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. A log and sql folder created within the folder in step 3
5. Download UnixUtils – the 20+ unix utilities adapted for windows-in particular: cat.exe

INSTALL

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

2. Create a file called mail.vbs inside E:\oracle\scripts\Report_Standby_Hours_Behind. 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 report_hours_behind.bat and paste the following into it. Set the variables in yellow to what you want:
SETLOCAL EnableDelayedExpansion

set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1

set PATH=E:\oracle\scripts\Report_Standby_Hours_Behind\;%ORACLE_HOME%\bin;%PATH%;C:\Windows\System32

set LOG_DIR=E:\oracle\scripts\Report_Standby_Hours_Behind\logs

set SQL_DIR=E:\oracle\scripts\Report_Standby_Hours_Behind\sql

echo . > %LOG_DIR%\standby_hours_behind_report.log

set ORACLE_SID=DBNAME1

sqlplus /nolog @%SQL_DIR%\standby_hours_behind.sql %LOG_DIR% %ORACLE_SID%

cat %LOG_DIR%\standby_hours_behind_%ORACLE_SID%.log >> %LOG_DIR%\standby_hours_behind_report.log

set ORACLE_SID=DBNAME2sqlplus /nolog @%SQL_DIR%\standby_hours_behind.sql %LOG_DIR% %ORACLE_SID%

cat %LOG_DIR%\standby_hours_behind_%ORACLE_SID%.log >> %LOG_DIR%\standby_hours_behind_report.log

set ORACLE_SID=DBNAME3

sqlplus /nolog @%SQL_DIR%\standby_hours_behind.sql %LOG_DIR% %ORACLE_SID%

cat %LOG_DIR%\standby_hours_behind_%ORACLE_SID%.log >> %LOG_DIR%\standby_hours_behind_report.log

mail.vbs sendaddress@yourdomain.net dba-emailaddress@domain.net "SERVERNAME Standby Hours Behind" %LOG_DIR%\standby_hours_behind_report.log

4. Create a file called standby_hours_behind.sql inside the sql folder and paste the following into it:
conn / as sysdba

define p_log_dir='&1'

define p_db='&2'

set echo off

set verify off

set linesize 20

set pages 100

spool &1.\standby_hours_behind.log

PROMPT &p_db. DATABASE

PROMPT ===============

select trunc(24*(sysdate-max(first_time)),2) as hours_behind

  from v$log_history;

spool off

exit

4. Copy cat.exe (unixutils) into E:\oracle\scripts\Report_Standby_Hours_Behind or unix utils in your %PATH% location. You need unixutils to take advantage of linux in windows.

5. Create a task scheduler to run twice a day – once at 6am and once at 4pm:

Click start, open stask scheduler

Right click task scheduler library

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

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.

Create 2 schedules – for details on their configuration see the screenshot below.

Schedule the job to run in the morning and afternoon - create 2 schedules on daily repeat