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:
image

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

About these ads

5 comments

  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.

    Cheers
    David

    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.

      Thanks

      Bobby

  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 SQL..here).  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

HeliFromFinland

Heli's Oracle thoughts

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Follow

Get every new post delivered to your Inbox.

Join 1,589 other followers

%d bloggers like this: