4 Replies Latest reply: Jan 23, 2013 1:07 AM by Girish Sharma RSS

    SGA allocation

    633234
      Hello folks,

      We have a dataguard setup with primary database on 10.2.0.3 on AIX. I have kept all the parameters same in parameter file but querying SHOW SGA gives different results on primary database and DR database which causes ORA-04030 - Out of memory error. Can you please suggest some guidance?

      Parameters on Primary:
      --------------------------------

      SQL> show parameter db_cache

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_cache_advice string OFF
      db_cache_size big integer 368M
      SQL> show parameter shared_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      hi_shared_memory_address integer 0
      max_shared_servers integer
      shared_memory_address integer 0
      shared_pool_reserved_size big integer 5M
      shared_pool_size big integer 224M
      shared_server_sessions integer
      shared_servers integer 0
      SQL> show parameter sga_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      sga_max_size big integer 2512M
      sga_target big integer 2000M
      SQL> show parameter pga_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      pga_aggregate_target big integer 250M
      SQL>

      ===========================
      Parameters on DR:
      -----------------------------
      SQL> show parameter db_cache

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_cache_advice string OFF
      db_cache_size big integer 368M
      SQL> show parameter shared_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      hi_shared_memory_address integer 0
      max_shared_servers integer
      shared_memory_address integer 0
      shared_pool_reserved_size big integer 5M
      shared_pool_size big integer 224M
      shared_server_sessions integer
      shared_servers integer 0
      SQL> show parameter sga_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      sga_max_size big integer 2512M
      sga_target big integer 2000M
      SQL> show parameter pga_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      pga_aggregate_target big integer 250M
      SQL>

      ==================================
      Primary SGA --

      SQL> show sga

      Total System Global Area 2634022912 bytes
      Fixed Size 2075184 bytes
      Variable Size 1761609168 bytes
      Database Buffers 754974720 bytes
      Redo Buffers 115363840 bytes
      SQL>

      DR SGA --

      SQL> show sga

      Total System Global Area 2634022912 bytes
      Fixed Size 2075184 bytes
      Variable Size 805307856 bytes
      Database Buffers 1711276032 bytes
      Redo Buffers 115363840 bytes
      SQL>

      Why primary and DR Variable size & Database buffers is different?