Re: Increasing Size of SGA_MAX in Oracle 10G
From: <navneel.singh_at_gmail.com>
Date: Mon, 27 Apr 2009 20:15:14 -0700 (PDT)
Message-ID: <f7ddefe4-7558-431d-92f7-1cd2c2300326_at_d2g2000pra.googlegroups.com>
On Apr 28, 12:04 pm, navneel.si..._at_gmail.com wrote:
> On Apr 27, 6:11 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > navneel.si..._at_gmail.com schrieb:
>
> > > 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.
>
> > You have a typo.
>
> > SQL> alter system set sga_max_size=400M scop=spfile;
> > alter system set sga_max_size=400M scop=spfile
> > *
> > ERROR at line 1:
> > ORA-02095: specified initialization parameter cannot be modified
>
> > SQL> alter system set sga_max_size=400M scope=spfile;
>
> > System altered.
>
> > Best regards
>
> > Maxim- Hide quoted text -
>
> > - Show quoted text -
>
> Ok, so i was able to alter the system .....and was successfully able
> to restart the DB without any errors but when i execute the command
> 'show parameter spfile' i get an empty string
>
> SQL> show parameter spfile
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> spfile string
>
> Also when i try to check the new value of sga_max_size, it is the same
> as before.- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 27 Apr 2009 20:15:14 -0700 (PDT)
Message-ID: <f7ddefe4-7558-431d-92f7-1cd2c2300326_at_d2g2000pra.googlegroups.com>
On Apr 28, 12:04 pm, navneel.si..._at_gmail.com wrote:
> On Apr 27, 6:11 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > navneel.si..._at_gmail.com schrieb:
>
> > > 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.
>
> > You have a typo.
>
> > SQL> alter system set sga_max_size=400M scop=spfile;
> > alter system set sga_max_size=400M scop=spfile
> > *
> > ERROR at line 1:
> > ORA-02095: specified initialization parameter cannot be modified
>
> > SQL> alter system set sga_max_size=400M scope=spfile;
>
> > System altered.
>
> > Best regards
>
> > Maxim- Hide quoted text -
>
> > - Show quoted text -
>
> Ok, so i was able to alter the system .....and was successfully able
> to restart the DB without any errors but when i execute the command
> 'show parameter spfile' i get an empty string
>
> SQL> show parameter spfile
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> spfile string
>
> Also when i try to check the new value of sga_max_size, it is the same
> as before.- Hide quoted text -
>
> - Show quoted text -
ok, so i tried to jsut startup the database and it came online but i cannot view my spfile as stated in my earlier post. Actually when i try to start the database by specifying the spfile it gives me the following error message:
startup pfile='+ASMDISK/INSTANCE/spfileINSTANCE_NAME.ora'
LRM-00109: could not open parameter file '+ASMDISK/INSTANCE/
spfileINSTANCE_NAME.ora'
ORA-01078: failure in processing system parameters
But i did create pfiles from the same location and when starting the database with that pfile, it starts normally. Received on Mon Apr 27 2009 - 22:15:14 CDT