4 Replies Latest reply: Sep 30, 2013 9:18 PM by mseberg RSS

    Standby Redo Log just sits as IN-MEMORY

    Jimbo

      Hi, I have shipped an archived redo log from my Primary to my physical standby.

       

      I can see the log arriving at my Standby and being applied

       

      select thread#, max(sequence#) "Last Standby Seq Received"
      from v$archived_log val, v$database vdb
      where val.resetlogs_change# = vdb.resetlogs_change#
      group by thread# order by 1;

       

      returns

       

         THREAD# Last Standby Seq Received

      ---------- -------------------------

               1                       151

       

      select thread#, max(sequence#) "Last Standby Seq Applied"

      from v$archived_log val, v$database vdb

      where val.resetlogs_change# = vdb.resetlogs_change#

      and applied='YES'

      group by thread# order by 1;

       

         THREAD# Last Standby Seq Applied

      ---------- ------------------------

               1                      150

       

      select stamp,name,applied

      from v$archived_log

      where applied != 'YES'

       

          STAMP

      ----------

      NAME

      --------------------------------------------------------------------------------

      APPLIED

      ---------

      827498375

      /home/app/oracle/fast_recovery_area/STANDBYL/archivelog/2013_09_30/o1_mf_1_151_9

      4lrqpj3_.arc

      IN-MEMORY

       

       

      This log continually sits like this

       

      I know that Redo Appply is active

       

      select * from v$managed_standby where process = 'MRP0';

       

      PROCESS          PID STATUS       CLIENT_P

      --------- ---------- ------------ --------

      CLIENT_PID

      ----------------------------------------

      CLIENT_DBID

      ----------------------------------------

      GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#

      ---------------------------------------- ----------- ---------- ----------

          BLOCK#     BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

      ---------- ---------- ---------- ------------ -------------

      MRP0            3068 APPLYING_LOG N/A

      N/A

      N/A

      N/A                                        820252586          1        152

            5038     102400          0            3             3

       

      It looks as if 151 has been applied - yet

       

      select thread#, max(sequence#) "Last Standby Seq Applied"

      from v$archived_log val, v$database vdb

      where val.resetlogs_change# = vdb.resetlogs_change#

      and applied='YES'

      group by thread# order by 1;

       

      still shows 150 !

       

      151 is not that big, I still would have expected it to have applied by now ( in excess of 30 mins )

       

      Also I know there is no defer or time delay on the archive_destination setting on the Primary.

       

      Any ideas why this standby redo log just sits IN-MEMORY ?

       

      thanks,

      Jim

        • 1. Re: Standby Redo Log just sits as IN-MEMORY
          Jimbo

          Strange if I use

           

          select thread#, max(sequence#) "Last Standby Seq Applied"
          from v$archived_log val, v$database vdb
          where val.resetlogs_change# = vdb.resetlogs_change#
          and applied='YES'
          group by thread# order by 1;

           

          it shows the last Seq applied as 150

           

          However if I use

           

          SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
          FROM 
          (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
          (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
          WHERE 
          ARCH.THREAD# = APPL.THREAD# 
          ORDER BY 1;

           

          It shows last Seq Applied as 151 !

           

          Which of these is telling the truth ?

           

          Jim

           

           

          • 2. Re: Standby Redo Log just sits as IN-MEMORY
            mseberg

            Hello;

             

            Not able to reproduce issue. Using Oracle 11.2.0.3 without Real-time apply.

             

             

            select thread#, max(sequence#) "Last Standby Seq Received"

            from v$archived_log val, v$database vdb

            where val.resetlogs_change# = vdb.resetlogs_change#

            group by thread# order by 1;

             

            THREAD#                Last Standby Seq Received

            ---------------------- -------------------------

            1                      194    

             

             

             

             

             

            select thread#, max(sequence#) "Last Standby Seq Applied"

            from v$archived_log val, v$database vdb

            where val.resetlogs_change# = vdb.resetlogs_change#

            and applied='YES'

            group by thread# order by 1;

             

            THREAD#                Last Standby Seq Applied

            ---------------------- ------------------------

            1                      194         

             

             

            select thread#, max(sequence#) "Last Standby Seq Applied"

            from v$archived_log val, v$database vdb

            where val.resetlogs_change# = vdb.resetlogs_change#

            And Applied='YES'

            group by thread# order by 1;

             

            THREAD#                Last Standby Seq Applied

            ---------------------- ------------------------

            1                      194   

             

             

            SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"

            FROM

            (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

            (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

            WHERE

            Arch.Thread# = Appl.Thread#

            ORDER BY 1;

             

            Thread                 Last Sequence Received Last Sequence Applied 

            ---------------------- ---------------------- ----------------------

            1                      194                    194                  

             

            select * from v$managed_standby where process = 'MRP0';

             

             

            ( shows the next sequence 195 on mine )

             

            Is it possible the Standby has one extra archive? I've noticed that sometime you will see one extra on that side which appears to have nothing to do with the Data Guard process.

             

            Best Regards

             

            mseberg

             

             

             

            • 3. Re: Standby Redo Log just sits as IN-MEMORY
              Jimbo

              No the exact same archive logs exist in the FRA of both the Primary and the Standby i.e. the max sequence being 151

               

              I left it for about an hour then both queries on my Standby returned the latest archived log sequence i.e. 151 in my case.

               

              It's almost as if the 2 queries are looking at something slightly different.

               

              Is there any way you can query when the Standby started to apply the archive log and when it completed. At least this wou;ld give me an indication of how long it took ( normally it is less than a couple of mins - so not sure why this took so long, it is not a particularly big archive log )

               

              thanks,

              Jim

              • 4. Re: Standby Redo Log just sits as IN-MEMORY
                mseberg

                I think Emre Baransel has one his site:

                 

                 

                http://emrebaransel.blogspot.com/2010/06/mrp-speed-log-apply-rate-of-standby.html

                 

                Best Regards

                 

                mseberg