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.
[sourcecode language=”perl” wraplines=”false”]
#!/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);
[/sourcecode]
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.
[sourcecode language=”sql” wraplines=”false”]
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;
[/sourcecode]
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.
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;
[/sourcecode]
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
Please follow and like:
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”.
Follow if you like:
