Wanna see some of my favorite GoldenGate scripts?

We use GoldenGate for replication from our hot server, to our warm, search and dr. Lots of data is replicated, using many different GoldenGate processes. From time to time, we will stop a process, halting replicatino for a period. After starting it back up, replication will be latent for a period. Given time, it will catch up. But in the meantime, I get worried about the status. I also need to know if processes are running or stopped.

To aid in this purpose, I have created some standard sql scripts that will provide me the data needed.

To see which items are latent, we can check the ACONPROCSB table within the DirectorDB, and produce latency times for each item that is 3 minutes latent.

--Get Latent > 3:00
SELECT
SUBSTRING(LASTCHECKPOINTLAG, 1, 2) as [hours],
SUBSTRING(LASTCHECKPOINTLAG, 4, 2) as [minutes],
SUBSTRING(LASTCHECKPOINTLAG, 7, 2) as [seconds],
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus,
LASTCHECKPOINTLAG
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastCheckPointLag > '00:03:00'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


To see which items are not running, we can check the ACONPROCSB table within the DirectorDB, and check the LastStatus value of each process.

--Get Non-Running
SELECT
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastStatus <> 'Running'
and ProcessName not like 'R1T2T%'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


I have as many of these scripts precoded to the appropriate director database, with the appropriate other 'where' clause values. I can quickly open up these scripts from within a project and execute them where they need to be executed, and tell the status of the systems. The results of this query are from the Director database, and it does contain a bit of latency itself, but its a quick and dirty solution to tell me status. Armed with this knowledge, I can easily tell where we stand.

I have used these queries to ease my mind on many many occasions. setting them up, testing them, and having them handy has helped me tremendously.