RE: changing init.ora and spfile
Date: Fri, 31 Jul 2009 13:28:57 -0400
Is it not true now that automatic memory management will not occur is sga_max = target? Id resizing buffer cache, shared etc.
[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:
- 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
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)
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.
Robert G. Freeman
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! :-)
- 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
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
lost next time the database is bounced. Am I correct?
Please consider the environment before printing this email.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 31 2009 - 12:28:57 CDT