DBA survival BLOG

#!D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe -w
use DBI;
use DBD::Oracle qw(:ora_session_modes);
# DB connection #
my $prod  = "prod";
my $stby = "stby";
my $prodh;
unless ($prodh = DBI->connect('dbi:Oracle:'.$prod, 
    'sys', 'strongpwd', 
    {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', 'strongpwd',
    {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 stdby MRP0
$sth = $stbyh->prepare( <<EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby 
    where process='MRP0'
EOSQL
$sth->execute();
my ($mrpthread, $mrpsequence, $mrpblock) = $sth->fetchrow_array();
$sth->finish();
 
### query stdby RFS
$sth = $stbyh->prepare( <<EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby 
    where process='RFS' and client_process='LGWR' order by thread#
EOSQL
$sth->execute();
my ($rfsthread1, $rfssequence1, $rfsblock1) = $sth->fetchrow_array();
my ($rfsthread2, $rfssequence2, $rfsblock2) = $sth->fetchrow_array();
$sth->finish();
 
### query prod
$sth = $prodh->prepare( <<EOSQL );
select thread#, SEQUENCE#, BLOCK#
    from gv\$managed_standby
    where process='LNS' order by thread#
EOSQL
$sth->execute();
my ($pthread1, $psequence1, $pblock1) = $sth->fetchrow_array();
my ($pthread2, $psequence2, $pblock2) = $sth->fetchrow_array();
$sth->finish();
 
 
printf ("ENVIRONM  Thread Sequence   Block\n");
printf ("--------- ------ ---------- ----------\n");
printf ("PROD     LNS1  1 %10d %10d\n", $psequence1, $pblock1);
printf ("STANDBY  RFS1  1 %10d %10d\n", $rfssequence1, $rfsblock1);
printf ("PROD     LSN2  2 %10d %10d\n", $psequence2, $pblock2);
printf ("STANDBY  RFS2  2 %10d %10d\n", $rfssequence2, $rfsblock2);
printf ("STANDBY  MRP0  %d %10d %10d\n", $mrpthread, $mrpsequence, $mrpblock);
 
my $psequence;
my $pblock;
if ( $mrpthread == 1 ) {
$psequence=$psequence1;
$pblock=$pblock1;
} else {
$psequence=$psequence2;
$pblock=$pblock2;
}
 
$sth = $stbyh->prepare( <<EOSQL );
select nvl(sum(blocks),0)
+ $pblock - $mrpblock as BLOCK_GAP
from gv\$archived_log
where thread#=$mrpthread and sequence#
between $mrpsequence and $psequence
EOSQL
$sth->execute();
my ($mrpblockgap) = $sth->fetchrow_array();
$sth->finish();
 
$sth = $stbyh->prepare( <<EOSQL );
select nvl(sum(blocks),0)
+ $pblock1 - $rfsblock1 as BLOCK_GAP
from gv\$archived_log
where thread#=1 and sequence#
between $rfssequence1 and $psequence1
EOSQL
$sth->execute();
my ($rfsblockgap1) = $sth->fetchrow_array();
$sth->finish();
 
$sth = $stbyh->prepare( <<EOSQL );
select nvl(sum(blocks),0)
+ $pblock2 - $rfsblock2 as BLOCK_GAP
from gv\$archived_log
where thread#=2 and sequence#
between $rfssequence2 and $psequence2
EOSQL
$sth->execute();
my ($rfsblockgap2) = $sth->fetchrow_array();
$sth->finish();
printf ("\n\n%-10d blocks gap in TRANSMISSION\n", $rfsblockgap1+$rfsblockgap2);
printf ("%-10d blocks gap in APPLY (MRP0)\n", $mrpblockgap);
 
$stbyh->disconnect;
$prodh->disconnect;