Here is the script we use as a template for monitoring Oracle databases via query. You can use it to view any query you can think of provided you have permissions as the user you connect with.
Place it in the $UPTIME_HOME/bin directory and make sure it is executable
Set up a custom monitor on the web interface
Pass the arguments on the line for it.
Not really sure if I gave enough info for people to rip and mod it. If not, please post your questions and I will try to explain.
#!/usr/bin/perl
#############################################################
#
# ATS Generic Oracle SQL Script For Uptime monitoring System
# Written by Rocky Allen for ATS 2006
# This script is distributed under the Perl Artistic License
#
#############################################################
### always use warnings and strict
use warnings;
use strict;
### install DBI and DBD::Oracle modules on the monitoring station
### READ THE INSTRUCTIONS – you have to set the oracle environment for root
use DBI;
### Set this value to your oracle home ($ORACLE_HOME)
BEGIN {
$ENV{ORACLE_HOME} = '/opt/oracle/product/10gr2';
}
### Uptime is paasing the first variable
my $host = shift;
### SID is the SID of your oracle instance This is your first parameter in the arguments line on the web interface
my $sid = shift;
### userrname to connect as Second argument
my $user = shift;
### password – hard code this entry and DONT pass it in as a param. Also, make sure to secure the script properly
my $passwd = [snip];
### This is related to THIS particular query – you may not need it Third argument (for me)
my $dest_id = shift;
### Scope and declare variables for later use
my ($sth0, @row);
### database connection starts here
### Place your query between the SQL_END statements as illustrated below
### I have found that the second SQL_END statement needs to be at the beginning of the line
### This particular query will check for log sync in dataguard scenarios. You can write any query though
my $dbh = DBI->connect(“dbi:Oracle:host=$host;sid=$sid”, $user, $passwd, { PrintError => 0});
$sth0 = $dbh->prepare( <
FROM V$ARCHIVED_LOG LOCAL,
(SELECT SEQUENCE#, THREAD#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = $dest_id
AND COMPLETION_TIME >= (SYSDATE) – 1) NOT_LOCAL
WHERE LOCAL.COMPLETION_TIME >= (SYSDATE) – 1
AND LOCAL.DEST_ID = 1
AND LOCAL.SEQUENCE# = NOT_LOCAL.SEQUENCE#(+)
AND LOCAL.THREAD# = NOT_LOCAL.THREAD#(+)
AND NOT_LOCAL.SEQUENCE# IS NULL
SQL_END
### execute the query … doy
$sth0->execute();
### scope and declare bind values (see below)
my $answer;
### binding columns is the most efficient way to get data back from your query
### you will need one bind value per column that you expect to get back from the query above.
### I expect only one value back from my query so I bound one column
### you will definitely have to figure this out on a per query basis
$sth0->bind_columns($answer);
### Here is where we parse the data returned and assign conditions to it
while( $sth0->fetch() ) {
### Warning condition – disconnect from DB and exit 1
if ($answer >= 4 and $answer
print “WARN – Standby $answer logs out of syncn”;
$dbh->disconnect;
exit 1;
} elsif ($answer >= 7) {
### error condition – disconnect from DB and exit 2
print “CRIT – Standby $answer logs out of syncn”;
$dbh->disconnect;
exit 2;
}
}
$dbh->disconnect;
### Everything was swell. Just print your fine and exit 0
print “OK – Standby $answer logs out of sync”;
exit 0;