3 Replies Latest reply: Mar 3, 2014 10:43 AM by BPeaslandDBA RSS

    How to check if standby database is in sync with the primary?

    User502636-OC


      I checked the database alert log and dataguard log but did not see any errors. How can I check whether primary db and standby are in sync?

       

      Would querying v$archive_log be sufficient?

        • 1. Re: How to check if standby database is in sync with the primary?
          CKPT

          Run below query from standby.

           

          SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

          FROM

          (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

          (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

          WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

           

          Or you can use as below as well

          Primary: select thread#,max(sequence#) from v$archived_log group by thread#;

          Standby: select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

          • 2. Re: How to check if standby database is in sync with the primary?
            885820

            Another  mechanism that you could investigate (depending on what version you are on) is DG Broker.

             

            For the amount of effort required to set it up - the broker could  make life easier for you  when monitoring the health / apply lag (etc) of your standby(s), especially if your also using EM

             

            Perhaps this suggestion is a little off-topic as you were asking about which views you could query - but its just another option  you could consider perhaps

            • 3. Re: How to check if standby database is in sync with the primary?
              BPeaslandDBA

              I also recommend using the DG Broker. With it, you can do a simple "show configuration" command it will tell you if there are issues between the primary and the standby. You can further drill down into the issue by doing "show database db_name" where db_name can be either the primary or the standby.


              Cheers,
              Brian