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
use strict;
use warnings;

#Static Variables

my $gghome = "/oracle/app/product/12.1.2/oggcore_1";
my $outfile = "/tmp/gg_process_sqldev.txt";

my @buf = `$gghome/ggsci << EOF
info all

open (GGPROC, ">$outfile") or die "Unable to open file";
foreach (@buf)
        no warnings 'uninitialized';
        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
            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:


twitter: @dbasolved



  1. David Allan · · Reply

    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.


    1. 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.



  2. […] within SQL Developer is needed.  Using XML extensions, I’ve been able to leverage monitoring GoldenGate from SQL into a working extension.  The extension is not perfect and continues to need some work.  […]

  3. […] 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  Using the external table, I can see what my run rates are from any SQL capable tool.  Below is […]

  4. […] in more ways to monitor GoldenGate, be sure to check out my older posts, Bobby Curtis’ posts (1 & 2), and his upcoming presentation at Collaborate 14. Coincidentally, he is sitting with me on […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: