7 Replies Latest reply on May 27, 2009 11:27 AM by Aman....

    Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1

    user640001
      Hi,


      we have successfully upgrade our database from 8.1.7.0 to 10.2.0.1

      now in the database control, SGA max size is 524MB

      i want to increase it, as to increase the size of SHARED_POOL_SIZE and DATBASE_BUFFER size

      i have checked the init.ora file, in that there is no parameter mentioned as sga_max_size


      from where and how to increase the SGA_MAX_SIZE in 10g version ?



      with regards
        • 1. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
          Mohammed Mehraj Hussain
          Oracle 10g has a new feature called automatic memory management ,
          This feature can be enabled using the parameter sga_target

          for more details look at the documentation
          http://www.oracle.com/technology/pub/articles/10gdba/week17_10gdba.html
          http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10755/initparams190.htm

          Regards,
          Mohammed Mehraj Hussain
          • 2. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
            602725
            Hi,
            SGA_MAX_SIZE parameter is not dynamic...
            If you are using a PFILE, shutdown the database, change the parameter in the pfile, recreate an spfile and restart the database.

            On the other han, SGA_TARGET is a dynamic parameter
            • 3. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
              brtk
              According to MOS 189268.1 the default value for MAX_SGA_SIZE is "the SGA size".
              So it's better to set it up explicit.

              Bartek
              • 4. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
                669771
                Hi,

                There are two parameter which work close to each other
                SGA_MAX_SIZE and SGATAGET
                You must set kernel parameters correctly per documentation
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#i12483

                Regards,
                Tom
                http://asktom.cz
                • 5. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
                  user640001
                  Hi,


                  Database is using init.ora file in the pfile folder

                  In the init.ora file i have increased the SGA_MAX_SIZE parameter to 1024Mb from 524Mb

                  Now, from dbcontrol screen, Automatic Shared Memory Managment is Disabled and i want to make it ENABLE

                  now when i click on ENABLE button, on the next page it shows total SGa size i.e 332 Mb

                  and further wheni click on "OK" button for ASSM configuration, it shows the error as below :-


                  1.) SGATargetSize - ORA-02097 : parameter cannot be modified because specified vale is invalid ORA : cannot set sga_target due to existing internal settings, see alert log for information


                  in the alert log , it mentions

                  Cannot set sga_target with db_block_buffers set

                  and

                  Alter system set java_pool_size='0' SCOPE=MEMORY;



                  2) sharedPoolsize - ORA-02097 parameter cannot be modified because specified value is invalid ORA-04034: unable to shrink pool to specified size


                  now i just mentioned the memory component details for reference :

                  shared pool : 104 Mb
                  buffer cache : 169 Mb
                  large pool : 0
                  java pool : 4 Mb
                  other : 54 Mb
                  ----------------------------------
                  total SGA -- 332 Mb

                  ----------------------------------------
                  Maximum SGA Size -- 1024 Mb


                  how to make ASSM Enable ???



                  With Regards
                  • 6. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
                    Mohammed Mehraj Hussain
                    Read this page

                    http://www.oracle.com/technology/pub/articles/10gdba/week17_10gdba.html
                    • 7. Re: Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1
                      Aman....
                      Db_block_buffers can't be used when you are using the Automatic Memory Management. So you need to edit your parameter file and replace this parameter with DB_CACHE_SIZE parameter. Also set SGA_TARGET to some non-zero value and thanrestart the database.

                      HTH
                      Aman....