Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 9i - pfile and spfile - what's the difference, why use one instead of the other

Posted on 2004-08-10
4
Medium Priority
?
1,104 Views
Last Modified: 2010-08-05
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!
0
Comment
Question by:DalTXColtsFan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
Elena-S earned 200 total points
ID: 11762952
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11763261
SPFILE is a binary file and many entries can be changed dynamically - without shutdown the instance.

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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11763296
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11810011
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question