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:
image
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 = &quot;/u01/app/oracle/product/12.1.2/ogg&quot;;
my $outfile = &quot;/tmp/gg_replicat_runrates.txt&quot;;
my $sleeptime = 60;
my $gguser = &quot;c##ggate&quot;;
my $ggpass = &quot;ggate&quot;;
my @process = (&quot;replicat&quot;);
my $replicat;
my($date1,$curtrail1,$rba1);
my($date2,$curtrail2,$rba2);
my($rate_min, $rate_hr, $rate_gb_hr, $rate_gb_day);
#Program
open (RUNRATES, &quot;&gt;&gt;$outfile&quot;) or die &quot;Unable to open file&quot;;
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 &quot;$curtrail1 -&gt; $date1 -&gt; $rba1\n&quot;;
        sleep($sleeptime);
        check_replicat($gguser, $ggpass, $replicat);
        ($curtrail2,$date2,$rba2) = check_replicat();
        #print &quot;$curtrail2 -&gt; $date2 -&gt; $rba2\n&quot;;
        calc_rate($rba1,$rba2);
        ($rate_min, $rate_hr, $rate_gb_hr, $rate_gb_day) = calc_rate();
       
        print RUNRATES &quot;$replicat|$date1|$curtrail1|$rba1|$date2|$curtrail2|$rba2|$rate_min|$rate_hr|$rate_gb_hr|$rate_gb_day\n&quot;;
    }
}
close (RUNRATES);
#################
#Sub Programs
#################
sub check_replicat
{
my @buf = `$gghome/ggsci &lt;&lt; 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.&quot;-&quot;.$2.&quot;-&quot;.$3.&quot; &quot;.$4.&quot;:&quot;.$5.&quot;:&quot;.$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:
Comments
  • This design is wicked! You obviously know how to keep a reader amused.
    Between your wit and your videos, I was almost moved to start my
    own blog (well, almost…HaHa!) Excellent job. I really loved what you had to say,
    and more than that, how you presented it. Too cool!

  • I was curious if you ever thought of changing the layout of your website?
    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect with it better.
    Youve got an awful lot of text for only having 1
    or 2 images. Maybe you could space it out
    better?

  • I’d like to thank you for the efforts you’ve put in penning this site.

    I’m hoping to view the same high-grade blog posts by
    you later on as well. In truth, your creative writing abilities has motivated me to get my very own website now 😉

  • After I initially left a comment I seem to have clicked
    on the -Notify me when new comments are added-
    checkbox and from now on each time a comment is added I recieve 4 emails
    with the same comment. Perhaps there is a way you are able to remove me from that service?
    Thank you!

  • Howdy just wanted to give you a quick heads up. The words in your article seem to be running off the
    screen in Safari. I’m not sure if this is a format
    issue or something to do with browser compatibility but I thought I’d post
    to let you know. The style and design look great though!
    Hope you get the issue solved soon. Many thanks

  • Thanks a lot for sharing this with all of us you really recognise what you’re talking
    about! Bookmarked. Kindly additionally visit my website =).
    We can have a link exchange arrangement among us

Leave a Reply

Your email address will not be published. Required fields are marked *

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.