5 Replies Latest reply: May 4, 2012 12:11 PM by DD RSS

    defer log shipping to physical standby when applying patch

    DD
      I am applying a CPU (patchset) to a dataguard primary and physical standby using Doc ID 278641.1. I need to temporarily stop archivelog shipping from primary to standby. The documentation gives this command example:

      alter system set log_archive_dest_state_X=defer scope=both sid='*'

      Where X is the number of the destination used for shipping redo to the standby site.

      So what is X? In searching online the answer is almost always Log_archive_dest_state_2. But in querying the primary, 2 is inactive and the standby seems to be on 1, so I would think I use 1. Which one do I use?

      On the primary:

      select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

      VALID
      LOG_ARCHIVE_DEST_1
      prod1s

      VALID
      LOG_ARCHIVE_DEST_10
      USE_DB_RECOVERY_FILE_DEST

      show parameter log_archive_dest_1;

      log_archive_dest_1 string SERVICE=prod1s VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) LGW R SYNC AFFIRM DB_UNIQUE_NAME=prod1s

      log_archive_dest_10 string LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY


      show parameter log_archive_dest_state

      NAME TYPE VALUE
      ------------------------------------ ---- ------------------------------
      log_archive_dest_state_1 stri ENABLE
      ng
      log_archive_dest_state_10 stri ENABLE
      ng
      log_archive_dest_state_2 stri ENABLE
      ng
      log_archive_dest_state_3 stri enable
      ng
      log_archive_dest_state_4 stri enable
      ng
      log_archive_dest_state_5 stri enable
      ng
      log_archive_dest_state_6 stri enable
      ng
      log_archive_dest_state_7 stri enable
      ng
      log_archive_dest_state_8 stri enable
      ng
      log_archive_dest_state_9 stri enable
      ng


      On the physical standby:

      select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

      VALID
      LOG_ARCHIVE_DEST_2
      prod1

      VALID
      LOG_ARCHIVE_DEST_10
      USE_DB_RECOVERY_FILE_DEST

      VALID
      STANDBY_ARCHIVE_DEST
      USE_DB_RECOVERY_FILE_DEST

      show parameter log_archive_dest_

      log_archive_dest_10 string LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY
      log_archive_dest_2 string SERVICE=prod1 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) LGWR
      SYNC AFFIRM DB_UNIQUE_NAME=prod1

      show parameter log_archive_dest_state

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_state_1 string DEFER
      log_archive_dest_state_10 string ENABLE
      log_archive_dest_state_2 string enable
      log_archive_dest_state_3 string enable
      log_archive_dest_state_4 string enable
      log_archive_dest_state_5 string enable
      log_archive_dest_state_6 string enable
      log_archive_dest_state_7 string enable
      log_archive_dest_state_8 string enable
      log_archive_dest_state_9 string enable

      dataguard with physical standby
      DB vers: 10.2.0.4.0
      No RAC, ASM, or DG Broker

      Edited by: DD on May 2, 2012 10:05 AM

      Edited by: DD on May 2, 2012 10:05 AM
        • 1. Re: defer log shipping to physical standby when applying patch
          mseberg
          Hello;

          You want this

          alter system set log_archive_dest_state_2=defer; ( you need to do this for your log_archive_dest_state_n ) DEST ID'S that apply to your system ( looks like 2 also )

          I will post my complete how to patch here is a moment.

          0. Disable log shipping from the Primary
          1. Shutdown Standby
          2. Install patch on Standby software only
          3. Startup Standby in recovery mode (do NOT run any SQL at the standby)
          4. Shutdown Primary
          5. Install patch on Primary
          6. Run SQL on Primary
          7. Re-enable log shipping
          8. Monitor the redo apply from Primary to Standby --- this will also upgrade the Standby 
          
          See Oracle support article : How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]
          Please consider closing some of you old questions.

          Best Regards

          mseberg

          Edited by: mseberg on May 2, 2012 9:16 AM
          • 2. Re: defer log shipping to physical standby when applying patch
            CKPT
            Hello,

            log_archive_dest_state_X, Points to remote destinations you been configured, So you can configure either 2, 3, 4..... depends on versions. So check for the available remote destinations configured and defer that destination, If you are using Broker manage only using DGMGRL.

            This is the best note for patching on standby *How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]*
            Edited by: CKPT on May 2, 2012 7:57 PM
            • 3. Re: defer log shipping to physical standby when applying patch
              DD
              Hi mseberg ,

              So I use:

              primary> alter system set log_archive_dest_state_2=defer;

              before patch application and then:

              primary> alter system set log_archive_dest_state_2=enable;

              after patch application. Right?

              Regards,
              David
              • 4. Re: defer log shipping to physical standby when applying patch
                mseberg
                Correct.

                The step by step I posted before is exactly what I tested. So Step 0 is DEFER and Step 7 is Enable.



                Best Regards

                mseberg

                Thanks for closing what you did! No worries.

                ms

                Edited by: mseberg on May 2, 2012 11:23 AM
                • 5. Re: defer log shipping to physical standby when applying patch
                  DD
                  About closing old questions, will be happy to do so, but not sure how to close them. I looked in the FAQ, didn't see anything on closing.

                  Thanks for the quick and clear replies!

                  David

                  marked old questions as answered, I think that should close them.

                  Edited by: DD on May 4, 2012 1:09 PM