To SPFILE or Not To SPFILE

0 pts.
Tags:
Oracle
Hi, What are your experiences using the SPFILE? Why would I want to use the SPFILE versus the old PFILE? Thanks! - D.
ASKED: May 18, 2004  10:49 AM
UPDATED: May 19, 2004  3:11 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mljsher
    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
    0 pointsBadges:
    report
  • Peasland
    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
    0 pointsBadges:
    report
  • 17051966
    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.
    0 pointsBadges:
    report
  • PRASAD55
    We have used the SPFILE and had no problems at all. For other information on SPFILE check out oracle documentation.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following