This discussion is archived
12 Replies Latest reply: Feb 23, 2012 1:39 AM by 735967 RSS

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

735967 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    CKPT,

    Thank you for your time and valuable information...

    Thanks Again..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points