Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

Set up script to check standby database is in sync with production on cloud control 12C

Hi there all,
I am trying to  set up a script to check if standby database is in sync with production by comparing time interval on cloud control 12C. i.e if there is a gap in the logs not applied for >= 1 hour it should issue an alert.

I have set up the below script as a Metric Extension in Cloud control on Production database. For 10 archive logs gap - threshold is set for warning. For 25 archive logs gap, it is critical.

However I  wanted to convert it to "time" interval instead of # of archive logs. i.e for 1 hour gap it should show an alert.

Could you tell how below script to time interval..?

select sum(local.sequence#-target.sequence#) Total_gap
(select thread#,max(sequence#) sequence# from gv$archived_log where
dest_id=(select dest_id from v$archive_dest where TARGET='STANDBY'
and applied='YES' group by thread#) target,
(select thread#,max(sequence#) sequence# from gv$log group by thread#) local
where target.thread#=local.thread#;

Thank you
  • 2
2 Solutions
Abhimanyu SuriSr Database EngineerCommented:
On Primary

alter session set nls_date_format="dd-MON-yy hh24:mi:ss";
set lines 150
       prim.seq CURR_SEQ, CURR_TIME,
       tgt.thread# TGT_THD,
       tgt.seq TGT_SEQ, TGT_COMPL_TIME,
       prim.seq - tgt.seq SEQ_GAP,
       ( -*24*60 TIME_LAG  -- In MINS
  FROM (  SELECT thread#, MAX (sequence#) SEQ, MAX (completion_time) TM
            FROM v$archived_log
        GROUP BY thread#) PRIM,
       (  SELECT thread#, MAX (sequence#) SEQ, MAX (completion_time) TM
            FROM v$archived_log
           WHERE     dest_id = (SELECT dest_id
                                  FROM v$archive_dest
                                 WHERE TARGET = 'STANDBY')
                 AND applied = 'YES'
        GROUP BY thread#) TGT
 WHERE prim.thread# = tgt.thread#
---------- ---------- -------------------- ---------- ---------- -------------------- ---------- ----------
         1      75822 26-SEP-16 13:27:28            1      75822 26-SEP-16 13:27:28            0          0
         2      75837 26-SEP-16 13:27:55            2      75836 26-SEP-16 13:12:55            1 15.0000000
Abhimanyu SuriSr Database EngineerCommented:
Here is a script for secondary :

 Secondary :

  SELECT arch.thread# "Thread",
         arch.sequence# "Last Sequence Received",
         appl.sequence# "Last Sequence Applied",
         (arch.sequence# - appl.sequence#) "Diff",
           (  (SELECT NEXT_TIME
                 FROM v$archived_log a
                WHERE a.thread# = arch.thread# AND a.sequence# = arch.sequence#)
            - appl.NEXT_TIME)
         * 24
         * 60
    FROM (SELECT thread#, sequence#, NEXT_TIME
            FROM v$archived_log
           WHERE (thread#, first_time) IN (  SELECT thread#, MAX (first_time)
                                               FROM v$archived_log
                                              WHERE applied = 'YES'
                                           GROUP BY thread#)) appl,
         (SELECT thread#, sequence#
            FROM v$log_history
           WHERE (thread#, first_time) IN (  SELECT thread#, MAX (first_time)
                                               FROM v$log_history
                                           GROUP BY thread#)) arch
   WHERE arch.thread# = appl.thread#

But please note that , these will always give you lag from archive log perspective.
It is very well possible that an archive log is being processed (in-memory )

Hence, best suggested way to calculate time lag is

1. Create heartbeat table with date column
2. Schedule a job to insert date into Heratbeat table every minute or as desired  

That way you would be able to check real time lag as close to a minute ( if DG is real time i.e. LGWR )
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now