Oracle #GoldenGate Replicate Apply (Run) Rate

For a couple of weeks now, I’ve been trying to figure out a way to identify the size of data for transactions that are getting processed over a give period of time with Oracle GoldenGate.  When I started to think through the process, I keyed in on the Relative Byte Address (RBA).  What is the RBA?  From reading Oracle’s GoldenGate documentation, the RBA is mostly a marker within the trail file to identify the location of the transaction.  This got me to thinking; maybe I can use the RBA to “estimate” the amount of data applied to the source over a period of time (compare 2 RBAs).
Before I ventured off in the unknown; I wanted to verify if there was a method already identified by Oracle.  What I found in MOS was Note ID: 1356524.1.  This note deals mostly with how to identify the speed of the extraction process.  What I found interesting in this note is that Oracle is using the RBA to help calculate the amount of data being extracted.  With this note in hand, I felt comfortable in using the RBA to “estimate” the amount of data being applied by a replicat.
Note:  How to estimate Goldengate extract redo processing speed? (Doc ID 1356524.1)
A few sentences ago, I mentioned that I wanted to compare 2 RBAs to “estimate” the amount of data applied over a period of time.  In order to do this, I need to convert the RBA into meaningful number.
The following formulas I used to convert the RBA to megabytes and then into the metrics I wanted:

(($sec_rba - $first_rba)/(1024*1024))  <-  find the “estimated” size applied in MB
($mb_min*60)                           <- find the “estimate” size applied over an hour in MB
($mb_hr/(1024))                        <- find the “estimate” size applied in GB for an hour
($gb_hr*24)                            <- find the “estimate” size for a day in GB

Now the question was how can I grab this information from each replicat.  The information I needed could be found by doing a “info replicat <replicat>, detail” (The detail part is not really needed, just use it to list out all the associated trail files).    The output from the info command looks similar to this:
Info Replicat Output:

The thing to keep in mind is that I’m only concern about two lines in this output.  The first line is the “Log Read Checkpoint” and the second line that has the Date and RBA number.  Now in order to gather this information and do the calculations using the RBA, I wrote a Perl script.  The  for this basics of the script are below:

#!/usr/bin/perl -w
#
#Author: Bobby Curtis, Oracle ACE
#Copyright: 2014
#Title: gg_run_rate_from_rba.pl
#
use strict;
use warnings;
#Static Variables
my $gghome = "/u01/app/oracle/product/12.1.2/ogg";
my $outfile = "/tmp/gg_replicat_runrates.txt";
my $sleeptime = 60;
my $gguser = "c##ggate";
my $ggpass = "ggate";
my @process = ("replicat");
my $replicat;
my($date1,$curtrail1,$rba1);
my($date2,$curtrail2,$rba2);
my($rate_min, $rate_hr, $rate_gb_hr, $rate_gb_day);
#Program
open (RUNRATES, ">>$outfile") or die "Unable to open file";
foreach my $i(@process)
{
my @process_name = `ps -ef | grep dirprm | grep $i | grep -v grep | awk '{print \$14}'`;   
my @replicats = @process_name;
    foreach (@replicats)
    {
        $replicat = $_;
        chomp($replicat);
        check_replicat($gguser, $ggpass, $replicat);
        ($curtrail1,$date1,$rba1) = check_replicat();
        #print "$curtrail1 -> $date1 -> $rba1\n";
        sleep($sleeptime);
        check_replicat($gguser, $ggpass, $replicat);
        ($curtrail2,$date2,$rba2) = check_replicat();
        #print "$curtrail2 -> $date2 -> $rba2\n";
        calc_rate($rba1,$rba2);
        ($rate_min, $rate_hr, $rate_gb_hr, $rate_gb_day) = calc_rate();
       
        print RUNRATES "$replicat|$date1|$curtrail1|$rba1|$date2|$curtrail2|$rba2|$rate_min|$rate_hr|$rate_gb_hr|$rate_gb_day\n";
    }
}
close (RUNRATES);
#################
#Sub Programs
#################
sub check_replicat
{
my @buf = `$gghome/ggsci << EOF
dblogin userid $gguser\@pdb2 password $ggpass
info replicat $replicat, detail
EOF`;
my $curtrail;
my $date;
my $rba;
    foreach (@buf)
    {
        if (/Log Read Checkpoint/)
        {
            if (m/(\.\/\w+\/\w+)/g)
            {
                $curtrail = $1;
            }
        }
       
        if (/RBA/)
        {
            if (m/(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})/g)
            {
                $date = $1."-".$2."-".$3." ".$4.":".$5.":".$6;
            }
           
            if (m/RBA (\d+)/g)
            {
                $rba = $1;
            }   
        }
    }
    return($curtrail,$date,$rba);
} #end sub check_replicat
sub calc_rate
{
    my $first_rba = $rba1;
    my $sec_rba = $rba2;
 
    my $mb_min = (($sec_rba-$first_rba)/(1024*1024));
    my $mb_hr = ($mb_min*60);
    my $gb_hr = ($mb_hr/(1024));
    my $gb_day = ($gb_hr*24);
    return ($mb_min,$mb_hr,$gb_hr, $gb_day);
} #end sub calc_rate

This script is a bit longer than I like; however, it will capture all information required and then waits 60 seconds and gather the information again for the replicat it is working on. Once the first and second RBA are grabbed then the script writes the output to a flat file with the calculations for MB per min, MB per hour, GB per hour and GB per day.
Once the flat file has been written,  I can now use an external table that will allow me to view this data from SQL (see my other post on monitoring GG from SQL..here).  Using the external table, I can see what my run rates are from any SQL capable tool.  Below is a simple query to pull the data from the external table.
Note: Some numbers in the output may be negative.  This is due to the subtraction between RBA2 (smaller) and RBA1 (larger).

select
        repgroup as processgroup,
        to_char(date1, 'DD-MON-YY HH:MI:SS') snap1,
        curtrail1 as snap1_trail,
        rba1 as snap1_rba,
        to_char(date1, 'DD-MON-YY HH:MI:SS') snap2,
        curtrail2 as snap2_trail,
        rba2 as snap2_rba,
        rate_min_mb,
        rate_hr_mb,
        rate_hr_gb,
        rate_day_gb
from
  gghb.replicat_runrates
where
  repgroup = 'REP';
--Output (unformatted)--
PROCESSG SNAP1              SNAP1_TRAIL                     SNAP1_RBA SNAP2              SNAP2_TRAIL                     SNAP2_RBA RATE_MIN_MB RATE_HR_MB RATE_HR_GB RATE_DAY_GB
-------- ------------------ ------------------------------ ---------- ------------------ ------------------------------ ---------- ----------- ---------- ---------- -----------
REP      22-MAY-14 01:38:51 ./dirdat/rt000034                 2905346 22-MAY-14 01:38:51 ./dirdat/rt000034                 3197702         286      17130         17         401
REP      22-MAY-14 01:39:49 ./dirdat/rt000034                 3197702 22-MAY-14 01:39:49 ./dirdat/rt000034                 3521610         316      18979         19         445
REP      22-MAY-14 01:40:50 ./dirdat/rt000034                 3521610 22-MAY-14 01:40:50 ./dirdat/rt000034                 3802260         274      16444         16         385
REP      22-MAY-14 01:41:49 ./dirdat/rt000034                 3802260 22-MAY-14 01:41:49 ./dirdat/rt000034                 4112529         303      18180         18         426
REP      22-MAY-14 01:42:49 ./dirdat/rt000034                 4112529 22-MAY-14 01:42:49 ./dirdat/rt000034                 4463477         343      20563         20         482
 

Being able to use an external table to view run rates additional scripts can be written to report on what is going on within the Oracle GoldenGate apply process.  Allowing administrators a better understanding of what is going on within their environments.  At the same time, I think this information is valuable in the turning process of Oracle GoldenGate as environment grown.
Let me know your thoughts and comments on this, because it is always interesting to see how other organizations solve these issues as well.
Enjoy!
twitter: @dbasolved
blog: http://dbasolved.com

Please follow and like: