Re: Increasing Size of SGA_MAX in Oracle 10G

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sun, 26 Apr 2009 22:24:24 -0500
Message-ID: <Kz9Jl.12905$jZ1.5827_at_flpi144.ffdc.sbc.com>

navneel.singh_at_gmail.com wrote:
> Hi People,
>
> I am running an Oracle 10G clustered database with ASM hosted on a SAM
> system. There are two instances accessing the DB from two different
> machines at any given point of time. Now my question is how do we
> change the parameter value of SGA_MAX_SIZE in such an environment. I
> tried the following and i got the stated error msg
>
> SQL> create pfile from spfile;
>
> File created.
>
> SQL> alter system set sga_max_size=4000m scop=spfile;
> alter system set sga_max_size=4000m scop=spfile
> *
> ERROR at line 1:
> ORA-02095: specified initialization parameter cannot be modified
>
> Now I had also tried the very same thing on another machine hosting
> the same Oracle 10G DB but with a single instance and without ASM and
> it worked perfectly. Is there a reason why the above may have failed
> or any work arounds to this, thought to seek some advice before i
> start playing with a prod machine :-)
>
> Thanks.

sga_max_size – This parameter defines the maximum size of the Oracle SGA, and cannot be modified while the instance is running.

Shut down instance 1, modify the parameter startup instance 1 ditto for instance 2. Received on Sun Apr 26 2009 - 22:24:24 CDT