Oracle GoldenGate Best Practices: Heartbeat Table for Monitoring Lag times

Introduction

This document is intended to help you implement the best practice for creating a heartbeat process that can be used to determine where and when lag is developing between a source and target system.

Main Article

This document will walk you thru the step-by-step process of creating the necessary tables and added table mapping statements needed to keep track of processing times between a source and target database.  Once the information is added into the data flow, the information is then stored into target tables that can be analyzed to determine when and when the lag is being introduced between the source and target systems.The heartbeat tables can be used to identify the following information:

  • Where lag is developing in each of the processes
  • When lag is developing by comparing the commit timestamp with the current time that the record pass thru each process.

These tables also allow you to:

  • Create a history of lag to determine what time of day lag develops.
  • Create a history to identify if lag is increasing over time.
  • Identify if a upstream process is stopped for any reason.
  • Monitor DML and DDL statistics

You can find the complete document on the Oracle Support site stored as Document ID:1299679.1

 

OGG Best Practice – heartbeat table using DBMS_SCHEDULER- V11_0 ID1299679.1

Comments

  1. Hi – I have a question on a situation I recently encountered and wanted to know how heartbeat table would help.

    extract — call it ext1 ( captures dml changes for 5 tables)
    replicat — call it rep1,rep2, rep3,..rep8 ( 8 range split replicats to apply changes on target db for same 5 tables)

    — no DDLs & no deletes are captured

    now..we noticed that one/two among the 8 replicats at time would fall behind..say rpt2 & rpt6 would show 2 hour lag (Lag at checkpoint column), but others would be fine.

    stats on all replicats would show almost same speed.(more or less).

    if I implement heartbeat table for each replicat..how would it solve anything or give more insight?

    In my example abv, if rpt2 & rpt6 were slow..wouldnt the heartbeat transactions also get queued up behind and waiting to get applied?

    Or am I missing something? if rpt2 applies latest heartbeat skipping the pending transactions, that would mean bigger data-integrity issue ( Tx/SCN -100 getting applied before Tx-1)?

    I guess, I am at a loss to understand what extra value does this heartbeat table bring-in (& how), that’s not conveyed by existing by “info all” & stat reports?

    Can you please explain?

    Thanks,
    Ven

Add Your Comment