8 Replies Latest reply: Feb 21, 2013 9:39 PM by 973679 RSS

    v$archived noy sync in Dataguard

    973679
      Hi,

      My question is about a doubt that I had y my Dataguard.

      Few days ago I did a scn incremental restore in order to sync Primary and Standby instances, the problem is v$archived_log is not updated. Let me show you

      Primary:

      select max(sequence#), to_char(next_time,'DD-MON-YY:HH24:MI:SS'), applied from v$archived_log group by to_char(next_time,'DD-MON-YY:HH24:MI:SS'), applied order by 1 desc
      .
      .
      .
      .
      923426 17-FEB-13:07:16:38 NO

      SQL> select max(sequence#) from v$archived_log where applied='YES';


      MAX(SEQUENCE#)
      --------------
      915252



      Standby:


      select max(sequence#), to_char(next_time,'DD-MON-YY:HH24:MI:SS'), applied from v$archived_log group by to_char(next_time,'DD-MON-YY:HH24:MI:SS'), applied order by 1 desc
      .
      .
      .
      923426 17-FEB-13:07:16:38 YES


      When I do automatic recover the gap is:


      Fetching gap sequence in thread 1, gap sequence 923427-923526



      My question is why I have different values on that queries?

      Thanks a lot!

      Edited by: 970676 on Feb 21, 2013 4:31 PM
        • 1. Re: v$archived noy sync in Dataguard
          mseberg
          Hello;

          If I understand the question correctly its because of how you query v$archived_log. The max(sequence#) hurts the query because it does not allow you see the different values of DEST_ID.

          Try changing the query to include DEST_ID and use WHERE sysdate > -1 ( on one of the date columns ) to see a smaller range.
          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;
          The try this query from your Primary :

          http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

          Best Regards

          mseberg

          Edited by: mseberg on Feb 21, 2013 6:49 PM
          • 2. Re: v$archived noy sync in Dataguard
            973679
            Thanks,

            This is the resultset of the query on Primary and Standby.

            Primary:

            DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
            ---------- -------------- ------------ ----------- -------------- -----------
            XXXXX XXXXXXX 930435 915252 13-FEB/07:08 15183


            Standby:

            DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
            ---------- -------------- ------------ ----------- -------------- -------
            XXXX XXXXX 923426 21-FEB/17:06



            I don't understand the big difference between Primary and Standby.
            • 3. Re: v$archived noy sync in Dataguard
              Hemant K Chitale
              I did a scn incremental restore in order to sync Primary and Standby instances
              Did you also sync the controlfile ? That needs to be done.


              Hemant K Chitale
              • 4. Re: v$archived noy sync in Dataguard
                973679
                Yes I did it.


                Do you think is good idea sync the controlfile again?

                Primary:

                RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';


                Standby:

                RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
                • 5. Re: v$archived noy sync in Dataguard
                  Hemant K Chitale
                  If you have already sync'ed the controlfile you should not need to do it again.


                  See all the steps under http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#CIHIAADC


                  Hemant K Chitale
                  • 6. Re: v$archived noy sync in Dataguard
                    973679
                    I've followed that steps for an incremental restore.

                    I really don't understand why the views of Primary and Standby are totally different.

                    As you can see I have a long gap yet, if I do an incremental backup/restore again, would this work again?

                    Thanks.
                    • 7. Re: v$archived noy sync in Dataguard
                      Hemant K Chitale
                      You would need to compare the total GBytes (and growing) for the archivelog gap versus the total GBytes for a fresh incremental backup from SCN to decide what is better to transfer across the network.


                      Hemant K Chitale
                      • 8. Re: v$archived noy sync in Dataguard
                        973679
                        Ok,

                        And do you have any suggestion about the difference between both views?