Heartbeat table

Owned by Arjen Visser
Feb 03, 2015

The DBRSCOMMON_HEARTBEAT in the dbvrep schema can be used to monitor the replication.  The table is updated by determination of the MINE_HEARTBEAT_TIME parameter.  The parameter default is set to 10 seconds by can be set according to the needs of the customer.  Every 10 seconds the DBRSCOMMON_HEARTBEAT table would be updated with new information such as the timestamp and SCN.  This parameter also signals that the MINE process is now ready to do remote commands.

An example SQL that can be run against the Heartbeat table is:

 SELECT 
 DDC_ID,
 TO_CHAR(WALLCLOCK_DATE,'YYYY-MM-DD HH24:MI:SS') WALLCLOCK_DATE,
 TO_CHAR(MINE_DATE,'YYYY-MM-DD HH24:MI:SS') MINE_DATE,
 (WALLCLOCK_DATE - MINE_DATE) * 24 * 60 * 60 "Difference in Sec",
 SOURCE_SCN,
 MINE_SCN,
 SOURCE_SCN - MINE_SCN "SCN Difference"
 FROM DBRSCOMMON_HEARTBEAT;

If this SQL is run on the Mine (or source) database then the query returns the following information:

  • WALLCLOCK DATE. This is the actual current database time.
  • MINE_DATE. This is the current time at which Mine is mining the data
  • Difference in Sec. This is the time difference between WALLCLOCK_DATE and MINE_DATE. This is in essence the lag of the Mine process between the actual changes in the database and the changes being mined. Note that some of the lag will be due to MINE_HEARTBEAT_TIME parameter which is set to 10 seconds by default.
  • SOURCE SCN. This is the current SCN from the Mine database
  • MINE SCN. This is the current SCN of the mining process
  • SCN Difference. This is the SCN difference between SOURCE_SCN and MINE_SCN

If this SQL is run on the Apply (or target) database then the query can be compared with the SQL that is run on the source database. The following columns can be compared:

  • MINE_DATE. The MINE_DATE from the source database versus the MINE_DATE from the target database will indicate the replication lag in time between the Mine and Apply process
  • MINE_SCN. The MINE_SCN from the source database versus the MINE_SCN from the target database will indicate the replication lag in SCN between the Mine and Apply process