This discussion is archived
7 Replies Latest reply: May 27, 2009 4:27 AM by Aman.... RSS

Increasing SGA_MAX_SIZE  in Oracle 10.2.0.1

user640001 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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....

Legend

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