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”]
#Author: Bobby Curtis, Oracle ACE
my $gghome = "/oracle/app/product/12.1.2/oggcore_1";
my $outfile = "/tmp/gg_process_sqldev.txt";
my @buf = `$gghome/ggsci << EOF
open (GGPROC, ">$outfile") or die "Unable to open file";
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";
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
default directory TEMP
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘|’
MISSING FIELD VALUES ARE NULL
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.