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:
- Download the script from http://bit.ly/1cZNScb.
- Create or import a windows task (provided along with the script).
- Change the Location of the GG binaries location in the function “Get-Replicat”.
- 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.
