To SPFILE or Not To SPFILE
Hi,
What are your experiences using the SPFILE? Why would I want to use the SPFILE versus the old PFILE?
Thanks!
- D.
Software/Hardware used:
Software/Hardware used:
ASKED:
May 18, 2004 10:49 AM
UPDATED:
May 19, 2004 3:11 AM
Answer Wiki:
SPFILE allows the retention of parameter changes made while the database is still up. Before SPFILE any changes made via alter system were not retained after a shutdown of database. Now you can specify changes to be made to memory and or spfile. Example :
alter system set sort_area_size = 10485760 scope=both ;
This will change parameter in running database and update spfile so that after restart parameter is saved. In old versions if you forgot to update pfile chages were lost.
To see all answers submitted to the Answer Wiki: View Answer History.
I haven’t personally used SPFILE, but I have heard, at least in the earlier 9i versions, it’s not that stable. I’ve also eard the workaround is to use ‘startup pfile=’. This will allegedly bypass the spfile. I haven’t tried it, myself.
Matt Sherwood
I use the SPFILE in my 9i instances. There are a number of reasons.
1) Without SPFILEs, you won’t be able to dynmically alter the sizes of your SGA components
2) RMAN can be configured to automatically back up an SPFILE. You can’t do this with PFILEs.
3) With SPFILEs, you can perform remote admin tasks (like STARTUP) without requiring a PFILE remotely.
I haven’t had any problems with SPFILEs since I’ve been using them.
HTH,
Brian
I have been using spfile for the last six months in a 9.2.0.4 database – both production and non-production servers. I had no problems at all and I recommend using it.
The value of a parameter modified at the session level remains valid ONLY during the session. The value of a parameter modified at the system level remains valid ONLY until the instance is shut down. To continue the changed value of the parameter after start up of the instance, the DBA has to edit the initSID.ora file to update the affected parameter and then start up the instance. A persistent initialization parameter file can be changed dynamically while the instance is running and the new values will continue to be enforced even after the instance is shut down and started up without manually editing the initSID.ora file.
We have used the SPFILE and had no problems at all. For other information on SPFILE check out oracle documentation.