PowerShell Way: Automating Golden Gate Replication Monitoring on Windows

Recently I had the opportunity to work with our Tandem Team to implement an Automate Monitoring and Alerting solution while replicating data from Tandem Data source to SQL Server 2012 using Oracle Golden Gate Replication technology. The Golden Gate replication pushes data 24/7 basis to SQL Server and once in a while, the latency increases or sometimes one or more “REPLICAT” stops working. As this is a mission critical system, we need to learn any malfunction almost immediately.

Golden Gate on Windows:

As the GG (Golden Gate) replication sits on Windows Server and runs all its services, it is quite easy to investigate the GG replication status manually. There are sets of commands provided by the GG mechanism to understand the replication configuration, status and the health of each or all REPLICATs. For example, as we are interested on replication status we need to utilize the GG Command “STATUS ALL” to see the “ABENDED”, “STOPPED”, “LAG” or “CHKPT” status of each REPLICAT.

Say for example, the GG Replication is running Windows on D:\GGReplication, and to see the status we need to do the following.

1.      Use CMD.exe and go to the “D:\GGReplication” folder;

2.      Run “GGSCI” to get into the GG replication;

3.      Execute the Command “STATUS ALL” or “INFO ALL”.

PowerShell Way:

There is no direct command to grab status information of the Golden Gate replication. However, we can utilize PowerShell “Invoke-Expression” to perform the above task from inside PowerShell sessions. Following is the PowerShell function I have developed to monitor the Golden Gate replication from PowerShell.

PowerShell Function to monitor Golden Gate Replication:

<####################################

# Golden Gate Status Monitor

# Developed: Sarjen Haque

#####################################>

function Get-Replicat

{

     $string = "CMD /c echo Status All | D:\GGReplication\GGSCI"

     $result = Invoke-Expression $String

     $raw = $result -match 'REPLICAT'

    

     [StringSplitOptions]$Options = "RemoveEmptyEntries"

    

     # loop through each line and break

     foreach ($line in $raw)

     {

           $wrd = $line.Split(" ", $Options)

           $lg = $wrd[3].Split(":")

           $tm = $wrd[4].Split(":")

                    

           $result = [Ordered]@{

                    "Program" = $wrd[0];

                    "Status" = $wrd[1];

                    "Name" = $wrd[2];

                    "LagMin" = [int]$lg[0] * 60 + [int]$lg[1];

                    "Lag" = $wrd[3];

                    "ChkPt" = $wrd[4];

                    "ChkPtMin" = [int]$tm[0] * 60 + [int]$tm[1];

           }

           $obj = New-Object -TypeName PSObject -Property $result

           Write-Output $obj

          

     }

}

We have our PowerShell function, now we can use this function in various way to collect the Golden Gate replication status.  Following are some examples:

Example #1: Collect all “REPLICAT” status

Get-Replicat | ft -AutoSize

Example #2: Collect “REPLICAT” status if the LAG is greater than 15 minutes or if a REPLICAT is “ABENDED”

Get-Replicat | where-object { $_.LagMin -ge 15 -or $_.Status -eq 'ABENDED' }|ft -AutoSize

Example #3: Collect “REPLICAT” status if “ABENDED”

Get-Replicat | where-object { $_.Status -eq 'ABENDED' }|ft -AutoSize

Example #4: Collect “REPLICAT” status if stopped

Get-Replicat | where-object { $_.Status -eq 'STOPPED' }|ft -AutoSize

Automating Golden Gate Process Monitoring:

By utilizing the above “Get-Replicat” function, we can easily automate the process monitoring and send alerts if a performance condition exists based on provided criteria. A Windows Schedule task can be created to execute that PowerShell script every x minutes to check the “REPLICAT” status.

You can download this entire scripted Golden Gate monitoring solution on Windows from my shared Dropbox folder. The output of the script is similar to the one below.

Steps to follow:

1. Download the script from http://bit.ly/1cZNScb.
2. Create or import a windows task (provided along with the script).
3. Change the Location of the GG binaries location in the function “Get-Replicat”.
4. Change the smtp server name and email address for mailing functionality.

Conclusion: I have used one my favorite PowerShell Guru’s (Don Jones, MVP, www.powershell.org) library function for HTML reporting. I believe that someone will benefit from this simple function. Let me know if you find it useful.