[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2016-09-26
3
Medium Priority
?
898 Views
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
from
(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
0
Comment
Question by:newbieexpert
  • 2
2 Comments
 
LVL 5

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
SELECT PRIM.THREAD# CURR_THD,
       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#
 
  CURR_THD   CURR_SEQ CURR_TIME               TGT_THD    TGT_SEQ TGT_COMPL_TIME          SEQ_GAP   TIME_LAG
---------- ---------- -------------------- ---------- ---------- -------------------- ---------- ----------
         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
0
 
LVL 5

Accepted Solution

by:
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
            "TIME_LAG"
    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#
ORDER BY 1

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 )
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

591 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