Re: Increasing Size of SGA_MAX in Oracle 10G

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 28 Apr 2009 08:27:59 -0500
Message-ID: <EvDJl.28644$YU2.11962_at_nlpi066.nbdc.sbc.com>

navneel.singh_at_gmail.com wrote:

> 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.

Sounds like you were not where you thought you were... make sure that you have the correct SID/OHOME defined - in a RAC environment you should have separate OHOME for ASM and RDBMS. Received on Tue Apr 28 2009 - 08:27:59 CDT