6 Replies Latest reply: Feb 16, 2013 12:21 PM by CKPT RSS

    Strange Observation in Standby Server

    sandy121
      On one of the standby server in our dataguard environment , I ran the below query to find out the archive logs which were not applied on standby and it returned below output as below :-

      {SQL> SELECT SEQUENCE#, to_char(FIRST_TIME,'dd-mon-yyyy hh24:mi'), to_char(NEXT_TIME,'dd-mon-yyyy hh24:mi'),APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ORDER BY SEQUENCE#;}

      Output :-

      SEQUENCE# TO_CHAR(FIRST_TIME,'DD- TO_CHAR(NEXT_TIME,'DD-M APP
      ---------- ----------------------- ----------------------- ---
      1520 20-nov-2012 13:59 20-nov-2012 13:59 NO
      1521 20-nov-2012 13:59 20-nov-2012 14:00 NO
      1521 20-nov-2012 13:59 20-nov-2012 14:00 NO
      1805 28-jan-2013 06:04 28-jan-2013 06:08 NO
      1808 28-jan-2013 06:11 28-jan-2013 06:11 NO
      1809 28-jan-2013 06:11 28-jan-2013 06:13 NO

      6 rows selected.

      It was noticed that archivelog with sequences 1520,1521,1805,1808 & 1809 are not applied on the standby servers.

      But when checked individually, the applied status was showing as both 'YES' and 'NO' as shown below :-

      {SQL> select sequence#,applied from v$archived_log where sequence# in (1520,1521,1805,1808,1809);}

      Output :-

      SEQUENCE# APP
      ---------- ---
      1520 YES
      1520 NO
      1520 YES
      1521 YES
      1521 NO
      1521 NO
      1805 NO
      1805 YES
      1808 NO
      1808 YES
      1809 NO

      SEQUENCE# APP
      ---------- ---
      1809 YES

      What may be the reason behind it ? Any explanations will be great for me . Thanks in advance.

      Edited by: 918868 on Feb 16, 2013 7:56 AM
        • 1. Re: Strange Observation in Standby Server
          Step_Into_Oracle_DBA
          Post

          Select * from v$managed_standby;
          • 2. Re: Strange Observation in Standby Server
            damorgan
            To help you please do the following:

            1. Read the FAQ and learn how to post to these forums. All listings should be enclosed in { code } tags so they are readable.
            2. Post full version number
            SELECT * FROM v$version;
            3. Post the SQL that created the output for your listings. We have no idea where what you are showing us originated and I don't believe in guessing.

            Thank you.
            • 3. Re: Strange Observation in Standby Server
              mseberg
              Hello;

              Assuming Oracle 10 or 11 this would be normal in most setups. If you add DEST_ID to your query you should see why this happens.
              select 
                sequence#,
                applied,
                DEST_ID
              from 
                v$archived_log 
              where 
                sequence# in (1520,1521,1805,1808,1809);
              ( In most setups DEST_ID = 1 will show NO and DEST_ID = 2 will show YES which is correct)


              Best Regards

              mseberg
              • 4. Re: Strange Observation in Standby Server
                sandy121
                Thanks buddy, can you please explain it in a bit elaborate way ?
                • 5. Re: Strange Observation in Standby Server
                  mseberg
                  OK

                  My test system has these parameters
                  *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
                  
                  *.log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
                  
                  *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
                  
                  *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
                  So if I run the query as shown in the prior post dest_1 will always come up NO and dest_2 should come up YES ( assuming all is well ). So each sequence number has two destinations in this setup, one to standby (dest_2) and one that's note (dest_1). dest_1 is not applied to standby so the query is working as expected.

                  Make sense? Run this from your Primary :
                  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

                  Edited by: mseberg on Feb 16, 2013 11:11 AM
                  • 6. Re: Strange Observation in Standby Server
                    CKPT
                    On one of the standby server in our dataguard environment , I ran the below query to find out the archive logs which were not applied on standby and it returned below output as below :-
                    {SQL> SELECT SEQUENCE#, to_char(FIRST_TIME,'dd-mon-yyyy hh24:mi'), to_char(NEXT_TIME,'dd-mon-yyyy hh24:mi'),APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ORDER BY SEQUENCE#;}

                    Output :-

                    SEQUENCE# TO_CHAR(FIRST_TIME,'DD- TO_CHAR(NEXT_TIME,'DD-M APP
                    ---------- ----------------------- ----------------------- ---
                    1520 20-nov-2012 13:59 20-nov-2012 13:59 NO
                    1521 20-nov-2012 13:59 20-nov-2012 14:00 NO
                    1521 20-nov-2012 13:59 20-nov-2012 14:00 NO
                    1805 28-jan-2013 06:04 28-jan-2013 06:08 NO
                    1808 28-jan-2013 06:11 28-jan-2013 06:11 NO
                    1809 28-jan-2013 06:11 28-jan-2013 06:13 NO
                    Have you performed any drills? like switchover past on 28th January?
                    It was noticed that archivelog with sequences 1520,1521,1805,1808 & 1809 are not applied on the standby servers.

                    But when checked individually, the applied status was showing as both 'YES' and 'NO' as shown below :-

                    {SQL> select sequence#,applied from v$archived_log where sequence# in (1520,1521,1805,1808,1809);}

                    Output :-

                    SEQUENCE# APP
                    ---------- ---
                    1520 YES
                    1520 NO
                    1520 YES
                    1521 YES
                    1521 NO
                    1521 NO
                    1805 NO
                    1805 YES
                    1808 NO
                    1808 YES
                    1809 NO

                    SEQUENCE# APP
                    ---------- ---
                    1809 YES

                    What may be the reason behind it ? Any explanations will be great for me . Thanks in advance.
                    If you see here, lets an example of sequence *1520* it has 3 rows and 2 rows as applied and one row wasn't applied. My guess was the row which is not applied is from Primary and remaining two must be of standby database.

                    So whenever you using queries, Do check with proper destination ID, such as

                    SQL> select dest_id, sequence#,applied from v$archived_log;
                    You have to take a look closely on destination ID.

                    BTW, v$archived_log has some bugs with views, May be not applicable in your environment. and you can use this script to monitor redo transport between primary and standby databases(even RAC) http://www.oracle-ckpt.com/script-to-monitor-primary-and-standby-databases/

                    What about the synchronization status? Is everything sync?
                    If you think you have issue with that, Use the below script and post output from primary and standby in coded format.
                    http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/


                    HTH.