Re: Foolish Question about SGA_MAX_SIZE

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Thu, 22 Jun 2006 03:18:00 GMT
Message-ID: <J18ru4.496@igsrsparc2.er.usgs.gov>

leo_at_tech-trans.com wrote:
> sjaffarhussain_at_gmail.com 寫道：
>

>>>>> Unfortunately, it cause Oracle stop
>> Did you do it during the heave business hrs, which cause oracle to
>> stop?
>> sga_max_size is not a dynamic parameter, it has to be changed in the
>> init/spfile and restart the database.
>> Keep little margin of sga_max_size of the total of
>> (buffer_cache,shared_pool,java_pool)
>>
>> leo_at_tech-trans.com wrote:
>>> Hi!
>>>
>>>   I tuned the parameter of memory (sga_max_size) in Oracle 9i.
>>> Unfortunately, it cause Oracle stop.  How can I do?  One more question,
>>> any guideline to define the maximum of sga_max_size? Thx!
>>>
>>> Ming

>
> Hi
> I install Oracle 9i (9.2.0.5.0) It is not in a business time,
> oracle error is out of shared memory. My server has 3G memory. I set
> sga_max_size to 2G by using alter system set sga_max_size=2048M
> scope=spfile. Unfortunately, It stops and cannot restart. How to edit
> the spfile binary file ?
>

Do the following:

1. sqlplus /nolog
2. connect / as sysdba
3. create pfile from spfile; The above may generate an error about Oracle not being available, but you will be able to obtain a text version (PFILE) of your SPFILE. The PFILE should be found in $ORACLE_HOME/dbs by default for most platforms. IIRC, for Windows, it will be found in %ORACLE_HOME%\database instead.
4. Modify the PFILE to hold the correct value for SGA_MAX_SIZE. You can use any text editor to modify the PFILE's contents as it is a simple ASCII file.
5. create spfile from pfile; Again, you may get an error about Oracle not being available, but you will also get a message stating the file was created.
6. alter database open;

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Jun 21 2006 - 22:18:00 CDT