This discussion is archived
4 Replies Latest reply: Mar 22, 2013 2:32 AM by Lonion RSS

Delay=60 not working in 11.2.0.2 Data Guard

Arun Natarajan-OC Explorer
Currently Being Moderated
Hi Friends,

I am using 11.2.0.2 Data Guard.

I had set Delay=60 for Standby Database in init parameters of Primary Database and bounced both Primary and Standby DB.

But as soon as i perform log switch in Primary DB it is being applied in Standby DB immediately ignoring my Delay parameter.

The Physical Standby is mounted and redo apply is enabled.

Please let me know the reason.



Parameters:
-----------------

LOG_ARCHIVE_DEST_1=
'LOCATION=/data/dg/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC DELAY=60
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE



Standby:
-------------


SEQUENCE# APPLIED
---------- ---------
1208 YES
1209 YES
1210 YES
1211 YES
1212 YES
1213 YES
1214 YES
1215 YES
1216 IN-MEMORY


Regards,
DB
  • 1. Re: Delay=60 not working in 11.2.0.2 Data Guard
    mseberg Guru
    Currently Being Moderated
    Hello;

    I'm pretty sure you can cancel a delay by
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    Also make sure you set DELAY on both the Primary and Standby.

    What you show looks correct.

    If IN-MEMORY shows for your delay period then you may have no issue.

    Best Regards

    mseberg
  • 2. Re: Delay=60 not working in 11.2.0.2 Data Guard
    Arun Natarajan-OC Explorer
    Currently Being Moderated
    Hi Friends,

    I performed multiple log switches. The In Memory is only for the Last sequence whereas the other previous sequences were applied as YES.

    Regards,
    DB
  • 3. Re: Delay=60 not working in 11.2.0.2 Data Guard
    mseberg Guru
    Currently Being Moderated
    Hello;

    There must be some small mistake.

    Test

    Release 11.2.0.3.0


    Test of Sync before
    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRIMARY    MYHOST                  221         221 20-MAR/08:33         0
    1 row selected.
    Setting of log_archive_dest_n
    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC DELAY=90 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
    Perform several log switches


    Check the time

    SQL> !date
    Wed Mar 20 08:44:15 CDT 2013



    Changes to Standby = None


    Query used to check apply :

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


    Check the time again

    SQL> !date
    Wed Mar 20 08:49:45 CDT 2013


    Test of Sync after
    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRIMARY    MYHOST                  226         221 20-MAR/08:33         5
    1 row selected.
    Works.


    Test after 10 minutes

    SQL> !date
    Wed Mar 20 08:54:54 CDT 2013
    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRIMARY    MYHOST                  226         221 20-MAR/08:33         5
    1 row selected.
    half hour check

    SQL> !date
    Wed Mar 20 09:16:25 CDT 2013

    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRIMARY    MYHOST                    226         221 20-MAR/08:33         5
    1 row selected.
    Much later after the delay has past the logs are applied as expected.


    SQL> !date
    Wed Mar 20 12:12:53 CDT 2013

    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRIMARY    MYHOST                   226         226 20-MAR/08:44         0
    1 row selected.
    Standby alert log
    Media Recovery Delayed for 88 minute(s) (thread 1 sequence 222)
    Wed Mar 20 10:01:12 2013
    Media Recovery Log /u01/app/oracle/flash_recovery_area/STANDBY/archivelog/2013_03_20/o1_mf_1_222_8nmgnnjk_.arc
    Media Recovery Log /u01/app/oracle/flash_recovery_area/STANDBY/archivelog/2013_03_20/o1_mf_1_223_8nmgno26_.arc
    Media Recovery Delayed for 89 minute(s) (thread 1 sequence 224)
    Best Regards

    mseberg
  • 4. Re: Delay=60 not working in 11.2.0.2 Data Guard
    Lonion Newbie
    Currently Being Moderated
    did you enable real-time apply in standby database ?

    Query it on your standby database using the following:
    SQL> select recovery_mode from v$archive_dest_status where dest_id = 1;

Legend

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