This discussion is archived
4 Replies Latest reply: Dec 21, 2012 7:51 PM by sulimo RSS

ASMM - sga dynamic components and standby

sulimo Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    FYI, seems that was what happened

Legend

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