How do we monitor Oracle GoldenGate?

Update Aug-16:

The Perl-based custom monitoring script i has been a hit and therefore some small bugs/enhancements need to be addressed. Check this blog from time to time as I will start to update the gg_monitor.pl and the corresponding documentation to go with it as several enhancements have been made as a result of customer requests.


Original Blog Content:

This is a question that comes up repeatedly, and by that statement, I mean all the time!

I hope this blog will help highlight the options available from Oracle, provide enough information for you to plan a solution (ie, pick an approach or a product or two from the “pack”, test and validate the solution), and offer a couple of free methods that can help monitor Oracle GoldenGate by themselves or in conjunction with any of the Oracle products that will be highlighted.

Oracle GoldenGate is “file-system” centric. This statement does not imply that it does file-system replication (as it does not); it simply means that installation and setup/confguration, management and maintenance, files created and/or used within its processing, and monitoring of status/lag is generally tracked by files in the file system and accomplished via command-line utilities through an operating system shell.

The main tool of course is the “ggsci” utility; this is Oracle GoldenGate’s key command line interface utility used for administration/configuration and status/monitoring of Oracle GoldenGate processes.  Other tools may be involved in monitoring or troubleshooting, such as operating system utilities: ps, top, lsof, strace/prace, ls, tcpdump, etc to look at processes, files or process existence/activity on the system.  The OS tools of course depend on the platforms involved and the utilities available on the platform. 

Oracle GoldenGate has a number of other command line utilities (one being “logdump”), but these are not generally used in monitoring or inquiring about status information, they are used more so for troubleshooting or capacity planning.  We will not cover those tools here as they need their own space.

However, all the tools mentioned provide information on Oracle GoldenGate processes and files outside of a database. This is what we mean by “file-system centric”.  Even though Oracle GoldenGate may be replicating data from one database to another database, there is very little information that can be attained about its status (for classic processes) from inside the databases themselves (of course you have the checkpoint table in the target, if configured for that), we are simply making a generalization that monitoring Oracle GoldenGate is done outside the databases involved.

The statements above are true regardless of whether you are replicating Oracle-to-Oracle, something-to-Oracle, or Non-Oracle-to-Non-Oracle.  And, whether you are using a classic type Oracle GoldenGate process or integrated processes in Oracle databases (we will not go into detail about those here). This is changing with the new integrated processes (integrated capture/integrated apply).
Integrated processes do maintain views/tables in the database in which status information and run time information can be obtained. We will cover them in a different post for just integrated process types.

The point, most monitoring of Oracle GoldenGate is done outside the databases involved when using classic processes.

I highlight the monitoring solutions available and will also cover how to get a database-level view of process status, lag information and/or transaction statistics from inside the database.

The following products are available from Oracle for monitoring Oracle GoldenGate instances;  all of the following are part of the Oracle GoldenGate Management Pack. The Management Pack is highly recommended and being enhanced with every release by Oracle. It is a very feature rich monitoring tool (plug-in for OEM).

•        Oracle Enterprise Manager (OEM) Plug-in
•   called the OGG Plug-in from the Management Pack for Oracle GoldenGate
•        Oracle GoldenGate Monitor
• Referred to as the “standalone monitor”
• This tool can be used if not using or have OEM in the environment
  such as non-Oracle replication environments *Sybase, SQLServer, DB2
• OEM and the OEM Management pack is highly recommended
•        Oracle GoldenGate Director
•   only product that allows remote administration of Oracle GoldenGate instances
•    the new OEM plugin version is suppose to include this functionality

Other “free” methods of monitoring Oracle GoldenGate instance include the following:

•       Oracle GoldenGate Heartbeat
• Free, set of database objects and mappings to provide a heartbeat transaction
• Can be customized to make a solution that meaningful for you
• See the following MOS Notes
• Using A Heartbeat Table To Monitor LAG Between Source And Target 
• Doc ID 968710.1
• Oracle GoldenGate Best Practices: Heartbeat Table for Monitoring 
• Doc ID: 1299679.1
• Heartbeat will be embedded in future Oracle releases
• Oracle continues to enhance and add to the product
•       Custom Script(s)
•          Look for the free Perl script (gg_monitor.pl) you can use to monitor Oracle GoldenGate
•          Any script (written in Perl, shell such as Bourne/Korn/C, Java or a Windows scripting or any other language available on the platform) that you write to perform the “ggsci” commands and parse the resulting output according to your logic for determining status/performance
•        Scripts may also check on processes, files or system utilization at the operating system or file system level

Another valuable product that is not used for primarily for monitoring Oracle GoldenGate processes directly, but nonetheless is important tool for ensuring data synchronization (monitoring for logical corruption as a result of replication) and continued synchronization of data between source and target is:

•            Veridata

Veridata (another great product) is not licensed as part of the Management Pack for Oracle GoldenGate. It is a separate product and is therefore licensed separately from the Management Pack for Oracle GoldenGate (please be aware of this, don't let this stop you from using it though).

Each product/approach has its own page used to cover each solution in more detail and highlight the solution requirements, components and configuration involved and also highlight the benefits and drawbacks.  We will not cover the installation or usage details of each product here, as there is plenty of material available for that already.

Overall, thorough monitoring Oracle GoldenGate instances generally involves a hybrid approach, meaning, uses a combination of the products/approaches.

Most often, the heartbeat and a custom script is always put into the implementation (as a minimum in all uni or bi-directional implementations).  This keeps replication hot (live) and gives a database view of replication status from the target side (checkpoint table can also be used, but be cautious and never alter the checkpoint table structure or records), and offers some configurable thresholds for monitoring the processes and their status/lag/checkpoint delays. The heartbeat itself is not very useful for diagnosing or troubleshooting process failures, it is merely a quick status check which is an indication of delays or process failure. You can use it on a regular interval and be alerted when the interval updates have not been meet, and someone needs to dive in to investigate.  As customers grow their GoldenGate implementation base (and we hope you do) and in more complex implementations or in environments with many implementations, we tend to roll out one or two of the other products from Oracle mentioned above. These products offer a great visual view of the GoldenGate instances, their components/topology and how they integrate together (dependencies).