This discussion is archived
4 Replies Latest reply: Oct 15, 2012 10:18 PM by sanikv RSS

Dataguard is lagging 50 min or so but there is no archive pending to apply

910522 Newbie
Currently Being Moderated
Dataguard is lagging 50 min or so but there is no archive pending to apply.

select (select name from V\$DATABASE)DB_NAME,(select max(sequence#) from v\$archived_log where dest_id=1) CURRENT_PRIMARY_SEQ,( select max(sequence#) from v\$archived_log where dest_id=$1) MAX_SHIP
PED_SEQ_AT_STANDBY,( select max(sequence#) from v\$archived_log where dest_id=$1 and applied='YES') MAX_APPLIED_SEQ_AT_STANDBY,(select nvl((select max(sequence#) - min(sequence#) from v\$archived_l
og where dest_id=$1 and applied='NO'),0) from dual) "NO_OF_SEQ_TO_BE_APPLIED",((select max(sequence#) from v\$archived_log where dest_id=1) - (select max(sequence#) from v\$archived_log where dest
id=$1)) "NOOF_SEQ_TO_BE_SHIPPED" from dual;
select floor(((select next_time from v\$archived_log where sequence#=(select max(sequence#) from v\$archived_log where dest_id=1) and dest_id=1) - (select next_time from v\$archived_log where seque
nce#=(select max(sequence#) from v\$archived_log where dest_id=$1 and applied='YES') and dest_id=$1))*1440) lag_mins from dual;

DB_NAME CURRENT_PRIMARY_SEQ MAX_SHIPPED_SEQ_AT_STANDBY MAX_APPLIED_SEQ_AT_STANDBY NO_OF_SEQ_TO_BE_APPLIED NO_OF_SEQ_TO_BE_SHIPPED
--------- ------------------- -------------------------- -------------------------- ----------------------- -----------------------
OASIS 15940 15940 15939 0 0


we are monitoring through this script and it says no archive to apply but still 40 or some min lag.what conf we need to change .Db running on perf mode and SYNC lLGWR.
  • 1. Re: Dataguard is lagging 50 min or so but there is no archive pending to apply
    JohnWatson Guru
    Currently Being Moderated
    Your post is unreadable, I do not think anyone can comment on what it is supposed to be reporting. There is a code formatter here http://www.dpriver.com/pp/sqlformat.htm that you might want to use, and then post the output enclosed within
     tags.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: Dataguard is lagging 50 min or so but there is no archive pending to apply
    Balazs Papp Expert
    Currently Being Moderated
    i guess you are using a MAXIMUM PERFORMANCE configuration without standby redo logs, where changes get applied only at log switches

    on primary:

    show parameter log_archive_dest_N (use the approriate number that is representing the standby destination)
    select protection_mode, protection_level from v$database;
    select bytes from v$log;

    on standby:

    select status from v$managed_standby where process='MRP0';
    select recovery_mode from v$archive_dest_status;
    select group#, thread#, status from v$standby_log;
  • 3. Re: Dataguard is lagging 50 min or so but there is no archive pending to apply
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You haven't posted the output of your second query.

    My guess is that the Primary hasn't generated a new ArchiveLog in the last 50minutes -- so there is nothing to apply on the standby. You should be using ARCHIVE_LAG_TARGET to ensure that the Primary generates and ships a new ArchiveLog at a predetermined frequency.


    Hemant K Chitale
  • 4. Re: Dataguard is lagging 50 min or so but there is no archive pending to apply
    sanikv Newbie
    Currently Being Moderated
    is that a RAC database ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points