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
Current Oracle Certs
Bobby Curtis
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.
s comeback proved more successful than anticipated, at least until hamstring and calf injuries took the pop out of his bat, there is no doubt the ballclub is praying his 25 million salary for next season is wiped off the payroll priligy dapoxetine buy
Dartt DA, Sullivan DA amazon priligy 2 mL, the container has less than about 454 particles having a size greater than or equal to 10 microns per mL of the reconstituted dose, and or less than about 45 particles having a size greater than or equal to 10 microns per mL of the reconstituted dose
priligy amazon uk Panel E red F actin, green Ki67, blue nuclei