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

Posted on 2016-09-26
Medium Priority
Last Modified: 2016-11-08
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
Question by:newbieexpert
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 2000 total points
ID: 41816711
On Primary

alter session set nls_date_format="dd-MON-yy hh24:mi:ss";
set lines 150
       prim.seq CURR_SEQ,
       prim.tm CURR_TIME,
       tgt.thread# TGT_THD,
       tgt.seq TGT_SEQ,
       tgt.tm TGT_COMPL_TIME,
       prim.seq - tgt.seq SEQ_GAP,
       (prim.tm - tgt.tm)*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

Accepted Solution

Abhimanyu Suri earned 2000 total points
ID: 41817828
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 )

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question