The Integrated Heartbeat of Oracle #GoldenGate #12c (12.2)

Last updated on October 5, 2019 GoldenGate Classic

With the announcement of Oracle GoldenGate 12c (12.2.0.1.0) at Oracle Open World this year, I was interested in hearing that Oracle has finally integrated the heartbeat process into the core functionality of GoldenGate. Setting up the heartbeat is always a fun thing to do with GoldenGate and I had hoped that this move to integrated it would bring benefits with it. To my dismay, I’m a little underwhelmed with the heartbeat that is integrated with GoldenGate 12c (12.2.0.1.0) now.

Note: If you would like to setup the older version of heartbeat, use My Oracle Support (MOS Note: 1299679.1) to reference the items needed.

Although, I’m not 100% sold on the new integrated heartbeat, it is a bit easier to setup than the process identified in the MOS note 1299679.1. And a little less confusing.

First thing that Oracle has done is add a few new GoldenGate parameters that are used to define heartbeat related items. I’ve used these parameters in the GLOBALS file, that way the changes effect the whole environment.

  • HEARTBEATTABLE <table_name> = this parameter allows you to define the heartbeat table you want to use; default name will be GG_HEARTBEAT
  • ENABLE_HEARTBEAT_TABLE | DISABLE_HEARTBEAT_TABLE = These parameters are used to either start or stop the heartbeat related items in the GoldenGate environment. (the parameters can be used in either GLOBALS, Extract or Replicat to enable/disable the heartbeat)

After making the changes to the GLOBALS file, then additional changes need to be made from GGSCI. If you were to issue a “help” command from the GGSCI prompt, you will notice there is 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.

To create the heartbeat table, you just need to simply 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. The below image shows all the objects that GoldenGate will create for heartbeat.

You will notice that the ADD HEARTBEAT command creates the following database objects:

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

Unlike the old way of creating a heartbeat, where GoldenGate used triggers; it is now using stored procedures to populated 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.

Closer Look at Heartbeat Table:

Taking a closer look at the heartbeat table, Oracle is using a lot of timestamps. This is similar to what is traditionally done in the older heartbeat setup. The one thing that should be noticed, is that you cannot customize the table to include DML and/or DDL statistics.

Note: I have not tried to customize the table, will test this a bit later to see if can add the items I would like to add, such as DML/DDL statistics.

—Heartbeat Table Structure—

CREATE TABLE “GGATE”.”GG_HEARTBEAT”
( “LOCAL_DATABASE” VARCHAR2(512 BYTE),
“HEARTBEAT_TIMESTAMP” TIMESTAMP (6),
“REMOTE_DATABASE” VARCHAR2(512 BYTE),
“INCOMING_EXTRACT” VARCHAR2(128 BYTE),
“INCOMING_ROUTING_PATH” VARCHAR2(4000 BYTE),
“INCOMING_REPLICAT” VARCHAR2(128 BYTE),
“INCOMING_HEARTBEAT_TS” TIMESTAMP (6),
“INCOMING_EXTRACT_TS” TIMESTAMP (6),
“INCOMING_ROUTING_TS” TIMESTAMP (6),
“INCOMING_REPLICAT_TS” TIMESTAMP (6),
“OUTGOING_EXTRACT” VARCHAR2(128 BYTE),
“OUTGOING_ROUTING_PATH” VARCHAR2(4000 BYTE),
“OUTGOING_REPLICAT” VARCHAR2(128 BYTE),
“OUTGOING_HEARTBEAT_TS” TIMESTAMP (6),
“OUTGOING_EXTRACT_TS” TIMESTAMP (6),
“OUTGOING_ROUTING_TS” TIMESTAMP (6),
“OUTGOING_REPLICAT_TS” TIMESTAMP (6),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “GGATE” ;

—End Heartbeat Table Structure–

The end result here is that you now have a table that is a “canned” table for strictly reporting lag within the GoldenGate environment. This is great for identifying network bandwidth issues; however, any other metrics you would like to track would need to be gathered by other means.

Closer Look at the GG_LAG View:

From looking at the views that are created with running ADD HEARTBEATTABLE from GGSCI, it is clear that Oracle took the logic that was in the old triggers and moved it into the views. The GG_LAG view also has a few additional information about path structure as well.

—Heartbeat View – GG_LAG —
CREATE OR REPLACE FORCE EDITIONABLE VIEW “GGATE”.”GG_LAG”
(“LOCAL_DATABASE”, “CURRENT_LOCAL_TS”, “REMOTE_DATABASE”, “INCOMING_HEARTBEAT_AGE”, “INCOMING_PATH”, “INCOMING_LAG”, “OUTGOING_HEARTBEAT_AGE”, “OUTGOING_PATH”, “OUTGOING_LAG”)
AS
SELECT local_database, current_local_ts, remote_database,
(extract(day from in_hb_age) * 86400 + extract (hour from in_hb_age) * 3600 + extract(minute from in_hb_age) * 60 + extract (second from in_hb_age)) incoming_heartbeat_age,
incoming_path,
(extract(day from in_lag) * 86400 + extract (hour from in_lag) * 3600 + extract(minute from in_lag) * 60 + extract (second from in_lag)) incoming_lag,
(extract(day from out_hb_age) * 86400 + extract (hour from out_hb_age) * 3600 + extract(minute from out_hb_age) * 60 + extract (second from out_hb_age)) outgoing_heartbeat_age,
outgoing_path, (extract(day from out_lag) * 86400 + extract (hour from out_lag) * 3600 + extract(minute from out_lag) * 60 + extract (second from out_lag)) outgoing_lag
FROM
(SELECT local_database, sys_extract_utc(systimestamp) current_local_ts, remote_database, (sys_extract_utc(systimestamp) – incoming_heartbeat_ts) in_hb_age,
(case when
incoming_routing_path is null
then (‘ ‘ || trim(incoming_extract) || ‘ ==> ‘ || trim(incoming_replicat) || ‘ ‘)
else (‘ ‘ || trim(incoming_extract) || ‘ ==> ‘ || substr(trim(incoming_routing_path),1,3734) || ‘ ==> ‘ || trim(incoming_replicat) || ‘ ‘) end) incoming_path,
(incoming_replicat_ts – incoming_heartbeat_ts) in_lag, (sys_extract_utc(systimestamp) – outgoing_heartbeat_ts) out_hb_age,
(case when outgoing_extract is null then null else (case when outgoing_routing_path is null
then (‘ ‘ || trim(outgoing_extract) || ‘ ==> ‘ || trim(outgoing_replicat) || ‘ ‘)
else (‘ ‘ || trim(outgoing_extract) || ‘ ==> ‘ || substr(trim(outgoing_routing_path),1,3734) || ‘ ==> ‘ || trim(outgoing_replicat) || ‘ ‘) end) end) outgoing_path,
(outgoing_replicat_ts – outgoing_heartbeat_ts) out_lag
FROM ggate.gg_heartbe
at) ;

—End Heartbeat View—

I like the view that are pre-packaged with the heartbeat setup; however, I think there is still additional information that is not gathered based on personal preferences. All the other items that are created with the ADD HEARTBEATTABLE are pretty standard database items.

Overall, the new heartbeat functionality is decent, but leaving me a bit under-whelmed with functionality that I would like to see as part of the heartbeat process. Hopefully, Oracle in the near future will flesh out more options for the heartbeat table to make it more statistical gathering tool within the environment (i.e. DML/DDL deltas).

Enjoy!

about.me: http://about.me/dbasolved

Please follow and like:
error
Tweet
Bobby Curtis

I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.

If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.

Follow if you like:
RSS
Follow by Email
Twitter
LinkedIn

Related articles

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.
(770) 876-5498