4 Replies Latest reply: Oct 16, 2012 12:18 AM by sanikv RSS

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

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