GoldenGate 12.2 Tutorial – Configure the Heartbeat Table

With GoldenGate 12.2, the Oracle GoldenGate heartbeat table is now integrated and I feel that this is  one of the best new features in the 12.2 release. This heartbeat table is easily configured and provides automatic heartbeat transactions each minute and includes an auto purge feature. The heartbeat works by replicating a heartbeat transaction across each Extract -> Pump -> Replicat that is configured in GoldenGate. These heartbeat tables are not present in the extract, pump and replicat parameter files. In order to view the results of the heartbeats Oracle has provided a heartbeat view and a heartbeat history view.

 

Oracle GoldenGate 12.2 Training Classes - Feed your Brain!

The heartbeat table is designed to capture lag times between the source and target. The lag time is the time that it takes from the time a transaction is committed on the source to when it is committed on the target. The lag time is essentially the delay incurred when GoldenGate is replicating. The longer the lag, the more out of sync the databases are. With bi-directional replication, the more lag, the more potential time for a collision to occur. Knowing the lag time is very important to understanding what is happening with your GoldenGate configuration.

In order to configure the GoldenGate automatic heartbeat feature you must first add GGSCHEMA <GoldenGate Schema> to the GLOBALS file by editing the file. The GLOBALS file might look something like this:

GGSCHEMA ggadmin
ALLOWOUTPUTDIR /u02/ggtrails

Note: Once you add the GGSCHEMA parameter to the GLOBALS file the manager must be restarted.

The second step is to issue the command: add heartbeattable command as shown here:

Note: You must login to the database before issuing the add heartbeattable command.

GGSCI> dblogin useridalias ggadm
Successfully logged into database.

GGSCI (gg21c as ggadmin@gg21cdb) 8> add heartbeattable

2016-07-11 11:36:52 INFO OGG-14001 Successfully created heartbeat seed table [“GG_HEARTBEAT_SEED”].

2016-07-11 11:36:52 INFO OGG-14032 Successfully added supplemental logging for heartbeat seed table [“GG_HEARTBEAT_SEED”].

2016-07-11 11:36:52 INFO OGG-14000 Successfully created heartbeat table [“GG_HEARTBEAT”].

2016-07-11 11:36:52 INFO OGG-14033 Successfully added supplemental logging for heartbeat table [“GG_HEARTBEAT”].

2016-07-11 11:36:52 INFO OGG-14016 Successfully created heartbeat history table [“GG_HEARTBEAT_HISTORY”].

2016-07-11 11:36:52 INFO OGG-14023 Successfully created heartbeat lag view [“GG_LAG”].

2016-07-11 11:36:52 INFO OGG-14024 Successfully created heartbeat lag history view [“GG_LAG_HISTORY”].

2016-07-11 11:36:52 INFO OGG-14003 Successfully populated heartbeat seed table with [GG21CDB].

2016-07-11 11:36:53 INFO OGG-14004 Successfully created procedure [“GG_UPDATE_HB_TAB”] to update the heartbeat tables.

2016-07-11 11:36:53 INFO OGG-14017 Successfully created procedure [“GG_PURGE_HB_TAB”] to purge the heartbeat history table.

2016-07-11 11:36:53 INFO OGG-14005 Successfully created scheduler job [“GG_UPDATE_HEARTBEATS”] to update the heartbeat tables.

2016-07-11 11:36:53 INFO OGG-14018 Successfully created scheduler job [“GG_PURGE_HEARTBEATS”] to purge the heartbeat history table.

Note: You must login to the database before issuing the add heartbeattable command.

Run the same commands on the target system.

In addition to seeing the heartbeat tables and views in the ggadmin schema you will also see that database jobs have been created to manage the heartbeat table.

Tables
GG_HEARTBEAT
GG_HEARTBEAT_SEED
GG_HEARTBEAT_HISTORY

Views
GG_LAG
GG_LAG_HISTORY

Ggadmin scheduler jobs
GG_PURGE_HEARTBEATS
GG_UPDATE_HEARTBEATS

By default, the heartbeat data is kept for 30 days and has an interval of 60 seconds. These values can be modified with the ALTER HEARTBEATTABLE command. In order to see the current values, use the INFO HEARTBEATTABLE command as shown here:

GGSCI > info heartbeattable

HEARTBEAT table ggadmin.gg_heartbeat exists.

HEARTBEAT table ggadmin.gg_heartbeat_seed exists.

HEARTBEAT table ggadmin.gg_heartbeat_history exists.

Frequency interval: 60 seconds.

Purge frequency interval: 1 days.

Retention time: 30 days.

The heartbeat works by the scheduler updating a record in the GGADMIN.GG_HEARTBEAT_SEED table which is replicated to the target system. You can see these by running stats against the extract and replicat processes. The result is something like this:

Extracting from GGADMIN.GG_HEARTBEAT_SEED to GGADMIN.GG_HEARTBEAT_SEED:
*** Total statistics since 2016-07-11 11:36:56 ***
Total inserts 0.00
Total updates 286.00
Total deletes 0.00
Total discards 0.00
Total ignores 1.00
Total operations 286.00

End of Statistics.

Once the heartbeat table has been running for a while you can query the gg_lag_history table and see the lag over time. An example is shown here using the following query:

set pagesize 200 linesize 200
col heartbeat_received_ts format a30
col incoming_path format a24
col incoming_lag format 999,999.999
ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;
SELECT heartbeat_received_ts, incoming_path, incoming_lag FROM gg_lag_history;

A sample result of this query is shown here:

HEARTBEAT_RECEIVED_TS INCOMING_PATH INCOMING_LAG
—————————— ———————— ————
12-JUL-16 03.00.12.357163 AM ESOE ==> PSOE ==> RSOE         6.670
12-JUL-16 03.01.10.087177 AM ESOE ==> PSOE ==> RSOE         4.888
12-JUL-16 03.02.10.030191 AM ESOE ==> PSOE ==> RSOE         4.911
12-JUL-16 03.03.12.194142 AM ESOE ==> PSOE ==> RSOE         5.951
12-JUL-16 03.04.11.016185 AM ESOE ==> PSOE ==> RSOE         6.025
12-JUL-16 03.05.13.171268 AM ESOE ==> PSOE ==> RSOE         7.077
12-JUL-16 03.06.14.217224 AM ESOE ==> PSOE ==> RSOE         8.153
12-JUL-16 03.07.13.037193 AM ESOE ==> PSOE ==> RSOE         7.218
12-JUL-16 03.08.15.198291 AM ESOE ==> PSOE ==> RSOE         8.279
12-JUL-16 03.09.11.809165 AM ESOE ==> PSOE ==> RSOE         6.304
12-JUL-16 03.10.11.751199 AM ESOE ==> PSOE ==> RSOE         6.398
12-JUL-16 03.11.12.801258 AM ESOE ==> PSOE ==> RSOE         6.410
12-JUL-16 03.12.11.632087 AM ESOE ==> PSOE ==> RSOE         6.508
12-JUL-16 03.13.13.792216 AM ESOE ==> PSOE ==> RSOE         7.579
12-JUL-16 03.14.13.733941 AM ESOE ==> PSOE ==> RSOE         8.593

Beware that the heartbeat_received_ts might be in UTC unless you set up the timezone for the database to be your local timezone.

If you have the timezone of the database set to UTC you can use the following query to view the data in your timezone. This took a little trial and error and is manual but it works.

SELECT CAST(FROM_TZ(CAST(heartbeat_received_ts AS TIMESTAMP), ‘UTC’) AT TIME ZONE ‘America/Chicago’ AS Date)

‘ CT’ as incoming_heartbeat_ts, incoming_path, incoming_lag FROM gg_lag_history;

A sample result of this query is shown here:

INCOMING_HEARTBEAT_TS INCOMING_PATH INCOMING_LAG
———————- ———————— ————
2016-07-12 04:02:10 CT ESOE ==> PSOE ==> RSOE 6.325
2016-07-12 04:03:10 CT ESOE ==> PSOE ==> RSOE 6.374
2016-07-12 04:04:12 CT ESOE ==> PSOE ==> RSOE 8.466
2016-07-12 04:05:12 CT ESOE ==> PSOE ==> RSOE 7.568
2016-07-12 04:06:12 CT ESOE ==> PSOE ==> RSOE 6.591
2016-07-12 04:07:10 CT ESOE ==> PSOE ==> RSOE 5.680
2016-07-12 04:08:13 CT ESOE ==> PSOE ==> RSOE 7.713

This allows me to view in the native timezone.

This data can be displayed with excel or other tools in order to view lag time over time.

Lag.jpg

By knowing the lag times over time periods you can tell whether it is consistent or not. Often network traffic varies and lag times suffer. Since lag times are critical to GoldenGate performance it is an important metric.