This post covers the Heartbeat table of Oracle Goldengate (12.2).
Monitoring lag in GoldenGate has always been an important part of monitoring GoldenGate. Lags are reported in several ways. When using the ggsci LAG command only the latest lag is reported with a 1-second resolution providing the last reported lag. This isn’t trustworthy and nothing significant could be derived out of it!.
In the past, GoldenGate implementer has created heartbeat tables manually.
In Oracle GoldenGate 12.2 a built-in heartbeat table feature has been added. This heartbeat table allows for more accurate heartbeats and heartbeat history. It works by creating an artificial transaction every minute that contains timing information that is used for heartbeats.
The heartbeat tables are accessed via views that provide accurate lag data and lag history.
Note: If you would like to set up the older version of the heartbeat, use Oracle Support (MOS Note: 1299679.1) to reference the items needed.
Issue a “help” command from the GGSCI prompt, to notice there are now options to add a heartbeat table (similar to setting up the checkpoint table).
The image below shows the commands that are now available in GGSCI.
GGSCI> help
To create the heartbeat table, you need to run ADD HEARTBEATTABLE from GGSCI after logging into the database as the GoldenGate user.
After logging into the database and issuing the ADD HEARTBEATTABLE command, GoldenGate will create all the items needed for the heartbeat process to work.
ADD HEARTBEATTABE on target
GGSCI > add heartbeattable
Above command creates heartbeat table with the default name GG_HEARTBEAT under GOLDENGATE User. You can change the name of heartbeat table by defining under GLOBALS File of Goldengate.
You should see below output
2017-11-14 12:18:51 INFO OGG-14001 Successfully created heartbeat seed table [“GG_HEARTBEAT_SEED”].
2017-11-14 12:18:51 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table [“GG_HEARTBEAT_SEED”].
2017-11-14 12:18:51 INFO OGG-14000 Successfully created heartbeat table [“GG_HEARTBEAT”].
2017-11-14 12:18:51 INFO OGG-14033 Successfully added supplemental logging for heartbeat table [“GG_HEARTBEAT”].
2017-11-14 12:18:51 INFO OGG-14016 Successfully created heartbeat history table [“GG_HEARTBEAT_HISTORY”].
2017-11-14 12:18:51 INFO OGG-14023 Successfully created heartbeat lag view [“GG_LAG”].
2017-11-14 12:18:51 INFO OGG-14024 Successfully created heartbeat lag history view [“GG_LAG_HISTORY”].
2017-11-14 12:18:51 INFO OGG-14003 Successfully populated heartbeat seed table with [PDB2].
2017-11-14 12:18:51 INFO OGG-14004 Successfully created procedure [“GG_UPDATE_HB_TAB”] to update the heartbeat tables.
2017-11-14 12:18:51 INFO OGG-14017 Successfully created procedure [“GG_PURGE_HB_TAB”] to purge the heartbeat history table.
2017-11-14 12:18:51 INFO OGG-14005 Successfully created scheduler job [“GG_UPDATE_HEARTBEATS”] to update the heartbeat tables.
2017-11-14 12:18:51 INFO OGG-14018 Successfully created scheduler job [“GG_PURGE_HEARTBEATS”] to purge the heartbeat history table.
You will notice that the ADD HEARTBEAT command creates the following database objects under Goldengate user ggadmin:
Tables:
<heartbeat_table>_SEED (default GG_HEARTBEAT_SEED)
<heartbeat_table> (default GG_HEARTBEAT)
<heartbeat_table>_HISTORY (default GG_HEARTBEAT_HISTORY)
Views:
GG_LAG
GG_LAG_HISTORY
Stored Procedures:
GG_UPDATE_HB_TAB
GG_PURGE_HB_TAB
Scheduler Jobs:
GG_UPDATE_HEARTBEATS
GG_PURGE_HEARTBEATS
Check out this post which covers Overview & Components of Oracle GoldenGate (software for real-time data integration and replication in heterogeneous IT Systems).
ADD HEARTBEATTABLE on Source
GGSCI > add heartbeattable
2017-11-14 01:07:51 INFO OGG-14001 Successfully created heartbeat seed table [“GG_HEARTBEAT_SEED”].
2017-11-14 01:07:52 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table [“GG_HEARTBEAT_SEED”].
2017-11-14 01:07:52 INFO OGG-14000 Successfully created heartbeat table [“GG_HEARTBEAT”].
2017-11-14 01:07:52 INFO OGG-14033 Successfully added supplemental logging for heartbeat table [“GG_HEARTBEAT”].
2017-11-14 01:07:52 INFO OGG-14016 Successfully created heartbeat history table [“GG_HEARTBEAT_HISTORY”].
2017-11-14 01:07:52 INFO OGG-14023 Successfully created heartbeat lag view [“GG_LAG”].
2017-11-14 01:07:52 INFO OGG-14024 Successfully created heartbeat lag history view [“GG_LAG_HISTORY”].
2017-11-14 01:07:52 INFO OGG-14003 Successfully populated heartbeat seed table with [PDB1].
2017-11-14 01:07:52 INFO OGG-14004 Successfully created procedure [“GG_UPDATE_HB_TAB”] to update the heartbeat tables.
2017-11-14 01:07:52 INFO OGG-14017 Successfully created procedure [“GG_PURGE_HB_TAB”] to purge the heartbeat history table.
2017-11-14 01:07:52 INFO OGG-14005 Successfully created scheduler job [“GG_UPDATE_HEARTBEATS”] to update the heartbeat tables.
2017-11-14 01:07:52 INFO OGG-14018 Successfully created scheduler job [“GG_PURGE_HEARTBEATS”] to purge the heartbeat history table.
Once the heartbeat table was created it should have been an easy matter to go to the target system and query the ggadmin.gg_heartbeat and ggadmin.gg_heartbeat_history tables to see the automated heartbeats.
Managing Heartbeat Data
As seen above the heartbeat table is created via the ADD HEARTBEATTABLE command within GGSCI. By default a heartbeat is generated every minute, kept for 30 days then purged.
The frequency of the heartbeat, the history retention and how often the purge process runs is configurable. This is done via the ALTER HEARTBEATTABLE command.
Viewing Heartbeat Data
Viewing the heartbeat table is done via the two heartbeat views; GG_LAG and GG_LAG_history. These views provide information on lags for each set of ext -> pump -> replicat that is configured.
This information, as well as the history, is valuable for monitoring the performance of the GoldenGate configuration.
GG_LAG View
col local_database format a10 col current_local_ts format a30 col remote_database format a10 col incoming_path format a30 col incoming_lag format 999,999.999999 SQL>select local_database, current_local_ts, remote_database, incoming_path, incoming_lag from gg_lag;
|
GG_LAG_HISTORY View
set pagesize 100 col local_database format a10 col heartbeat_received_ts format a30 col remote_database format a10 col incoming_path format a32 col incoming_lag format 999,999.999999 SQL> select local_database, heartbeat_received_ts, remote_database, incoming_path, incoming_lag from gg_lag_history;
|
The output of the lag history can monitor lags over long periods of time and be used for alerting and monitoring. The lag history can be imported into a spreadsheet and graphed.
Unlike the old way of creating a heartbeat where GoldenGate used triggers; it is now using stored procedures to populate the tables. Then just like the old way, a scheduler job is used to execute the stored procedures to update or purge the tables. The main difference compared to the old heartbeat way is that there are views used to calculate the lag between processes.
Related/Further Reading:
- Oracle GoldenGate 12c Overview & Components: Manager | Extract | Collector | Replicat | Data Pump
- Oracle GoldenGate: Supplemental Logging & Its Importance
- [Video] Oracle GoldenGate 12c Microservices Architecture MA: Components, Documentation & Installation
- Oracle GoldenGate 12c: Step by step Activity Guides
- Oracle GoldenGate 19c Administration
Next Task For You
Want to move ahead in your career and want to get a higher Earning Job?
Get 7+ Courses for DBAs & Apps DBA in a bundle program and learn from the Industry’s best Experts.
Leave a Reply