Solved

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

Posted on 2016-09-26
3
141 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
[X]
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
3 Comments
 
LVL 4

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
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 4

Accepted Solution

by:
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
            "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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Verification of DataPump Export and Import 17 69
Row_number in SQL 6 45
Procedure syntax 5 50
Oracle perfomance issue. 4 25
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

696 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