Re: Increasing Size of SGA_MAX in Oracle 10G
From: <navneel.singh_at_gmail.com>
Date: Mon, 27 Apr 2009 20:31:05 -0700 (PDT)
Message-ID: <a5c14745-8d11-424d-843c-a42c7b1fca0f_at_b6g2000pre.googlegroups.com>
On Apr 28, 3:15 pm, navneel.si..._at_gmail.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 27 Apr 2009 20:31:05 -0700 (PDT)
Message-ID: <a5c14745-8d11-424d-843c-a42c7b1fca0f_at_b6g2000pre.googlegroups.com>
On Apr 28, 3:15 pm, navneel.si..._at_gmail.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
oh well...got this sorted out. Actually just doing a shutdown and startup at instance level doesnt work, we have to take this at cluster level...also need to restart the asm instance. Thanks everyone for the feedbacks.
Cheers. Received on Mon Apr 27 2009 - 22:31:05 CDT