RE: changing init.ora and spfile

From: <genegurevich_at_discover.com>
Date: Fri, 31 Jul 2009 12:43:03 -0500
Message-ID: <OF8F226682.7CC09CEE-ON86257604.0061344C-86257604.00615722_at_discover.com>



Joel:

I don't think this is true. In my database currently

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
lock_sga                             boolean     TRUE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 7348420608
sga_target                           big integer 7348420608

and I do know for a fact (watching OEM's memory advice) that oracle does adjust the sizes for db_cache and shared_pool over time.

thank you

Gene Gurevich

                                                                           
             <Joel.Patterson_at_c                                             
             rowley.com>                                                   
                                                                        To 
             07/31/2009 12:30          <robertgfreeman_at_yahoo.com>,         
             PM                        <genegurevich_at_discover.com>,        
                                       <oracle-l_at_freelists.org>            
                                                                        cc 
                                                                           
                                                                   Subject 
                                       RE: changing init.ora and spfile    
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           





Is it not true now that automatic memory management will not occur is sga_max = target? Id resizing buffer cache, shared etc.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Freeman Sent: Friday, July 31, 2009 1:19 PM
To: genegurevich_at_discover.com; oracle-l_at_freelists.org Subject: Re: changing init.ora and spfile

Kind of the hard way to do it. This would be easier I think:

Assuming database is up and running:

  1. You don't need to make an entry for SGA_MAX_SIZE if you are setting SGA_TARGET. SGA_MAX_SIZE will default to SGA_TARGET.

This changes the value SGA_MAX_SIZE in the SPFILE. This change is persistent, and will take place after the next reboot.

2. Change SGA_TARGET using alter system command: alter system set sga_target=500m scope=spfile;
This changes the value SGA_TARGET in the SPFILE. This change is persistent, and the scope=spfile indicates that the change will take place after the next reboot.

3. Restart the database (startup force)

Thats it.

If something bad happens and the database won't nomount (for example, you set sga_target to high) you can easily convert an spfile to a pfile and back when a database is down using the create spfile from pfile and create pfile from spfile commands. This should not happen often though.

RF

 Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Features (Oracle Press)
Other various titles out of print now... Blog: http://robertgfreeman.blogspot.com The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf

  • Original Message ---- From: "genegurevich_at_discover.com" <genegurevich_at_discover.com> To: oracle-l_at_freelists.org Sent: Friday, July 31, 2009 11:09:16 AM Subject: changing init.ora and spfile

Hello:

I want to make sure that I am making this change correctly. I have a 10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and SGA_TARGET parameters. My plan is to

  • modify the init.ora file
  • make a copy of an existing spfile
  • shut down the database
  • restart as startup using pfile=initXXX.ora
  • when database is started execute create spfile from pfile

That should guarantee that these changes are in the spfile and will not be
lost next time the database is bounced. Am I correct?

thank you

Gene Gurevich

Please consider the environment before printing this email.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l






Please consider the environment before printing this email.


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 31 2009 - 12:43:03 CDT

Original text of this message