Friday, December 23, 2011

Monitoring Goldengate through sql

I have been working on implementing GoldenGate.  Golden Gate works with Oracle, but I wanted to be able to monitor it's status within an Oracle session, and then use a database link to remotely query the status.. Once I have all this in place, I want to create a web page through Apex, that does a union of all my GG environments onto a single screen.. real time ! nifty Eh.

This is how I went about doing it..

1)  The basis for it all is the "info all" command executed within GGSCI.  The output looks like this.

GGSCI (orclbox) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

in this example you can see that the manager is running, but the extract is stopped.  I took the output of this command and created my own command script..

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 
export ADR_HOME=/u01/app/oracle/diag/rdbms/${DB_UNIQUE_NAME}/${ORACLE_SID}
export PATH=${ORACLE_HOME}/bin:${PATH_ORIG} 
export PATH=$PATH:/dbfs/gg/ggs11
 cd /dbfs/gg/ggs11
cd /dbfs/gg/ggs11 
ggsci </tmp/ggsci.log 2>/tmp/ggsci.log
info all

cat /tmp/ggsci.log  | grep EXTRACT
cat /tmp/ggsci.log  | grep MANAGER
cat /tmp/ggsci.log  | grep REPLICAT

The output of this script is the 2 lines from above that show the statuses of those processes.

EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

So now for step 2... We have the statuses we are looking for as the output of a script.. What to do ?  External tables with a preprocessor (sorry I believe this is an 11.2 feature)..

First create a directory and put your script in that directory.  (this is within oracle). I called mine SP_COPY

Here is how I defined my table creation to read the output of the above script.

create table bgrenn.ggsci_status
(gg_type varchar(20),
 status  varchar(20),
 lag_time       varchar(20),
  checkpoint    varchar(20)
 organization external
(type oracle_loader
  default directory SP_COPY
  access parameters
   records delimited by newline
   preprocessor SP_COPY:''
   badfile SP_COPY: 'ggsci_status.bad'
   logfile SP_COPY: 'ggsci_status.log'
    fields terminated by whitespace
     missing field values are null
   location ('')
reject limit unlimited;


Now select against this table and you will see the columns from the output of your script appear as columns in the table (2 rows for this example).

Finally .... Step 3.. create a database link to this database and do a select * from ggsci_status@mylink.

There you go.  How to dynamically show the status of Golden Gate processes through a database link on a remote database.

NOTE : If the database is a RAC cluster with GG running on only one node, you need to specify the SID to ensure you are looking at the correct node running GG.


1 comment:

  1. This is really cool. Thanks for sharing. I was looking for the same thing and found some interesting new dynamic views in Oracle database 11.2:
    Alas, they are not well documented and they appear to be completely empty in our currently running instance.
    One small suggestion: replace the last three lines of the script with:
    egrep 'EXTRACT|MANAGER|REPLICAT' /tmp/ggsci.log

    Only one subprocess spawned instead of three.