Heartbeat Table End-To-End Replication Flow

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.

Note:

The automatic heartbeat tables don’t populate the OUTGOING_% columns with data, when both the source and remote databases have the same name. To change the database name, use the utility DBNEWID. For details, see the DBNEWID Utility.

Column	Data Type	Description
`LOCAL_DATABASE`	`VARCHAR2`	Local database where the replication time from the remote database is measured.
`HEARTBEAT_TIMESTAMP`	`TIMESTAMP(6)`	The point in time when a timestamp is generated at the remote database.
`REMOTE_DATABASE`	`VARCHAR2`	Remote database where the timestamp is generated
`INCOMING_EXTRACT`	`VARCHAR2`	Name of the primary Extract (capture) at the remote database
`INCOMING_ROUTING_PATH`	`VARCHAR2`	Name of the secondary Extract (pump) at the remote database
`INCOMING_REPLICAT`	`VARCHAR2`	Name of the Replicat on the local database.
`INCOMING_HEARTBEAT_TS`	`TIMESTAMP(6)`	Final timestamp when the information is inserted into the `GG_HEARTBEAT` table at the local database.
`INCOMING_EXTRACT_TS`	`TIMESTAMP(6)`	Timestamp of the generated timestamp is processed by the primary Extract at the remote database.
`INCOMING_ROUTING_TS`	`TIMESTAMP(6)`	Timestamp of the generated timestamp is processed by the secondary Extract at the remote database.
`INCOMING_REPLICAT_TS`	`TIMESTAMP(6)`	Timestamp of the generated timestamp is processed by Replicat at the local database.
`OUTGOING_EXTRACT`	`VARCHAR2`	Bidirectional/N-way replication: Name of the primary Extract on the local database.
`OUTGOING_ROUTING_PATH`	`VARCHAR2`	Bidirectional/N-way replication: Name of the secondary Extract on the local database.
`OUTGOING_REPLICAT`	`VARCHAR2`	Bidirectional/N-way replication: Name of the Replicat on the remote database.
`OUTGOING_HEARTBEAT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Final timestamp when the information is inserted into the table at the remote database.
`OUTGOING_EXTRACT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp of the generated timestamp is processed by the primary Extract on the local database.
`OUTGOING_ROUTING_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp of the generated timestamp is processed by the secondary Extract on the local database.
`OUTGOING_REPLICAT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp of the generated timestamp is processed by Replicat on the remote database.
`INCOMING_REPLICAT_LW_CSN`	`VARCHAR2`	-
`INCOMING_EXTRACT_HEARTBEAT_CSN`	`VARCHAR2`	-
`INCOMING_EXTRACT_RESTART_CSN`	`VARCHAR2`	-
`INCOMING_EXTRACT_RESTART_TS`	`TIMESTAMP(6)`	-

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.

Column	Data Type	Description
`LOCAL_DATABASE`	`VARCHAR2`	Local database where the end-to-end lag is measured.
`HEARTBEAT_RECEIVED_TS`	`TIMESTAMP(6)`	Point in time when a timestamp from the remote database receives at the local database.
`REMOTE_DATABASE`	`VARCHAR2`	Remote database where the timestamp is generated.
`INCOMING_EXTRACT`	`VARCHAR2`	Name of the primary Extract on the remote database.
`INCOMING_ROUTING_PATH`	`VARCHAR2`	Name of the secondary Extract of the remote database.
`INCOMING_REPLICAT`	`VARCHAR2`	Name of the Replicat on the local database.
`INCOMING_HEARTBEAT_TS`	`TIMESTAMP(6)`	Final timestamp when the information is inserted into the `GG_HEARTBEAT_HISTORY` table on the local database.
`INCOMING_EXTRACT_TS`	`TIMESTAMP(6)`	Timestamp when the generated timestamp is processed by the primary Extract on the remote database.
`INCOMING_ROUTING_TS`	`TIMESTAMP(6)`	Timestamp when the generated timestamp is processed by the secondary Extract on the remote database.
`INCOMING_REPLICAT_TS`	`TIMESTAMP(6)`	Timestamp when the generated timestamp is processed by Replicat on the local database.
`OUTGOING_EXTRACT`	`VARCHAR2`	Bidirectional/N-way replication: Name of the primary Extract from the local database.
`OUTGOING_ROUTING_PATH`	`VARCHAR2`	Bidirectional/N-way replication: Name of the secondary Extract from the local database.
`OUTGOING_REPLICAT`	`VARCHAR2`	Bidirectional/N-way replication: Name of the Replicat on the remote database.
`OUTGOING_HEARTBEAT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Final timestamp when the information is persistently inserted into the table of the remote database.
`OUTGOING_EXTRACT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp when the generated timestamp is processed by the primary Extract on the local database.
`OUTGOING_ROUTING_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp when the generated timestamp is processed by the secondary Extract on the local database.
`OUTGOING_REPLICAT_TS`	`TIMESTAMP(6)`	Bidirectional/N-way replication: Timestamp when the generated timestamp is processed by Replicat on the remote database.
`REPLICAT_LOW_WATERMARK_CSN`	`String`	This column is populated by Replicat when it processes this heartbeat record. It populates this column with its current low watermark (LWM) when it processes this record. This allows us to choose a LOGBSN from a heartbeat record which is as of the Replicat LWM.
`SOURCE_EXTRACT_HEARTBEAT_CSN`	`String`	This column is populated by Extract and contains the source commit SCN for the heartbeat transaction in the source database. The heartbeat job on the source database cannot populate this value as it will not know the commit SCN apriori.
`SOURCE_EXTRACT_RESTART_CSN`	`String`	This column will be populated by Extract and will contain the current LOGBSN when Extract processes this particular heartbeat record. The heartbeat job on the source database will not populate this value.
`SOURCE_EXTRACT_RESTART_CSN_TS`	`TIMESTAMP`	This column will be populated by Extract and will contain the redo timestamp in UTC that corresponds to the current LOGBSN when Extract processes this particular heartbeat record. The heartbeat job on the source database will not populate this value.

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.

Column	Data Type	Description
`LOCAL_DATABASE`	`VARCHAR2`	Local database where the end-to-end replication lag from the remote database is measured.
`CURRENT_LOCAL_TS`	`TIMESTAMP(6)`	Current timestamp of the local database.
`REMOTE_DATABASE`	`VARCHAR2`	Remote database where the timestamp is generated.
`INCOMING_HEARTBEAT_AGE`	`NUMBER`	The age of the most recent heartbeat received from the remote database.
`INCOMING_PATH`	`VARCHAR2`	Replication path from the remote database to the local database with Extract and Replicat components.
`INCOMING_LAG`	`NUMBER`	Replication lag from the remote database to the local database. This is the time where the heartbeat where generated at the remote database minus the time where the information was persistently inserted into the table at the local database.
`OUTGOING_HEARTBEAT_AGE`	`NUMBER`	The age of the most recent heartbeat from the local database to the remote database.
`OUTGOING_PATH`	`VARCHAR2`	Replication Path from Local database to the remote database with Extract and Replicat components
`OUTGOING_LAG`	`NUMBER`	Replication Lag from the local database to the remote database. This is the time where the heartbeat where generated at the local database minus the time where the information was persistently inserted into the table at the remote database.
`REMOTE_EXTRACT_RESTART_CSN`	`String`	Source Extract restart position.
`REMOTE_DATABASE DB_UNIQUE_NAME`	`String`	Remote database unique name is displayed. If no unique name exists, then the `DB_NAME` value is displayed.
`REMOTE_EXTRACT_RESTART_CSN_TIME`	`Timestamp`	Timestamp associated with source Extract redo position.
`REMOTE_DB_OLDEST_OPEN_TXN_AGE`	`Timestamp`	Age of the oldest open transaction at the source database that Extract is currently processing. This column can be calculated as `SYSTIMESTAMP - REMOTE_EXTRACT_RESTART_TIME`.
`LOCAL_REPLICAT_LWM_CSN`	`String`	Low watermark CSN of the local Replicat when it processed the heartbeat.

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.

Column	Data Type	Description
`LOCAL_DATABASE`	`VARCHAR2`	Local database where the end-to-end replication lag from the remote database is measured.
`HEARTBEAT_RECEIVED_TS`	`TIMESTAMP(6)`	Point in time when a timestamp from the remote database receives on the local database.
`REMOTE_DATABASE`	`VARCHAR2`	Remote database where the timestamp is generated.
`DB_NAME`	String	Remote database name.
`DB_UNIQUE_NAME`	String	Remote database unique name. If the database unique name doesn't exist, then the `DB_NAME` and `DB_UNIQUE_NAME` will be same. In a switchover to standby scenario, the `db_unique_name` will change but the `db_name` and replication path remain the same
`INCOMING_HEARTBEAT_AGE`	`NUMBER`	The age of the heartbeat table.
`INCOMING_PATH`	`VARCHAR2`	Replication path from the remote database to local database with Extract and Replicat components.
`INCOMING_LAG`	`NUMBER`	Replication lag from the remote database to the local database. This is the time where the heartbeat was generated at the remote database minus the time where the information was persistently inserted into the table on the local database.
`OUTGOING_HEARTBEAT_AGE`	`NUMBER`
`OUTGOING_PATH`	`VARCHAR2`	Replication path from local database to the remote database with Extract and Replicat components.
`OUTGOING_LAG`	`NUMBER`	Replication lag from the local database to the remote database. This is the time where the heartbeat was generated at the local database minus the time where the information was persistently inserted into the table on the remote database.
`REMOTE_EXTRACT_RESTART_CSN`	`String`	Source Extract restart position.
`REMOTE_EXTRACT_RESTART_CSN_TIME`	`TIMESTAMP`	Timestamp associated with source Extract redo position.
`REMOTE_DB_OLDEST_OPEN_TXN_AGE`	`TIMESTAMP`	Age of the oldest open transaction at the source database that Extract is currently processing. This column can be calculated as: `SYSTIMESTAMP - REMOTE_EXTRACT_RESTART_TIME`
`LOCAL_REPLICAT_LWM_CSN`	`String`	Low watermark CSN of the local Replicat when it processed the heartbeat.
`INCOMING_EXTRACT_LAG`	-	-
`INCOMING_ROUTINE_LAG`	-	-
`INCOMING_REPLICAT_READ_LAG`	-	-
`INCOMING_REPICAT_LAG`	-	-
`OUTGOING_EXTRACT_LAG`	-	-
`OUTGOING_ROUTINE_LAG`	-	-
`OUTGOING_REPLICAT_READ_LAG`	-	-
`OUTGOING_REPLICAT_LAG`	-	-