Re: Increasing Size of SGA_MAX in Oracle 10G

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 27 Apr 2009 06:08:23 +0200
Message-ID: <49f52fb5$0$11778$426a74cc_at_news.free.fr>

<navneel.singh_at_gmail.com> a écrit dans le message de news: 9f0e3b59-ca66-40c8-a31e-070edc580a5b_at_j9g2000prh.googlegroups.com... On Apr 27, 3:24 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> navneel.si..._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.- Hide quoted text -
>
> - Show quoted text -

Hi Michael,
Just for knowledge sake, if what you say is true for all Oracle DB, then how was i able to do the same on another DB while the instance was still running. This was the single instance DB that i was talking about in my earlier post. Or is it that what you saying holds true for clustered DB whereby we need to switch instance 2 off, while we work on instance 1, restart instance 1. If this is true, do we need to do the same on the second instance or do we simply just restart instance 2 since the spfile used by both instance is the same, it being hosted on the ASM disk.

Thanks.

---

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

SQL> alter system set sga_max_size=4000m scope=spfile;

System altered.

Regards
Michel Received on Sun Apr 26 2009 - 23:08:23 CDT