Re: Increasing Size of SGA_MAX in Oracle 10G

From: The Boss <nltaal_at_baasbovenbaas.demon.nl>
Date: Tue, 28 Apr 2009 07:06:28 -0700 (PDT)
Message-ID: <e68123a5-5de6-467c-9750-5d8e7af5affe_at_k19g2000prh.googlegroups.com>

On 28 apr, 05:15, 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.

You can't specify a spfile in the pfile startup-parameter like you did.
If a startup using a non-default spfile is required, you have to create a single-line pfile containing the path and name of the spfile, something like this:
SPFILE = $ORACLE_HOME/<....>/your_spfile and use that pfile in your startup.

HTH

--
Jeroen

Received on Tue Apr 28 2009 - 09:06:28 CDT