12 Replies Latest reply: Nov 22, 2012 12:58 AM by Girish Sharma RSS

    SGA size not able to change in 11GR2

    Harsh_v
      Hello experts, i am facing problem. i am not able increase size, not SGA_MAX_SIZE

      Oracle vesion=11gR2
      OS version= Red hat Linux 5.5
      [root@VCC-WEB-1 ~]# free -g
                   total       used       free     shared    buffers     cached
      Mem:            31         15         16          0          0         20
      
      
      
      [root@VCC-WEB-1 ~]# cat /etc/sysctl.conf
      
      fs.suid_dumpable = 1
      fs.aio-max-nr = 1048576
      fs.file-max = 6815744
      kernel.shmall = 2097152
      kernel.shmmax = 10737418240
      kernel.shmmni = 4096
      kernel.sem = 250 32000 100 128
      net.ipv4.ip_local_port_range = 9000 65500
      net.core.rmem_default = 262144
      net.core.rmem_max = 4194304
      net.core.wmem_default = 262144
      net.core.wmem_max = 1048586
      When i was change sga size, then it was not change sga. it take same size again. as i share my log my production server.

      [oracle@VCC-WEB-1 ~]$ sqlplus "/as sysdba"
      
      SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 10:33:46 2012
      
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      
      SQL> show parameter sga
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      lock_sga                             boolean     FALSE
      pre_page_sga                         boolean     FALSE
      sga_max_size                         big integer 2336M
      sga_target                           big integer 0
      
      
      SQL> alter system set sga_max_size=4G scope=spfile;
      
      SQL> shu immediate
      
      SQL> startup
      
      SQL> show parameter sga
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      lock_sga                             boolean     FALSE
      pre_page_sga                         boolean     FALSE
      sga_max_size                         big integer 2336M
      sga_target                           big integer 0
      Alert logfile.
      Wed Nov 21 00:10:00 2012
      Instance shutdown complete
      Wed Nov 21 00:11:40 2012
      Starting ORACLE instance (normal)
      LICENSE_MAX_SESSION = 0
      LICENSE_SESSIONS_WARNING = 0
      Picked latch-free SCN scheme 2
      Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
      Autotune of undo retention is turned on.
      IMODE=BR
      ILAT =27
      LICENSE_MAX_USERS = 0
      SYS auditing is disabled
      Starting up:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options.
      Using parameter settings in client-side pfile /u01/app/oracle/product/11.2.0/dbs/initorcl.ora on machine VCC-WEB-1
      System parameters with non-default values:
        processes                = 150
        sga_max_size             = 2336M
        memory_target            = 12864M
        control_files            = "/u01/app/oracle/oradata/orcl/control01.ctl"
        control_files            = "/u01/app/oracle/flash_recovery_area/orcl/control02.ctl"
        db_block_size            = 8192
        compatible               = "11.2.0.0.0"
        db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
        db_recovery_file_dest_size= 3852M
        undo_tablespace          = "UNDO"
        remote_login_passwordfile= "EXCLUSIVE"
        db_domain                = ""
        dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
        local_listener           = "(address=(protocol=tcp)(host=localhost)(port=1521))"
        audit_file_dest          = "/u01/app/oracle/admin/orcl/adump"
        audit_trail              = "DB"
        db_name                  = "orcl"
        open_cursors             = 300
        diagnostic_dest          = "/u01/app/oracle"
      Wed Nov 21 00:11:41 2012
      PMON started with pid=2, OS id=31773
      Wed Nov 21 00:11:41 2012
      VKTM started with pid=3, OS id=31775 at elevated priority
      VKTM running at (10)millisec precision with DBRM quantum (100)ms
      Wed Nov 21 00:11:41 2012
      GEN0 started with pid=4, OS id=31779
      Wed Nov 21 00:11:41 2012
      DIAG started with pid=5, OS id=31781
      Wed Nov 21 00:11:41 2012
      DBRM started with pid=6, OS id=31783
      Wed Nov 21 00:11:41 2012
      PSP0 started with pid=7, OS id=31785
      Wed Nov 21 00:11:41 2012
      DIA0 started with pid=8, OS id=31787
      Wed Nov 21 00:11:41 2012
      MMAN started with pid=9, OS id=31789
      Wed Nov 21 00:11:41 2012
      DBW0 started with pid=10, OS id=31791
      Wed Nov 21 00:11:41 2012
      LGWR started with pid=11, OS id=31793
      Wed Nov 21 00:11:41 2012
      CKPT started with pid=12, OS id=31795
      Wed Nov 21 00:11:41 2012
      SMON started with pid=13, OS id=31797
      Wed Nov 21 00:11:41 2012
      RECO started with pid=14, OS id=31799
      Wed Nov 21 00:11:41 2012
      MMON started with pid=15, OS id=31801
      starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
      Wed Nov 21 00:11:41 2012
      MMNL started with pid=16, OS id=31803
      starting up 1 shared server(s) ...
      ORACLE_BASE from environment = /u01/app/oracle
      Wed Nov 21 00:11:42 2012
      ALTER DATABASE   MOUNT
      Successful mount of redo thread 1, with mount id 1327708734
      Database mounted in Exclusive Mode
      Lost write protection disabled
      Completed: ALTER DATABASE   MOUNT
      Wed Nov 21 00:11:46 2012
      ALTER DATABASE OPEN
      Thread 1 opened at log sequence 227
        Current log# 7 seq# 227 mem# 0: /oradata1/oradata/redo07.log
      Successful open of redo thread 1
      MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
      SMON: enabling cache recovery
      Successfully onlined Undo Tablespace 13.
      Verifying file header compatibility for 11g tablespace encryption..
      Verifying 11g file header compatibility for tablespace encryption completed
      SMON: enabling tx recovery
      Database Characterset is AL32UTF8
      No Resource Manager plan active
      replication_dependency_tracking turned off (no async multimaster replication found)
      Starting background process QMNC
      Wed Nov 21 00:11:48 2012
      QMNC started with pid=20, OS id=32399
      Completed: ALTER DATABASE OPEN
      Wed Nov 21 00:11:49 2012
      db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
      user-specified limit on the amount of space that will be used by this
      database for recovery-related files, and does not reflect the amount of
      space available in the underlying filesystem or ASM diskgroup.
      Starting background process CJQ0
      Wed Nov 21 00:11:51 2012
      CJQ0 started with pid=21, OS id=32448
      Setting Resource Manager plan SCHEDULER[0x3004]:DEFAULT_MAINTENANCE_PLAN via scheduler window
      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
      Wed Nov 21 00:11:54 2012
      Starting background process VKRM
      Wed Nov 21 00:11:54 2012
      VKRM started with pid=23, OS id=32730
      Wed Nov 21 00:12:16 2012
      Shutting down instance (immediate)
      Shutting down instance: further logons disabled
      Stopping background process QMNC
      Wed Nov 21 00:12:18 2012
      Stopping background process CJQ0
      Stopping background process MMNL
      Stopping background process MMON
      License high water mark = 6
      All dispatchers and shared servers shutdown
      ALTER DATABASE CLOSE NORMAL
      Wed Nov 21 00:12:23 2012
      SMON: disabling tx recovery
      SMON: disabling cache recovery
      I was it throw it pfile also but face same issue.
        • 1. Re: SGA size not able to change in 11GR2
          Shivananda Rao
          Hello,

          It looks like you are using AMM having MEMORY_TARGET set, then do not set SGA. Let Oracle handle it.
          You don't need to set SGA if you already have MEMORY_TARGET set. Oracle gives the preference to MEMORY_TARGET parameter over the SGA_MAX_SIZE when MEMORY_TARGET is set.
          • 2. Re: SGA size not able to change in 11GR2
            Harsh_v
            I have already set memory_target and memory_max_target with enough space. but face same issue

            SQL> show parameter memory

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            hi_shared_memory_address integer 0
            memory_max_target big integer 12864M
            memory_target big integer 12864M
            shared_memory_address integer 0
            • 3. Re: SGA size not able to change in 11GR2
              Osama_Mustafa
              Post
              Show parameter memory ;
              • 4. Re: SGA size not able to change in 11GR2
                Harsh_v
                Dear Osama_mustafa,

                SQL> Show parameter memory ;

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                hi_shared_memory_address integer 0
                memory_max_target big integer 12864M
                memory_target big integer 12864M
                shared_memory_address integer 0
                • 5. Re: SGA size not able to change in 11GR2
                  Shivananda Rao
                  Harshit DBA india wrote:
                  I have already set memory_target and memory_max_target with enough space. but face same issue

                  SQL> show parameter memory

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  hi_shared_memory_address integer 0
                  memory_max_target big integer 12864M
                  memory_target big integer 12864M
                  shared_memory_address integer 0
                  Hello,

                  That's what I am intending to say. When you have set MEMORY_TARGET and SGA_MAX_SIZE, Oracle gives preference to MEMORY_TARGET. Which is why the changes you make to SGA_MAX_SIZE would not be seen after you bounce the database.

                  Refer this What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?

                  If the reply seems to be helpful, then consider closing the thread by providing appropriate points.
                  • 6. Re: SGA size not able to change in 11GR2
                    Harsh_v
                    defiantly Shivananda Rao i will close it.
                    please tell me one thing more. if i want enable AMM ( 4G for SGA+ 2G PGA) in 11g. then my all memory figure look like that.
                    memory_max_target    12G
                    memory_target            6G
                    sga_max_size               0
                    sga_target                   0
                    Am i right Sir ?
                    • 7. Re: SGA size not able to change in 11GR2
                      CSM.DBA
                      Harshit DBA india wrote:
                      Using parameter settings in client-side pfile /u01/app/oracle/product/11.2.0/dbs/initorcl.ora on machine VCC-WEB-1
                      Your Database is using PFILE but not the SPFILE, I guess.

                      Please do the below three steps.

                      1. Remove SGA_MAX_SIZE from your PFILE

                      2. Please create an SPFILE from PFILE and restart the Database

                      3. set SGA_TARGET by issuing "ALTER SYSTEM SET SGA_TARGET=4G SCOPE=SPFILE;" and bounce the Database again. This will set the minimum limit to be allocated to the SGA from the overall MEMORY_TARGET.

                      Hope this helps.

                      PS:Please dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding

                      regards,
                      CSM
                      • 8. Re: SGA size not able to change in 11GR2
                        Aman....
                        Harshit DBA india wrote:
                        defiantly Shivananda Rao i will close it.
                        please tell me one thing more. if i want enable AMM ( 4G for SGA+ 2G PGA) in 11g. then my all memory figure look like that.
                        memory_max_target    12G
                        memory_target            6G
                        sga_max_size               0
                        sga_target                   0
                        Am i right Sir ?
                        The answer is yes but why don't you spend some time reading about AMM from oracle docs,
                        http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory003.htm#ADMIN11011

                        Aman....
                        • 9. Re: SGA size not able to change in 11GR2
                          Osama_Mustafa
                          Automatic memory management is configured using two new initialization parameters:

                          MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
                          MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

                          When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.


                          Please check
                          http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php#parameters
                          • 10. Re: SGA size not able to change in 11GR2
                            Shivananda Rao
                            Harshit DBA india wrote:
                            defiantly Shivananda Rao i will close it.
                            please tell me one thing more. if i want enable AMM ( 4G for SGA+ 2G PGA) in 11g. then my all memory figure look like that.
                            memory_max_target    12G
                            memory_target            6G
                            sga_max_size               0
                            sga_target                   0
                            Am i right Sir ?
                            Hello,

                            You are perfectly right. Refer this document http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm
                            • 11. Re: SGA size not able to change in 11GR2
                              Harsh_v
                              thanks Aman, Shivananda and specially Osama and CSM.DBA . thanks for help

                              Edited by: Harshit DBA india on Nov 22, 2012 12:31 PM
                              • 12. Re: SGA size not able to change in 11GR2
                                Girish Sharma
                                Harshit see this link :
                                http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx

                                This have a good diagram.