If Extract abends when a long-running transaction is open, it can seem to take a long time to recover when it is started again. To recover its processing state, Extract must search back through the online and archived logs (if necessary) to find the first log record for that long-running transaction. The farther back in time that the transaction started, the longer the recovery takes, in general, and Extract can appear to be stalled.

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:

DBLOGIN USERIDALIAS alias  [DOMAIN domain]|[SYSDBA | SQLID sqlid][SESSIONCHARSET character_set]}

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 Distribution Service 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, then the column is updated with the current group name (and path if this is a Distribution Service),"*", 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 distribution path 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 distribution path name is not in the list, then 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
  

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.

The HEARTBEAT_TIMESTAMP column in the heartbeat seed table must be updated periodically by a database job. The default heartbeat interval is 1 minute and this interval can be specified or overridden using from the command line or the Administration Service web interface.

For Oracle Database, the database job is created automatically.

For all other supported databases, you must create background jobs to update the heartbeat timestamp using the database specific scheduler functionality.

See ADD HEARTBEATTABLE, ALTER HEARTBEATTABLE for details on updating the heartbeat table.

The heartbeat history table is purged periodically using a job. The default interval is 30 days and this interval can be specified or overridden using a command line inteface such as Admin Client or the Administration Service web interface.

For Oracle Database, the database job is created automatically.

For all other supported databases, you must create background jobs to purge the heartbeat history table using the database specific scheduler functionality.

Oracle recommends that you:

• Use the same heartbeat frequency on all the databases to makes diagnosis easier.

• Adjust the retention period if space is an issue.

• Retain the default heartbeat table frequency; the frequency set to be 30 to 60 seconds gives the best results for most workloads.

• Use lag history statistics to collect lag and age information.

You can use the heartbeat table commands to control the Oracle GoldenGate automatic heartbeat functionality as follows.