This discussion is archived
10 Replies Latest reply: Dec 11, 2012 11:12 PM by user9127199 RSS

Duplicate archivelog sequence on standby database

user9127199 Newbie
Currently Being Moderated
Hi,

In our standby database when i check archivelog (below command), i see 2 duplicate sequence number. One is applied and another is not applied. Is there any problem with dataguard and how to solve it

TQ


SQL> sELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where SEQUENCE# like '369%' order by SEQUENCE#;

THREAD# SEQUENCE# APP
---------- ---------- ---
1 36900 YES
1 36901 YES
1 36902 YES
1 36902 NO
1 36903 YES
1 36903 YES
1 36904 YES
1 36904 YES
1 36905 YES
1 36905 NO
1 36906 YES
1 36907 YES
1 36908 YES
1 36909 YES
1 36910 YES
1 36911 YES
1 36911 NO
1 36912 YES
1 36912 NO
1 36913 YES
1 36913 YES
1 36914 YES
1 36914 NO
1 36915 YES
1 36915 NO
1 36916 YES
1 36917 YES
1 36918 YES
1 36919 YES
1 36919 YES
1 36920 YES
  • 1. Re: Duplicate archivelog sequence on standby database
    CKPT Guru
    Currently Being Moderated
    user9127199 wrote:
    Hi,

    In our standby database when i check archivelog (below command), i see 2 duplicate sequence number. One is applied and another is not applied. Is there any problem with dataguard and how to solve it
    If you have one standby database, One archive log location refers to local and other location refers to the remote destination.

    You can change selectivity as

    SQL> select name , sequence# from v$archived_log where dest_id=2;
    Note:- assuming your remote destination is "2", if not change as per your continence.

    and in standby you should use where clause APPLIED='YES' which refers archives are applied or not, Archives will be applied on standby and generated in primary. So there is no issue if status is "NO" in primary.

    Hope this clear.
  • 2. Re: Duplicate archivelog sequence on standby database
    user9127199 Newbie
    Currently Being Moderated
    I only have one standby. But when i select below it shows 3 destination. How to determine where dest_id ?

    SQL> select DEST_ID FROM V$ARCHIVED_LOG group by DEST_ID;

    DEST_ID
    ----------
    0
    1
    2


    When i select below, for example sequence number 36902 - have one on dest 1 = applied yes dest 2= applied no . what that means?

    SQL> select DEST_ID,THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where SEQUENCE# like '369%' order by SEQUENCE#;

    DEST_ID THREAD# SEQUENCE# APP
    ---------- ---------- ---------- ---
    1 1 36900 YES
    2 1 36901 YES
    1 1 36902 YES
    2 1 36902 NO
    1 1 36903 YES
    2 1 36903 YES
    1 1 36904 YES
    2 1 36904 YES
    1 1 36905 YES
    2 1 36905 NO
    1 1 36906 YES
    1 1 36907 YES
    1 1 36908 YES
    1 1 36909 YES
    2 1 36910 YES
    1 1 36911 YES
    2 1 36911 NO
    1 1 36912 YES
    2 1 36912 NO
    1 1 36913 YES
    2 1 36913 YES
    1 1 36914 YES
  • 3. Re: Duplicate archivelog sequence on standby database
    CKPT Guru
    Currently Being Moderated
    I only have one standby. But when i select below it shows 3 destination. How to determine where dest_id ?
    What is the version you are using?
    use below query and you can see what are the valid destinations, According to valid destinations you can use the destination ID to see what are the archives are transported and what are they applied.
    set lines 200
    set numwidth 15
    column ID format 99
    column "SRLs" format 99 
    column active format 99 
    col type format a4
    
    select     ds.dest_id id
    ,     ad.status
    ,     ds.database_mode db_mode
    ,     ad.archiver type
    ,     ds.recovery_mode
    ,     ds.protection_mode
    ,     ds.standby_logfile_count "SRLs"
    ,     ds.standby_logfile_active active
    ,     ds.archived_seq#
    from     v$archive_dest_status     ds
    ,     v$archive_dest          ad
    where     ds.dest_id = ad.dest_id
    and     ad.status != 'INACTIVE'
    order by
         ds.dest_id
    /
  • 4. Re: Duplicate archivelog sequence on standby database
    user9127199 Newbie
    Currently Being Moderated
    We used oracle 10.2.0.1.0

    Below is the result

    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- ----------------------- -------------------- ---- ------ ---------------
    1 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38268
    11 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38241


    SQL> select DEST_ID FROM V$ARCHIVED_LOG group by DEST_ID;

    DEST_ID
    ----------
    0
    1
    2
  • 5. Re: Duplicate archivelog sequence on standby database
    CKPT Guru
    Currently Being Moderated
    Below is the result

    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- ----------------------- -------------------- ---- ------ ---------------
    1 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38268
    11 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38241
    There are no Primary role destinations here? I can see only two standby databases. what is local destination you have configured?
    I think you know better about your environment, Please describe about it.
    from primary: SQL> archive log list

    also you can use script http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
  • 6. Re: Duplicate archivelog sequence on standby database
    user9127199 Newbie
    Currently Being Moderated
    Result At primary

    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- ----------------------- -------------------- ---- ------ ---------------
    1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 38320
    2 VALID UNKNOWN ARCH UNKNOWN MAXIMUM PERFORMANCE 7 7 38318

    archive log list
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /xxx/xxx
    Oldest online log sequence 38316
    Next log sequence to archive 0
    Current log sequence 38321


    Result at standby

    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- ----------------------- -------------------- ---- ------ ---------------
    1 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38316
    11 VALID MOUNTED-STANDBY ARCH MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 0 0 38318

    archive log list
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /xxx/xxx
    Oldest online log sequence 38316
    Next log sequence to archive 0
    Current log sequence 38321
  • 7. Re: Duplicate archivelog sequence on standby database
    CKPT Guru
    Currently Being Moderated
    Thats fine, So you queried from standby first.

    and your standby destination id is *2* so use the same destination using below query.
    SQL> select DEST_ID,THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where SEQUENCE# like '369%' and dest_id=2 order by SEQUENCE#;
    in this output you will get sequences related to only standby destination. No more duplicates you can see.
  • 8. Re: Duplicate archivelog sequence on standby database
    user9127199 Newbie
    Currently Being Moderated
    When i select at standby as per below . There's a sequence that applied=no and there's also a missing sequence . Is this mean that my standby is not sync ?

    DEST_ID THREAD# SEQUENCE# APP
    ---------- ---------- ---------- ---
    2 1 36901 YES
    2 1 36902 NO
    2 1 36903 YES
    2 1 36904 YES
    2 1 36905 NO
    2 1 36910 YES
    2 1 36911 NO
    2 1 36912 NO
    2 1 36913 YES
    2 1 36914 NO
    2 1 36915 NO
    2 1 36918 YES
    2 1 36919 YES
    2 1 36920 NO
    2 1 36921 YES
    2 1 36922 NO
    2 1 36923 NO
    2 1 36925 YES
    2 1 36928 YES
    2 1 36946 YES
    2 1 36947 YES
    2 1 36948 YES
  • 9. Re: Duplicate archivelog sequence on standby database
    CKPT Guru
    Currently Being Moderated
    It doesnt mean that lower sequences are not applied and the higher sequences are applied, Recover will be performed by sequences in order.
    At the same time there are some bugs with the "APPLIED" column of v$archived_log , You can refer some of the bugs

    Bug 4538727  Applied column is not updated in V$ARCHIVED_LOG
    Bug 7417614  APPLIED column in V$ARCHIVED_LOG can erroneously indicate a log was not applied
    +V$Archived_log "Applied" column not updated For Downstream Capture [ID 602960.1]+
  • 10. Re: Duplicate archivelog sequence on standby database
    user9127199 Newbie
    Currently Being Moderated
    Thank you so much

Legend

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