This discussion is archived
7 Replies Latest reply: Sep 12, 2013 9:47 AM by mseberg RSS

Logs applied on standby?

User369327 Newbie
Currently Being Moderated

hi, we just setup a physical standby for a RAC database (2 instances) 11.2.0.3. Logs are getting applied but when we check the status of the archives on the standby, the last log from one of the instances is always marked as not applied. It is always for the instance opposite from the one that had the most recent log switch

 

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

SEQUENCE# APPLIED

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

      4089 YES

      4090 YES

      4091 YES

      4092 YES

      4093 YES

      4094 YES

      4095 YES

      4096 YES

      4097 YES

      4098 YES

      4099 YES

      4100 YES

      4101 YES

      4102 YES

      4103 YES

      4104 YES

      4105 YES

      4106 NO

      4309 YES

      4310 YES

      4311 YES

      4312 YES

      4313 YES

      4314 YES

      4315 YES

      4316 YES

      4317 YES

      4318 YES

      4319 YES

      4320 YES

      4321 YES

      4322 YES

      4323 YES

      4324 YES

      4325 YES

 

35 rows selected.

 

 

On the primary instance the logs show as

 

 

SYS:dfcip12 > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +ORA_DATA

Oldest online log sequence     4105

Next log sequence to archive   4107

Current log sequence           4107

SYS:dfcip12 >

 

Is this anything to worry about or are we missing something? Appreciate any thoughts. Thanks.

  • 1. Re: Logs applied on standby?
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    Where are you running this query from ( Primary side or Standby side)?

     

    I would change the query to include DEST_ID and THREAD# if run on the primary side too.

     

    I like this query from the Primary side, change as needed:

     

    Monitor Data Guard Transport

     

    Adding a SYSDATE can help too.

     

    clear screen

    set linesize 100

     

    column STANDBY format a20

    column applied format a10

     

    SELECT NAME AS STANDBY, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

     

     

    Best Regards

     

    mseberg

  • 2. Re: Logs applied on standby?
    User369327 Newbie
    Currently Being Moderated

    I'm running that query on the standby.

  • 3. Re: Logs applied on standby?
    mseberg Guru
    Currently Being Moderated

    OK, trying adding the SYSDATE - like I show before and see if you get better results.

     

    Best Regards

     

    mseberg

  • 4. Re: Logs applied on standby?
    User369327 Newbie
    Currently Being Moderated

    i did but same results. That one log still shows as not applied.

     

     

    STANDBY               SEQUENCE# APPLIED    COMPLETIO

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

    +ORA_DATA/dfcip1/arc       4090 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4090.874

    .825850059

     

    +ORA_DATA/dfcip1/arc       4091 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4091.876

    .825850061

     

    +ORA_DATA/dfcip1/arc       4092 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4092.875

    .825850061

     

    +ORA_DATA/dfcip1/arc       4093 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4093.881

    .825850201

     

    +ORA_DATA/dfcip1/arc       4094 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4094.882

    .825850401

     

    +ORA_DATA/dfcip1/arc       4095 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4095.883

    .825850491

     

    +ORA_DATA/dfcip1/arc       4096 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4096.889

    .825852423

     

    +ORA_DATA/dfcip1/arc       4097 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4097.893

    .825852859

     

    +ORA_DATA/dfcip1/arc       4098 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4098.894

    .825852943

     

    +ORA_DATA/dfcip1/arc       4099 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4099.919

    .825866869

     

    +ORA_DATA/dfcip1/arc       4100 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4100.929

    .825873439

     

    +ORA_DATA/dfcip1/arc       4101 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4101.675

    .825879499

     

    +ORA_DATA/dfcip1/arc       4102 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4102.794

    .825879895

     

    +ORA_DATA/dfcip1/arc       4103 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4103.935

    .825883837

     

    +ORA_DATA/dfcip1/arc       4104 YES        11-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4104.941

    .825888229

     

    +ORA_DATA/dfcip1/arc       4105 YES        12-SEP-13

    hivelog/2013_09_11/t

    hread_2_seq_4105.947

    .825892671

     

    +ORA_DATA/dfcip1/arc       4106 NO         12-SEP-13

    hivelog/2013_09_12/t

    hread_2_seq_4106.930

    .825912003

  • 5. Re: Logs applied on standby?
    mseberg Guru
    Currently Being Moderated

    Any chance you have Apply running with "USING CURRENT LOG" ?

     

    If yes then this would be normal as Oracle will see the log as soon as its gets created, but not mark it applied until it moves to the next log.

     

    An ALTER SYSTEM SWITCH LOGFILE on the primary would shake this out.

     

    Best Regards

     

    mseberg

  • 6. Re: Logs applied on standby?
    User369327 Newbie
    Currently Being Moderated

    HI, we started it with below so unless 'USING CURRENT LOG' is a default then we're not using it

     

    alter database recover managed standby database disconnect from session;

     

     

    If i do another switch logfile then the log marked NO is now marked applied YES but the new log that was just archived is marked applied NO

  • 7. Re: Logs applied on standby?
    mseberg Guru
    Currently Being Moderated

    Odd.

     

    My Test Primary

     

    SQL> select max(sequence#) from v$archived_log;

     

    MAX(SEQUENCE#)

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

      807

     

    SQL> alter system switch logfile;

     

    System altered.

     

     

    My Test Standby

     

    SQL> select max(sequence#) from v$archived_log;

     

    MAX(SEQUENCE#)

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

      807

     

    SQL> /

     

    MAX(SEQUENCE#)

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

      807

     

    SQL> /

     

    MAX(SEQUENCE#)

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

      808

     

    SQL>

     

     

    It takes a few seconds but it moves to 808.

     

    But if I run your query in your first post I get the same results.

     

    SEQUENCE# APPLIED

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

      799 YES

      800 YES

      801 YES

      802 YES

      803 YES

      804 YES

      805 YES

      806 YES

      807 YES

      808 NO

     

    So Oracle knows about the log, but has not applied it yet. The Standby alert log will confirm:

     

    Media Recovery Waiting for thread 1 sequence 808 (in transit)

     

    I don't see an issue.

     

    Best Regards

     

    mseberg

Legend

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