Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

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

  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
  1. NOADDTRANDATA option of ADD HEARTBEATTABLE has been deprecated in 19.1

Hope It Helped!
Prashant Dixit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: