3 Replies Latest reply: Oct 25, 2013 8:45 AM by Youmayna RSS

    DATALAG calculation query

    Youmayna

      Hi All,

       

      Can anybody provide me the query to calculate datalag between primary and standby databases for oracle 11gr2.

       

      I'm using the below queries with which i'm getting negative value(i.e. standby database is ahead of primary database).

       

      1) ON DR (For Max Sequence : DRLogSequence )

       

      select max(sequence#) from v$log_history where

      (select min(checkpoint_change#) from v$datafile_header where file# in

      (select file# from (select file#, enabled from v$datafile where status = 'ONLINE')

      where enabled = 'READ WRITE')) between first_change# and next_change#;

       

       

      2) ON Production (For Time stamp of DR Log )

       

      select sequence#,to_char(COMPLETION_TIME,'mm-dd-yyyy hh24:mi:ss') completion_time,to_char(first_Change#)

      first_change# from gv$archived_log where sequence#=<sequence# output of query 1> and rownum<2;

       

      3) ON Production (For Time stamp of PR Log )

       

      select sequence#,to_char(COMPLETION_TIME,'mm-dd-yyyy hh24:mi:ss') completion_time,to_char(first_Change#) first_change# from v$archived_log

      where (select min(checkpoint_change#)

      from v$datafile_header where file# in

      (select file# from (select file#, enabled from v$datafile where status = 'ONLINE')

        where enabled = 'READ WRITE')) between first_change# and next_change# and rownum<2;


      how can i get the positive value, where i can see primary is always ahead of standby.



      Thanks in advance....!!