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;

Image 1:

Enjoy!
twitter: @dbasolved
blog: http://dbasolved.com

Please follow and like: