Golden Gate HEARTBEAT TABLE and its improvements in GG 19.1
Posted by FatDBA on June 23, 2021
Hi Mates,
This week I was working on one Golden Gate upgrade from 12.2 to 19.1, and have noticed few new features & improvements related to the HEARTBEAT TABLE. If you are yet to explore about it, you should do it now!. It’s a fantastic feature which provides some interesting delay/wait related information which LAG command failed to provide. Starting from GG 12.2 Oracle has a built in Heartbeat table which presents some really cool information and houses historical information too.
Let me show you why is it good to have them in your GG setup – Let’s quickly add the table to your setup, connect with your gg user and add the table.
GGSCI (canttowin.ontadomain as gger@dixitdb) 31> add heartbeattable
2021-06-22 14:42:57 INFO OGG-14001 Successfully created heartbeat seed table ""gger"."GG_HEARTBEAT_SEED"".
2021-06-22 14:42:57 INFO OGG-14089 Successfully tracking extract restart position with heartbeat table ""gger"."GG_HEARTBEAT_SEED"".
2021-06-22 14:43:04 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table ""gger"."GG_HEARTBEAT_SEED"".
2021-06-22 14:43:04 INFO OGG-14000 Successfully created heartbeat table ""gger"."GG_HEARTBEAT"".
2021-06-22 14:43:04 INFO OGG-14089 Successfully tracking extract restart position with heartbeat table ""gger"."GG_HEARTBEAT"".
2021-06-22 14:43:04 INFO OGG-14033 Successfully added supplemental logging for heartbeat table ""gger"."GG_HEARTBEAT"".
2021-06-22 14:43:04 INFO OGG-14016 Successfully created heartbeat history table ""gger"."GG_HEARTBEAT_HISTORY"".
2021-06-22 14:43:04 INFO OGG-14089 Successfully tracking extract restart position with heartbeat table ""gger"."GG_HEARTBEAT_HISTORY"".
2021-06-22 14:43:04 INFO OGG-14086 Successfully disabled partitioning for heartbeat history table ""gger"."GG_HEARTBEAT_HISTORY"".
2021-06-22 14:43:04 INFO OGG-14023 Successfully created heartbeat lag view ""gger"."GG_LAG"".
2021-06-22 14:43:04 INFO OGG-14024 Successfully created heartbeat lag history view ""gger"."GG_LAG_HISTORY"".
2021-06-22 14:43:05 INFO OGG-14003 Successfully populated heartbeat seed table with "DIXITDB".
2021-06-22 14:43:05 INFO OGG-14004 Successfully created procedure ""gger"."GG_UPDATE_HB_TAB"" to update the heartbeat tables.
2021-06-22 14:43:05 INFO OGG-14017 Successfully created procedure ""gger"."GG_PURGE_HB_TAB"" to purge the heartbeat history table.
2021-06-22 14:43:06 INFO OGG-14005 Successfully created scheduler job ""gger"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables.
2021-06-22 14:43:06 INFO OGG-14018 Successfully created scheduler job ""gger"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.
Let’s check some details about it.
GGSCI (canttowin.ontadomain as gger@dixitdb) 45> info heartbeattable
HEARTBEAT table gger.gg_heartbeat exists.
HEARTBEAT table gger.gg_heartbeat_seed exists.
HEARTBEAT table gger.gg_heartbeat_history exists.
HEARTBEAT table gger.gg_heartbeat supplemental logging ENABLED.
HEARTBEAT table gger.gg_heartbeat_seed supplemental logging ENABLED.
HEARTBEAT table gger.gg_heartbeat_history partitioning DISABLED.
Frequency interval: 60 seconds.
Purge frequency interval: 1 days.
Retention time: 30 days.
Now when the table is successfully created, let’s check lag/delay stats both using LAG command and through new dynamic views created for the purpose.
GGSCI (canttowin.ontadomain as gger@dixitdb) 32> lag
Lag Information From Heartbeat Table
LAG AGE FROM TO PATH
6.97s 24.24s DHAVALDB DIXITDB DXQUEX ==> DXQUDP ==> DXONRP
4.82s 59.12s DIXITDB DHAVALDB DXONEX ==> DXONDP ==> DXQUERP
SQL> col local_database format a10
col current_local_ts format a30
col remote_database format a10
col INCOMING_HEARTBEAT_AGE for a30
col incoming_path format a30
col incoming_lag format 999,999.999999
select LOCAL_DATABASE, CURRENT_LOCAL_TS, REMOTE_DATABASE,
INCOMING_HEARTBEAT_AGE, INCOMING_PATH, INCOMING_LAG
from GGER.GG_LAG;
LOCAL_DATA CURRENT_LOCAL_TS REMOTE_DAT INCOMING_HEARTBEAT_AGE INCOMING_PATH INCOMING_LAG
---------- ------------------------------ ---------- ---------------------- ------------------------------ ---------------
DHAVALDB 22-JUN-21 06.45.42.581334 PM DIXITDB ########## DXONEX ==> DXONDP ==> DXQUERP 4.754468
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
select local_database, heartbeat_received_ts, remote_database, incoming_path, incoming_lag from gger.gg_lag_history;
LOCAL_DATA HEARTBEAT_RECEIVED_TS REMOTE_DAT INCOMING_PATH INCOMING_LAG
---------- ------------------------------ ---------- -------------------------------- ---------------
DHAVALDB 22-JUN-21 06.43.19.624247 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 12.401984
DHAVALDB 22-JUN-21 06.44.11.776929 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 5.167206
DHAVALDB 22-JUN-21 06.45.10.898463 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 4.754468
DHAVALDB 22-JUN-21 06.46.10.996575 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 4.823870
DHAVALDB 22-JUN-21 06.47.13.057097 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 6.839796
DHAVALDB 22-JUN-21 06.48.13.163290 PM DIXITDB DXONEX ==> DXONDP ==> DXQUERP 7.016913
Now coming back to the learnings from this upgrade, I have learned two new things about GG heartbeat table which are specific to GG version 19.1
- Stating with Oracle Golden Gate 19.1 the command UPGRADE HEARTBEATTABLE is available to alter heartbeat table generated by the ADD HEARTBEATTABLE command using a previous Oracle Golden Gate release. So, if you have just completed your upgrade of GG, run the UPGRADE HEARTBEATTABLE command to add extra columns for tables and lag views. These extra columns are used to track the Extract restart position
GGSCI (canttowin.ontadomain as gger@dixitdb) 44> UPGRADE HEARTBEATTABLE
- NOADDTRANDATA option of ADD HEARTBEATTABLE has been deprecated in 19.1
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Leave a Reply