Monitor Oracle Golden Gate from SQL
One of my presentations at Collaborate 14 this year revolves around how many different ways there are to monitor Oracle Golden Gate. As I was putting the presentation together, I was listing out the different ways for monitoring. I have covered a few of the ways already in earlier posts. What I want to show you here is how to execute a simple “info all” command and see the results from SQL*Plus or SQL Developer using SQL.
First, a script (shell, Perl, etc..) needs to be written to write the output of the “info all” command to a text file. In this case, I’m going to write the text file in /tmp since I’m on Linux.
#!/usr/bin/perl -w # #Author: Bobby Curtis, Oracle ACE #Copyright: 2014 #Title: gg_monitor_sqldev.pl # use strict; use warnings; #Static Variables my $gghome = "/oracle/app/product/12.1.2/oggcore_1"; my $outfile = "/tmp/gg_process_sqldev.txt"; #Program my @buf = `$gghome/ggsci << EOF info all EOF`; open (GGPROC, ">$outfile") or die "Unable to open file"; foreach (@buf) { if(/MANAGER/||/JAGENT/||/EXTRACT/||/REPLICAT/) { no warnings 'uninitialized'; chomp; my ($program, $status, $group, $lagatchkpt, $timesincechkpt) = split(" "); if ($group eq "") { $group = $program; } if ($lagatchkpt eq "" || $timesincechkpt eq "") { $lagatchkpt = "00:00:00"; $timesincechkpt = "00:00:00"; } print GGPROC "$program|$status|$group|$lagatchkpt|$timesincechkpt\n"; } } close (GGPROC);
Next, is the text file needs to be placed into a table to be read by SQL*Plus or SQL Developer. External Tables are great for this.
create directory TEMP as '/tmp'; grant read on directory TEMP to PUBLIC; drop table ggate.os_process_mon; create table ggate.os_process_mon ( process char(15), status char(15), group char(15), lagatchk char(15), timelastchk char(15) ) organization external (type oracle_loader default directory TEMP access parameters ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL ( process char(15), status char(15), ggroup char(15), lagatchk char(15), timelastchk char(15) ) ) location ('gg_process_sqldev.txt') ); select * from ggate.os_process_mon;
Lastly, with these two pieces in place, I can now select the status from SQL*Plus or SQL Developer using SQL. Image 1 shows a sample from my testing environment, I’m building.
Image 1:
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”.


Nice post Bobby. It’d be nice to take it further and show use of the external table preprocessor – which will give you live status when the external table is queried.
Cheers
David
David,
Thanks for the comment. Please stay tuned, this post was only meant to be an introduction into how to monitor OGG with SQL. Shortly, the community will be working on taking this further.
Thanks
Bobby