This discussion is archived
4 Replies Latest reply: Sep 30, 2013 7:18 PM by mseberg RSS

Standby Redo Log just sits as IN-MEMORY

Jimbo Explorer
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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