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.

 

Website Comments

  1. Venkatraman lakshmanan
    Reply

    Genius. Thanks for sharing the script. Lots of time saved for DBA’s

  2. Manoranjan Pradhan
    Reply

    This script is great !!!
    If you can share a script for 1 primary and 2 standby to check the sync status .

    • Balraj Singh Chahal
      Reply

      is the script working for u ?

      Can you help ?

      i get GAP as negative

      SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
      41 42 43 44 45 46 47 48 49 FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’ and THREAD#=2
      )
      / 50 51

      DB_NAME APPLIED_TIME LOG_GAP STATUS
      —————- —————————— ———- ——————
      28-APR/18:03 -43293 OK!
      OK!

      SQL>

      • nassyambasha@gmail.com
        Reply

        Sorry for delay in response. Probably there are some orphan resetlogs change sequence exist and which is cataloged with control file.
        can you please send the below output from both primary and standby databases?
        select thread#,max(sequence#) from v$archived_log group by thread#;

Leave a Reply to nassyambasha@gmail.com Cancel reply

*