The end-to-end replication process for heartbeat tables relies on using the Oracle GoldenGate trail format. The process is as follows:

Add a heartbeat table to each of your databases with the ADD HEARTBEATTABLE command. Add the heartbeat table to all source and target instances and then restart existing Oracle GoldenGate processes to enable heartbeat functionality. Depending on the database, you may or may not be required to create or enable a job to populate the heartbeat table data.

See the following sample:

GGSCI>DBLOGIN {[SOURCEDB data_source] |[, database@host:port] |USERID {/ | userid}[, PASSWORD password] 
[algorithm ENCRYPTKEY {keyname | DEFAULT}] |USERIDALIAS alias [DOMAIN domain]|[SYSDBA | SQLID sqlid][SESSIONCHARSET character_set]}
GGSCI>ADD HEARTBEATTABLE

(Optional) For Oracle Databases, you must ensure that the Oracle DBMS_SCHEDULER is operating correctly as the heartbeat update relies on it. You can query the DBMS_SCHEDULER by issuing:

select START_DATE, LAST_START_DATE, NEXT_RUN_DATE 
from dba_scheduler_jobs 

Where job_name ='GG_UPDATE_HEARTBEATS';

Then look for valid entries for NEXT_RUN_DATE, which is the next time the scheduler will run. If this is a timestamp in the past, then no job will run and you must correct it.

A common reason for the scheduler not working is when the parameter job_queue_processes is set too low (typically zero). Increase the number of job_queue_processes configured in the database with the ALTER SYSTEM SET JOB_QUEUE_PROCESSES = ##; command where ## is the number of job queue processes.

Run an Extract, which on receiving the logical change records (LCR) checks the value in the OUTGOING_EXTRACT column.

• If the Extract name matches this value, the OUTGOING_EXTRACT_TS column is updated and the record is entered in the trail.

• If the Extract name does not match then the LCR is discarded.

• If the OUTGOING_EXTRACT value is NULL, it is populated along with OUTGOING_EXTRACT_TS and the record is entered in the trail.

The Pump or Distribution server on reading the record, checks the value in the OUTGOING_ROUTING_PATH column. This column has a list of distribution paths.

If the value is NULL, the column is updated with the current group name (and path if this is a Distribution server),"*", update the OUTGOING_ROUTING_TS column, and the record is written into its target trail file.

If the value has a "*" in the list, then replace it with group name[:pathname],"*"', update the OUTGOING_ROUTING_TS column, and the record is written into its target trail file. When the value does not have a asterisk (*) in the list and the pump name is in the list, then the record is sent to the path specified in the relevant group name[:pathname],"*"' pair in the list. If the pump name is not in the list, the record is discarded.

Run a Replicat, which on receiving the record checks the value in the OUTGOING_REPLICAT column.

• If the Replicat name matches the value, the row in the heartbeat table is updated and the record is inserted into the history table.

• If the Replicat name does not match, the record is discarded.

• If the value is NULL, the row in the heartbeat and heartbeat history tables are updated with an implicit invocation of the Replicat column mapping.

  Automatic Replicat Column Mapping:

  REMOTE_DATABASE 		= LOCAL_DATABASE
  INCOMING_EXTRACT	 	= OUTGOING_EXTRACT
  INCOMING_ROUTING_PATH      = OUTGOING_ROUTING_PATH with "*"    removed
  INCOMING_REPLICAT		= @GETENV ("GGENVIRONMENT", "GROUPNAME")
  INCOMING_HEARTBEAT_TS	= HEARTBEAT_TIMESTAMP
  INCOMING_EXTRACT_TS	= OUTGOING_EXTRACT_TS
  INCOMING_ROUTING_TS	= OUTGOING_ROUTING_TS
  INCOMING_REPLICAT_TS          = @DATE ('UYYYY-MM-DD HH:MI:SS.FFFFFF','JTSLCT',@GETENV ('JULIANTIMESTAMP'))
  LOCAL_DATABASE	= REMOTE_DATABASE
  OUTGOING_EXTRACT	= INCOMING_EXTRACT
  OUTGOING_ROUTING_PATH	= INCOMING_ROUTING_PATH
  OUTGOING_HEARTBEAT_TS     = INCOMING_HEARTBEAT_TS
  OUTGOING_REPLICAT	= INCOMING_REPLICAT
  OUTGOING_HEARTBEAT_TS	= INCOMING_HEARTBEAT_TS
  

There is just one column for OUTGOING_ROUTING_TS. If a record passes through multiple pump before being applied by a Replicat, each pump will overwrite the OUTGOING_ROUTING_TS column so that the pumps lag that is calculated is not specific to a single pump and refers to the lag across all the pumps specified in PUMP_PATH.

Additional Considerations:

Computing lags as the heartbeat flows through the system relies on the clocks of the source and target systems to be set up correctly. It is possible that the lag can be negative if the target system is ahead of the source system. The lag is shown as a negative number so that you are aware of their clock discrepancy and can take actions to fix it.

The timestamp that flows through the system is in UTC. There is no time zone associated with the timestamp so when viewing the heartbeat tables, the lag can be viewed quickly even if different components are in different time zones. You can write any view you want on top of the underlying tables; UTC is recommended.

All the heartbeat entries are written to the trail in UTF-8.

The outgoing and incoming paths together uniquely determine a row. Meaning that if you have two rows with same outgoing path and a different incoming path, then it is considered two unique entries.

Heartbeat Table Details

The GG_HEARTBEAT table displays timestamp information of the end-to-end replication time and the timing information at the different components primary and secondary Extract and Replicat.

In a unidirectional environment, only the target database contains information about the replication lag. That is the time when a record is generated at the source database and becomes visible to clients at the target database.

The GG_HEARTBEAT_HISTORY table displays historical timestamp information of the end-to-end replication time and the timing information at the different components primary and secondary Extract and Replicat.

In a unidirectional environment, only the destination database contains information about the replication lag.

Timestamps are managed in UTC time zone. That is the time when a record is generated at the source database and becomes visible to clients at the target database.

The GG_LAG view displays information about the replication lag between the local and remote databases.

In a unidirectional environment, only the destination database contains information about the replication lag. The lag is measured in seconds.

The GG_LAG_HISTORY view displays the history information about the replication lag history between the local and remote databases.

In a unidirectional environment, only the destination database contains information about the replication lag.

The unit of the lag units is in seconds.