#!/u1/app/oracle/product/10.2.0/perl/bin/perl -w
use DBI;
use DBD::Oracle qw(:ora_session_modes);
# DB connection #
my $prod = "orclprod";
my $stby = "orcldr";
my $prodh;
unless ($prodh = DBI->connect('dbi:Oracle:'.$prod,
'sys', 'strongpassword',
{PrintError=>0, AutoCommit => 0,
ora_session_mode => ORA_SYSDBA})) {
print "Error connecting to DB: $DBI::errstr\n";
exit(1);
}
$prodh->{RaiseError}=1;
my $stbyh;
unless ($stbyh = DBI->connect('dbi:Oracle:'.$stby,
'sys', 'strongpassword',
{PrintError=>0, AutoCommit => 0,
ora_session_mode => ORA_SYSDBA})) {
print "Error connecting to DB: $DBI::errstr\n";
$prodh->disconnect;
exit(1);
}
$stbyh->{RaiseError}=1;
my $sth;
### query prod
$sth = $prodh->prepare( < <eosql );
select SEQUENCE#, BLOCK# from v\$managed_standby
where process='LGWR'
EOSQL
$sth->execute();
my ($psequence, $pblock) = $sth->fetchrow_array();
$sth->finish();
### query stdby
$sth = $stbyh->prepare( < </eosql><eosql );
select SEQUENCE#, BLOCK# from v\$managed_standby
where process='RFS' and client_process='LGWR'
EOSQL
$sth->execute();
my ($ssequence, $sblock) = $sth->fetchrow_array();
$sth->finish();
printf ("PROD : %10d %10d\n", $psequence, $pblock);
printf ("STANDBY: %10d %10d\n", $ssequence, $sblock);
$sth = $stbyh->prepare( < </eosql><eosql );
select nvl(sum(blocks),0)
+ $pblock - $sblock as BLOCK_GAP
from v\$archived_log
where sequence# between $ssequence and $psequence
EOSQL
$sth->execute();
my ($blockgap) = $sth->fetchrow_array();
$sth->finish();
printf ("%-10d blocks gap\n", $blockgap);
$stbyh->disconnect;
$prodh->disconnect;
</eosql>