4 Replies Latest reply: Dec 21, 2012 9:51 PM by sulimo RSS

    ASMM - sga dynamic components and standby

    sulimo
      Are SGA dynamic modifications "transferred" from the primary to the standby?
      i.e: if __shared_pool is resized in primary will this also trigger standby?

      Trying to determine if dynamic pools may somehow end up being different upon switchover/failover or if it's possible that upon switchover standby's dynamic shared_pool size results smaller than that of former primary
        • 1. Re: ASMM - sga dynamic components and standby
          jgarry
          Don't know for sure, but I believe each instance has its own double underscore parameters. They may be very different, as not everything that pressures memory is transferred through redo. Remember, the standby is in continuous recovery mode.

          Just a guess, test and know for sure.
          • 2. Re: ASMM - sga dynamic components and standby
            JohnWatson
            Think it through: an ALTER SYSTEM command does not generate redo, therefore it will not be propagated. Or to put it another way, the spfile is not protected by redo.
            • 3. Re: ASMM - sga dynamic components and standby
              sulimo
              Absolutely, yet there are some things DG broker does upon switch over. Was wondering if somehow standby would be "updated" upon switcher...
              But then again, it's SGA as a hole may be smaller to begin with...

              Were you to tell if the following is true or false:

              "After activation standby database may have a SGA configuration different than that of primary"

              what would you say?

              Hole intention here is to determine whether after a switch over it's possible to start getting ORA-04031 on account of dynamic pools being different
              and oracle struggling to re-size them...

              Suppose we have a 6Gb SGA... primary has been working full steam for say 2 months and dynamic pools are set by oracle in the following way:
              shared_pool...3Gb
              cache...3Gb

              Now if standby has not acknowledged those dynamic changes, it may be using values of it's own... maybe a larger cache... say
              shared_pool...1Gb
              cache...5Gb

              Now we switch-over and if sga settings are not "synced", then new primary will have 2Gb less of shared pool...

              Application kicks in, shared pool runs low... Oracle will have to move 1 or 2Gbs from cache to shared pool, this will cause serious latching and most likely ORA-04031

              so is that likely to happen?
              • 4. Re: ASMM - sga dynamic components and standby
                sulimo
                FYI, seems that was what happened