Monitor Standby DB Script 
- Windows Server 
This script checks the status of archived redo logs applied to the standby database. If the standby database is behind by more than 3 archivelogs, an email is sent to the DBA. 
Oracle Version = 9.0.1 
OS = Windows NT/2000 

By .com Solutions Inc. www.dotcomsolutionsinc.net 




#! /user/local/bin/perl 
# program: prod3_standby_status.pl (renamed from 79_prod3_standby_status_2.pl) 

# features: This script checks the automatic application of 
# archived redo logs on the standby database server. 
# If the standby database is behind by more than 
# 3 archivelog files - an email is sent to the DBA. 
# Note: If a standby delay is used, the max_error_difference 
# will need to be adjusted accordingly. 
# 
# requires: Mail::Sendmail 
# 
# Script Sequence#: 79 
# Used By: run automatically via AT scheduler - on standby server 
# Usage: 
# ******** Monitor standby database status nightly at 11:50PM ******** 

# AT 23:50 /every:M,T,W,Th,F,S,Su c:\server_scripts\prod3_standby_status.pl 

# 
# Copyright 2002 by ABC Company 
# 
# --------------- Revision History --------------- 
# Date By Changes 
# 01-18-2002 dsimpson Initial Release 
# 
#This output file was created by Installgen 
version 1.0.5 on Thu May 30 05:31:13 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net 


use vars qw(@ISA @EXPORT_OK %EXPORT_TAGS); 
use Exporter qw(); 
@ISA=qw(Exporter); 
use strict; 
use Mail::Sendmail; 

# insure that environment variable is used by this perl script 
$ENV{'ORACLE_SID'} = "PROD3"; 

# ----------------------- main -------------------------- 

# make sure I/O is not buffered - and is received in correct order 
select (STDOUT); 
$| = 1; 
select (STDERR); 
$| = 1; 

# maximum gap allowed in applying archivelog files 
my $max_error_difference = 3; 
my $error_flag=0; 
my $temp_sql_filename = "temp_sql.sql"; 
my $tempsqlcode=""; 
my @proglist=''; 
my @output_spool_text=''; 
my $standby_log_number=0; 
my $primary_log_number=0; 

# -------------- standby database - highest log# 
my $tempsqlcode=<<"EOF"; 
connect / as SYSDBA 
set echo on 
spool standby_status.log 
SELECT MAX(SEQUENCE#) FROM V\$LOG_HISTORY; 
spool off 
exit; 
EOF 

open (FILE1,">$temp_sql_filename") || die ("Could not open output file 
$temp_sql_filename for writing. \n Does the full directory path exist?"); 

print FILE1 ($tempsqlcode); 
# close the output file 
close (FILE1); 
@proglist = ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename"); 

system (@proglist); 

open (FILE1,"standby_status.log") || die ("Could not open output file 
standby_status.log for reading. \n Does the full directory path exist?"); 

@output_spool_text = <file1>; 
close FILE1; 

my $standby_log_number = $output_spool_text[4]; 

# -------------- primary database - highest log# 
my $tempsqlcode=<<"EOF"; 
connect admin/adminpwd\@prod3 
set echo on 
spool standby_status.log 
SELECT MAX(SEQUENCE#) FROM V\$LOG_HISTORY; 
spool off 
exit; 
EOF 

open (FILE1,">$temp_sql_filename") || die ("Could not open output file 
$temp_sql_filename for writing. 
Does the full directory path exist?"); 
print FILE1 ($tempsqlcode); 
# close the output file 
close (FILE1); 
@proglist = ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename"); 

system (@proglist); 

open (FILE1,"standby_status.log") || die ("Could not open output file 
standby_status.log for reading. 
Does the full directory path exist?"); 
@output_spool_text = <file1>; 
close FILE1; 

my $primary_log_number = $output_spool_text[4]; 

my $log_difference = abs($primary_log_number - $standby_log_number); 

if ($log_difference >= $max_error_difference) 
{ 
# primary and standby servers are out of sync by more than the maximum 
allowed amount 
# signal an error by sending an email 
$error_flag=1; 
} 


# --------------- Email Message Text starts 2 lines down --------------- 

my $email_content=<<"EOF"; 
Warning! The standby and primary database servers exceed a gap of more 
than $max_error_difference archivelogs. 

prod3s on server red last archivelog = $primary_log_number 
prod3 on server ws134 last archivelog = $standby_log_number 
Difference = $log_difference 

EOF 
# --------------- Email Message Text ends 2 lines above --------------- 


my $smtp_server ="mail.abccompany.com"; 
my $email_recipient = ''; 
my $email_sender = 'prod3s@abccompany.com'; 
my $email_subject = "prod3s standby - archivelog difference = $log_difference 
"; 

if ($error_flag==1) 
{ 
# now send the actual email 
my %mail = (To => $email_recipient, 
From => $email_sender, 
Subject => $email_subject, 
Message => $email_content, 
Smtp => $smtp_server 
); 

if (sendmail %mail) { } 
else { } 

} 


# ----------------------- main -------------------------- 

Direct link to file: standbydb_status_windows