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
Leave a Reply