DalTXColtsFan
asked on
Oracle 9i - pfile and spfile - what's the difference, why use one instead of the other
subject line pretty much says it all - it seems that in Oracle 9i you can use either a pfile or an spfile to hold the parameters to start up a database instance. What are the differences between the two file types, and what would be some reasons to use one or the other?
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The pfile is the traditional parameter file that Oracle has used (at least since Oracle5 days) to tune Oracle for a particualr database and hardware. The biggest disadvantage of a pfile that is that most of the values are then not modifiable on-the-fly. You need to edit the file, then shut down and restart the database for the changed values to take effect.
The big advantage of an spfile is that it supports changes to many (not all) of the values on-the-fly, so a database can be returned without having to shut it down.
With Oracle9i, the default is an spfile. I use and strongly recommend using an spfile.
The big advantage of an spfile is that it supports changes to many (not all) of the values on-the-fly, so a database can be returned without having to shut it down.
With Oracle9i, the default is an spfile. I use and strongly recommend using an spfile.
I see that there was an error in my earlier posting. I intended that second paragraph to say that with a spfile, an Oracle9i database can be "retuned" (not "returned") without shutting it down. Elena-S did direct you to a more complete answer.
You can crerate PFILE from SPFILE using the statement:
SQL>create PFILE=c:\...\init1.ora from SPFILE
After editing and investigating some parameters you can create a SPFILE from PFILE
SQL>create SPFILE= d:\...\rdbms\spfile.ora from pfile=c:\...\init1.ora
You can not directly use text editor to edit SPFILE.
You can start oracle using PFILE:
SQL>startup pfile=c:\...\init1.ora