10 Replies Latest reply: Dec 12, 2012 1:12 AM by user9127199 RSS

    Duplicate archivelog sequence on standby database

    user9127199
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Thank you so much