9 Replies Latest reply: Oct 11, 2013 7:45 AM by yxes2013 RSS

    DG primary and standby

    yxes2013

      Hi all,

       

      11.2.0.1

      Aix 6.1

       

      I want to monitor the applied logs both on my primary and standby

      using > select client_process, process, sequence#, status from v$managed_standby;

       

      I want to issue the command on my client laptop so I want compare the output easily, whether logs are synch.

       

      How can I connect to a standby database s which is not open using client?

       

      Thanks a lot,

      zxy

        • 1. Re: DG primary and standby
          CKPT

          Applied logs you have to check only from standby database(s). Even view v$managed_standby you can see whether MRP is running or not or any status of process you can check from standby.

           

          If you want to access any database, from remote server. then you have to use oracle net service to connect and to use of it.

          $sqlplus sys/*****@standby as sysdba

           

          and then you can use below command to get archives received and applied on 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;

           

          - Thanks

          • 2. Re: DG primary and standby
            yxes2013

            Thanks chkpt

             

            Long time no hear.

            • 3. Re: DG primary and standby
              CKPT

              yxes2013 wrote:

               

              Thanks chkpt

               

              Long time no hear.

               

              Yes, Kind of break. Now am back.

              • 4. Re: DG primary and standby
                yxes2013

                Hi Ckpt,

                 

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

                  2  FROM

                  3  (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,

                  4  (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;  5

                 

                 

                    Thread Last Sequence Received Last Sequence Applied Difference

                ---------- ---------------------- --------------------- ----------

                         1                   2522                  2522          0

                 

                How do I know If how many hours did my last archivelog lag?

                 

                Since we have a service level of 30 min mandatory apply archivelog when disaster strikes.

                Some of our database is not even changing logs for 1 day due to our big redo of 200Mb and lesser transaction which can not fill up 200Mb.

                 

                What query do I run to check this lag time?

                 

                 

                Thanks a lot...

                • 5. Re: DG primary and standby
                  CKPT
                  • 6. Re: DG primary and standby
                    yxes2013

                    Nice blog.

                     

                    Should I run all the scripts always at the Standby Database?

                     

                        Thread Last Sequence Received Last Sequence Applied Difference

                    ---------- ---------------------- --------------------- ----------

                             1                   2522                  2522          0

                     

                     

                    What if my network between my Primary & Standby has been disconnected.

                    Could it be possible the same value is still reflected even if the Primary has been applying far ahead?

                     

                    Thanks,

                    • 7. Re: DG primary and standby
                      CKPT

                      Whatever i mention so far, they have to run on only standby of course.

                       

                      What if my network between my Primary & Standby has been disconnected.

                      Could it be possible the same value is still reflected even if the Primary has been applying far ahead?

                      It should not be same, have a test. Stop MRP, perform some log switches on primary and then see for fun

                      • 8. Re: DG primary and standby
                        Anar Godjaev

                        hi,

                         

                        Just wanted to share

                         

                        On Primary

                         

                        SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

                        FROM V$ARCHIVED_LOG

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

                        ORDER BY 1;

                         

                        On 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;

                         

                        SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

                         

                        Reference : http://www.oraclemasters.in/?p=1255

                         

                        -- List archivelogs not applied on standby

                         

                        SELECT DISTINCT SEQUENCE# FROM V$ARCHIVED_LOG where APPLIED='NO' and sequence# > 100 MINUS (SELECT SEQUENCE# FROM V$ARCHIVED_LOG where APPLIED='YES' AND SEQUENCE# IN (SELECT DISTINCT SEQUENCE# FROM V$ARCHIVED_LOG where APPLIED='NO'));

                         

                        -- Give the count of archvielogs not applied on standby

                         

                        select thread#,count(sequence#) "COUNT_ARCS_PENDING" from (SELECT DISTINCT THREAD# ,SEQUENCE#  FROM V$ARCHIVED_LOG where APPLIED='NO' and sequence# > 100 MINUS (SELECT THREAD#,SEQUENCE# FROM V$ARCHIVED_LOG  where APPLIED='YES' AND SEQUENCE# IN (SELECT DISTINCT SEQUENCE# FROM V$ARCHIVED_LOG where APPLIED='NO' and sequence# > 100))) group by thread#;

                        • 9. Re: DG primary and standby
                          yxes2013

                          I thank you all,

                           

                          My problem is I dont have a test server right now. 

                          I am just inheriting this PROD dataguard as new employee.

                          I am still looking for free server that I can make as my lab.