12 Replies Latest reply: Feb 23, 2012 3:39 AM by 735967 RSS

    How to know the delay in redo log apply on Active Dataguard 11g

    735967
      Hello All,

      How to know the delay in redo log apply on Active Dataguard 11g...

      Do we need to wait till log switch occurs?

      Or is it recommended to schedule a log switch every 15 min, no matter data is updated/inserted or not in primary?

      Please suggest...

      Oracle : oracle 11g Release 2
      OS : RHEL 5.4

      Thanks

      Edited by: user1687821 on Feb 23, 2012 12:02 AM
        • 1. Re: How to know the delay in redo log apply on Active Dataguard 11g
          CKPT
          How to know the delay in redo log apply on Active Dataguard 11g...
          have you configured dataguard broker? If so you can monitor by DGMGRL utility also,
          if not you have to use views.
          SQL> SELECT * FROM (
                SELECT sequence#, archived, applied,
                       TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
                 FROM sys.v$archived_log
                 ORDER BY sequence# DESC)
              WHERE ROWNUM <= 10
              /
          From primary:-
          SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

          From standby:-
          SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#:
          Do we need to wait till log switch occurs?
          What is the redo transport service you are using? is it LGWR or ARCH ?
          If you are using LGWR there would be delay only a commit , in case of archive redo transport you have to wait until log switch occurred on primary.

          Or is it recommended to schedule a log switch every 15 min, no matter data is updated/inserted in primary?
          No, no need to schedule any Jobs to perform log switch manually,

          configure LGWR service in log_archive_dest_2 for remote destination.
          So there would be not much delay as you are expecting.

          BTW, make sure your network speed is good enough.

          some more views
          SQL> select * from v$dataguard_stats;
          
          NAME                           VALUE      UNIT                           TIME_COMPUTED
          ------------------------------ ---------- ------------------------------ ------------------------------
          apply finish time                         day(2) to second(1) interval
          apply lag                                 day(2) to second(0) interval
          estimated startup time         48         second
          standby has been open          Y
          transport lag                             day(2) to second(0) interval
          Edited by: CKPT on Feb 23, 2012 1:56 PM
          • 2. Re: How to know the delay in redo log apply on Active Dataguard 11g
            mseberg
            Hello;

            If you are trying to avoid a delay

            Make sure LOG_ARCHIVE_MAX_PROCESSES is set to a higher value.

            Make sure you have the correct number and size of Standby logs

            Consider using Real Time apply.



            To Set a Delay  to the Log Apply

            (delays shown in minutes)

            Delay a half an hour

            ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby DELAY=30';

            Delay an hour

            ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby DELAY=60';

            Delay two hours

            ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby DELAY=120';

            Delay three hours

            ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby DELAY=180';

            Delay four hours

            ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby DELAY=240';


            Best Regards

            mseberg

            Edited by: mseberg on Feb 23, 2012 2:49 AM
            • 3. Re: How to know the delay in redo log apply on Active Dataguard 11g
              735967
              Hello CKPT,

              Thank you for the valuable information...

              We have not configured databroker.

              Output of the query

              SELECT * FROM (
              SELECT sequence#, archived, applied,
              TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
              FROM sys.v$archived_log
              ORDER BY sequence# DESC)
              WHERE ROWNUM <= 10
              /

              Primary...


              SEQUENCE#     ARCHIVED     APPLIED     COMPLETED

              29680          YES          YES     2012/02/23 01:11
              29680          YES          NO     2012/02/23 01:11
              29679          YES          NO     2012/02/22 23:11
              29679          YES          YES     2012/02/22 23:11
              29678          YES          YES     2012/02/22 23:11
              29678          YES          NO     2012/02/22 23:11
              29677          YES          YES     2012/02/22 22:32
              29677          YES          NO     2012/02/22 22:32
              29676          YES          YES     2012/02/22 22:02
              29676          YES          NO     2012/02/22 22:02


              Standby...

              SEQUENCE# ARC APP COMPLETED
              ---------- --- --- -------------------
              29680 YES YES 2012/02/23 01:11
              29679 YES YES 2012/02/22 23:11
              29678 YES YES 2012/02/22 23:11
              29677 YES YES 2012/02/22 22:32
              29676 YES YES 2012/02/22 22:02
              29675 YES YES 2012/02/22 21:24
              29674 YES YES 2012/02/22 19:24
              29673 YES YES 2012/02/22 18:59
              29672 YES YES 2012/02/22 17:42
              29671 YES YES 2012/02/22 17:41

              Primary shows yes as well as no...

              Next,

              From primary:-
              SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
              THREAD#     MAX(SEQUENCE#)

              1     29680

              From standby:-
              SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

              THREAD# MAX(SEQUENCE#)
              ---------- --------------
              1 29680

              What is the redo transport service you are using? is it LGWR or ARCH ?

              Output of query select * from v$parameter where name like 'log_archive_dest_2' shows below value...

              SERVICE=b_stdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=b_stdb

              So is it lgwr already configured...? if yes then how do i see the delay in both servers..

              Yes, the network is good as they both resides in same LAN within same rack


              Thanks...
              • 4. Re: How to know the delay in redo log apply on Active Dataguard 11g
                735967
                Hello mseberg,

                Thank you for the reply...

                In our environment the value for log_archive_dest_2 is SERVICE=b_stdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=b_stdb, how do i see the current value for the delay configured...

                Thanks
                • 5. Re: How to know the delay in redo log apply on Active Dataguard 11g
                  mseberg
                  Hello;

                  If one is set then

                  SQL>Show parameter log_archive_dest_2

                  Should show it.

                  Best Regards

                  mseberg
                  • 6. Re: How to know the delay in redo log apply on Active Dataguard 11g
                    CKPT
                    Your standby is very much sync with primary database.
                    SERVICE=b_stdb LGWR ASYNC
                    You are using LGWR service as redo transport which is highly recommended. Where there is very less data lag between primary & standby.

                    Also have you checked view v$dataguard_stats for column transport_lag ?

                    TRANSPORT LAG: How much redo data (in time units) generated by the primary database is not available or applicable on the standby database at the time of computation.

                    http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1080.htm


                    When you have not configured broker, You can monitor only from views.

                    V$archived_log, V$dataguard_stats, v$managed_standby ....

                    HTH..

                    Edited by: CKPT on Feb 23, 2012 2:31 PM
                    • 7. Re: How to know the delay in redo log apply on Active Dataguard 11g
                      735967
                      Hello mseberg

                      Thanks again, the output for SQL>Show parameter log_archive_dest_2 is as below


                      SQL> Show parameter log_archive_dest_2

                      NAME TYPE VALUE
                      ------------------------------------ ---------- ------------------------------
                      log_archive_dest_2 string SERVICE=b_stdb LGWR ASYNC
                      VALID_FOR=(ONLINE_LOGFILES,PRI
                      MARY_ROLE) DB_UNIQUE_NAME=b_stdb
                      SQL>

                      Please suggest

                      Thanks
                      • 8. Re: How to know the delay in redo log apply on Active Dataguard 11g
                        735967
                        CKPT,

                        Thank you for the reply again

                        The output is as below

                        SQL> select * from v$dataguard_stats;

                        NAME VALUE UNIT TIME_COMPUTED
                        ------------------------- ---------------- ------------------------------ ------------------------------
                        apply finish time +00 00:00:00.0 day(2) to second(1) interval 23-FEB-2012 03:02:16
                        apply lag +00 01:50:49 day(2) to second(0) interval 23-FEB-2012 03:02:16
                        estimated startup time 11 second 23-FEB-2012 03:02:16
                        standby has been open N 23-FEB-2012 03:02:16
                        transport lag +00 01:50:49 day(2) to second(0) interval 23-FEB-2012 03:02:16

                        SQL> !date
                        Thu Feb 23 03:03:34 CST 2012

                        SQL>

                        Thanks

                        Edited by: user1687821 on Feb 23, 2012 1:04 AM
                        • 9. Re: How to know the delay in redo log apply on Active Dataguard 11g
                          CKPT
                          apply finish time +00 00:00:00.0 day(2) to second(1) interval 23-FEB-2012 03:02:16
                          Here apply finish time is "Zero" If there are gaps in the log files, this parameter shows the time it will take to resolve the gap.
                          apply lag +00 01:50:49 day(2) to second(0) interval 23-FEB-2012 03:02:16
                          transport lag +00 01:50:49 day(2) to second(0) interval 23-FEB-2012 03:02:16
                          time that the redo data not available (or) behind on the standby database with the primary database.
                          SQL> !date
                          Thu Feb 23 03:03:34 CST 2012
                          If you see your current system date, and last apply finish time its not even a second too. So probably there is no delay i can say.
                          Or you can test by setting delay parameter in log_archive_dest_2 (or) disable log_archive_Dest_2 for some time perform couple of log switches and watch out for lags between primary & standby and how this view is working.
                          • 10. Re: How to know the delay in redo log apply on Active Dataguard 11g
                            735967
                            CKPT,

                            Thanks for the information...

                            I will try for the delay parameter and check...

                            One more thing,

                            STANDBY_MAX_DATA_DELAY
                            A session parameter
                            Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when they can be queried on the active standby.

                            The above lines are from

                            http://www.oracledatabase12g.com/archives/oracle-active-data-guard-technical-overview.html,

                            will this helpout to set as permanent parameter, if i include this in init file and recreate spfile from init... or am i going totally wrong

                            Please suggest

                            Thanks
                            • 11. Re: How to know the delay in redo log apply on Active Dataguard 11g
                              CKPT
                              One more thing,

                              STANDBY_MAX_DATA_DELAY
                              A session parameter
                              Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when they can be queried on the active standby.

                              The above lines are from

                              http://www.oracledatabase12g.com/archives/oracle-active-data-guard-technical-overview.html,

                              will this helpout to set as permanent parameter, if i include this in init file and recreate spfile from init... or am i going totally wrong
                              You can use as below

                              SQL> alter system set log_archive_dest_2='SERVICE=b_stdb LGWR ASYNC delay=120 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=b_stdb'

                              this parameter also applicable
                              STANDBY_MAX_DATA_DELAY
                              
                              Syntax:
                              
                              STANDBY_MAX_DATA_DELAY =  { integer | NONE } 
                              
                              In an Active Data Guard environment, this session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.
                              
                              If STANDBY_MAX_DATA_DELAY is set to the default value of NONE, queries issued to a physical standby database will be executed regardless of the apply lag on that database.
                              
                              If STANDBY_MAX_DATA_DELAY is set to a non-zero value, a query issued to a physical standby database will be executed only if the apply lag is less than or equal to STANDBY_MAX_DATA_DELAY. Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.
                              
                              If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.
                              Source:-
                              http://docs.oracle.com/cd/E24693_01/server.11203/e17118/statements_2013.htm
                              • 12. Re: How to know the delay in redo log apply on Active Dataguard 11g
                                735967
                                CKPT,

                                Thank you for your time and valuable information...

                                Thanks Again..