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

Posted on 2016-09-26
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 500 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, 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

Accepted Solution

Abhimanyu Suri earned 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 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