4 Replies Latest reply: Mar 22, 2013 4:32 AM by Lonion RSS

    Delay=60 not working in 11.2.0.2 Data Guard

    Arun Natarajan-OC
      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
          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
            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
              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
                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;