Monitor progress of APPLY processes

Problem Description

This explains how to monitor if APPLY and APPLY1 has caught up, so you don't have to stop MINE hours in advance for maintenance work, like taking database backups. We ran the query against APPLY and APPLY1 from the source database via db links, and on Source itself to see where MINE is. From here one can easily compare and decide at what point to stop MINE.

Solution

Created a database link on Source as dbvrep user to connect to APPLY target database and a another database link to connect to APPLY1 target database.
Run query as dbvrep user.

APPLY and APPLY1 ( on Source via db links)

SQL> @monitor_apply.sql
APPLY

DDC_ID WALLCLOCK_DATE      MINE_DATE    Difference in Sec SOURCE_SCN   MINE_SCN SCN Difference
---------- ------------------- ------------------- ----------------- ---------- ----------
      1 2016-06-30 12:06:53 2016-06-30 12:06:47      6    98217519   98217500      19

APPLY1

DDC_ID WALLCLOCK_DATE      MINE_DATE    Difference in Sec SOURCE_SCN   MINE_SCN SCN Difference
---------- ------------------- ------------------- ----------------- ---------- ---------- 
      1 2016-06-30 12:06:53 2016-06-30 12:06:47      6   98217519   98217500       19

Source

SQL> @monitor_mine.sql
MINE

DDC_ID WALLCLOCK_DATE      MINE_DATE    Difference in Sec SOURCE_SCN   MINE_SCN SCN Difference
---------- ------------------- ------------------- ----------------- ---------- ---------- 
     1 2016-06-30 12:20:03 2016-06-30 12:19:5         6   98221315   98221293      22

For more information on the heartbeat table please refer to this link https://dbvisit.atlassian.net/wiki/display/ugd7/Heartbeat+table
Find attached the 2 queries for this purpose.

monitor_mine.sql
monitor_apply.sql