Golden Gate HeartBeat Table Microservices Architecture
Oracle GoldenGate heartbeat table is completely automatic and now integrated from 12.2 version onwards. These heartbeat table can be configured easily and provides automatic heartbeat transactions every minute and with auto purge capability. Using GoldenGate, the heartbeat works by replicating a heartbeat transaction across each Extract -> Distribution Path -> Replicat. Neither the extract nor replicat parameter files contain these heartbeat tables.
Monitoring end-to-end replication lag can be accomplished using the default automatic heartbeat table functionality. Heartbeats are sent automatically from source databases as replication by updating records in a heartbeat seed table and a heartbeat table, and building a heartbeat history table. The target databases insert or update these heartbeat records into the heartbeat table.
The GG_HEARTBEAT table contains lag information for each component of Extract and Replicat. So when it comes to diagnosing a GoldenGate performance issue, we can quickly determine where the bottleneck is using these heartbeat table and view information.
STEP 1 : add schema name : GLOBALS
Login to Administration Service – Configuration – Parameter files -> GLOBALS
Select Edit and make sure to enter the ggschema. Here in this case ggschema -> ggadmin
STEP 2 : Add HeartBeat Table
HeartBeat tables can be added through UI or adminclient.
While using the UI, Login to the database using the connect and click on + icon next to heartbeat
Perform the similar steps on Target database. Here in this case, the source database is Standalone 12.2 Database and Target is Autonomous Database.
OGG (https://10.0.0.103 Oracle-Deployment as ORCL_SI@ORCL) 5> add heartbeattable
INFO OGG-14001 Successfully created heartbeat seed table ["GG_HEARTBEAT_SEED"].
INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table ["GG_HEARTBEAT_SEED"].
INFO OGG-14000 Successfully created heartbeat table ["GG_HEARTBEAT"].
INFO OGG-14033 Successfully added supplemental logging for heartbeat table ["GG_HEARTBEAT"].
INFO OGG-14016 Successfully created heartbeat history table ["GG_HEARTBEAT_HISTORY"].
INFO OGG-14023 Successfully created heartbeat lag view ["GG_LAG"].
INFO OGG-14024 Successfully created heartbeat lag history view ["GG_LAG_HISTORY"].
INFO OGG-14003 Successfully populated heartbeat seed table with [TRG12C].
INFO OGG-14004 Successfully created procedure ["GG_UPDATE_HB_TAB"] to update the heartbeat tables.
INFO OGG-14017 Successfully created procedure ["GG_PURGE_HB_TAB"] to purge the heartbeat history table.
INFO OGG-14005 Successfully created scheduler job ["GG_UPDATE_HEARTBEATS"] to update the heartbeat tables.
INFO OGG-14018 Successfully created scheduler job ["GG_PURGE_HEARTBEATS"] to purge the heartbeat history table.
OGG (https://10.0.0.103 Oracle-Deployment as ORCL_SI@ORCL) 6> info heartbeattable
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat exists.
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat_seed exists.
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat_history exists.
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat supplemental logging ENABLED.
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat_seed supplemental logging ENABLED.
2023-02-01T02:47:50Z INFO OGG-08100 HEARTBEAT table ggadmin.gg_heartbeat_history partitioning DISABLED.
2023-02-01T02:47:50Z INFO OGG-08100 Frequency interval: 60 seconds.
2023-02-01T02:47:50Z INFO OGG-08100 Purge frequency interval: 1 days.
2023-02-01T02:47:50Z INFO OGG-08100 Retention time: 30 days.
Tables:
- heartbeat_table_SEED : (default GG_HEARTBEAT_SEED)
- heartbeat_table : (default GG_HEARTBEAT)
- heartbeat_table_HISTORY : (default GG_HEARTBEAT_HISTORY)
- GG_LAG
- GG_LAG_HISTORY
- GG_UPDATE_HB_TAB
- GG_PURGE_HB_TAB
- GG_UPDATE_HEARTBEATS
- GG_PURGE_HEARTBEATS
STEP 3 : Check Scheduler Jobs
On Source and Target Database check dba_scheduler jobs are running using the below SQL
SQL> set lines 200
SQL> col START_DATE format a40
SQL> col LAST_START_DATE format a40
SQL> col NEXT_RUN_DATE format a40
SQL> select START_DATE, LAST_START_DATE, NEXT_RUN_DATE from dba_scheduler_jobs where job_name ='GG_UPDATE_HEARTBEATS';
START_DATE LAST_START_DATE NEXT_RUN_DATE
---------------------------------------- ---------------------------------------- ----------------------------------------
01-FEB-23 01.14.51.670185 AM ETC/GMT 01-FEB-23 03.09.51.050290 PM ETC/GMT 01-FEB-23 03.10.51.000000 PM ETC/GMT
STEP 4 : Check the Lag Information Using HeartBeat Table
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTSWING INTEGRATED 00:00:02 00:00:04
REPLICAT RUNNING RPSWING NONINTEGRATED 00:00:00 00:00:06
OGG (https://10.0.0.103 Oracle-Deployment as ORCL_SI@ORCL) 6> lag RPSWING
No Extract groups exist.
Sending GETLAG request to Replicat group RPSWING ...
Last record lag 3 seconds.
At EOF, no more records to process
For more information on HeartBeat table check out the Oracle documentation here.
That’s it. Now you can measure the lag and set the alerts based on heartbeat table…..Hope you found this article useful.Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle..