Script to Monitor Primary and Standby database(s)

Usually to monitor redo transport some times we have to connect both the Primary and Standby database(s) to check maximum sequence applied of each thread and of course we have to check what is the sequence is generated on primary database, This script more useful with RAC databases and by this script we can check below components we can execute from the primary database to get the information of all the nodes. They are

1. Database Name

2. Last Applied Time

3. Log gap on each standby database(s)

4. Status

And the remote destination number set to default which is “2“, If you have configured to other remote destinations then change the value of DEST_ID based on the LOG_ARCHIVE_DEST_n you have configured. From the output if the archive log GAP is more than “5” then it returns status as “Error” and if the Archive GAP is less than “5” the status will be returned as “OK“. You can change the script based on the acceptable Archive GAP between primary and standby databases.

column applied_time for a30
set linesize 140
select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
            (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
            ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
      then 'Error! Log Gap is ' 
      else 'OK!' 
 end) Status
FROM
(
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
)
UNION
SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
            (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
            ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
      then 'Error! Log Gap is ' 
      else 'OK!' 
 end) Status
from (
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
)
/

Tested this script in 2-node RAC standby and you can see the sample output how it returns.

DB_NAME          APPLIED_TIME                      LOG_GAP STATUS
---------------- ------------------------------ ---------- --------------------------------
CKPT             04-FEB/01:42                            0 OK!
CKPT             04-FEB/01:43                            0 OK!

Here, You can see the status of each standby database nodes and log gap of each thread.

Script to Monitor Primary and Standby database(s)