Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# program: prod3_standby_status.pl (renamed from 79_prod3_standby_status_1.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 .com Solutions Inc.
#
# --------------- Revision History ---------------
# Date By Changes
# 01-18-2002 dsimpson Initial Release
# This output file was created by Installgen version 1.38 on Thu Nov 14 17:16:25 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 = ;
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 = ;
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 host2 last archivelog = $primary_log_number
prod3 on server host1 last archivelog = $standby_log_number
Difference = $log_difference
EOF
# --------------- Email Message Text ends 2 lines above ---------------
my $smtp_server ="mail.dotcomsolutionsinc.net";
my $email_recipient = 'dba\@dotcomsolutionsinc.net';
my $email_sender = 'prod3s@dotcomsolutionsinc.net';
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 --------------------------
