RE: changing init.ora and spfile
Date: Fri, 31 Jul 2009 12:43:03 -0500
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.
<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.
[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 Please consider the environment before printing this email. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 31 2009 - 12:43:03 CDT