Different between spfile and pfile

[Adriana_andros]

As a new user for oracle database,I need to know two different files in oracle and the names are so much close and both are used in oracle database. can you tell me the use of these files in database and If I accidentally delete this file then the describe me the effects that can be on database.
Thanks.

[Praetor]

Difference between spfile and pfile :

The spfile and pfile both are very important file of database.The character of the database instance is depends on the parameters which is written on parameter file .For this parameters,Two types of files are being used which is available from oracle 9i and All prior releases of Oracle are using PFILEs.

If we talk about the difference between both The pfile and spfile,The pfile is a static parameter which is text file and can be updated and on another hand the Spfile is Server parameter file which is a binary file and can't be update using any editor.

[Katty]

Starting database with spfile:

When you start your database using STARTUP command,the database starts by default using spfile(Serever parameter file) that's why it is called "SPFILE".
After issuing the command,the oracle database searches suitable parameter file in these locations :

$ORACLE_HOME/dbs for UNIX/LINUX and ORACLE_HOME/database for windows operating system.

Starting database using PFILE:

You can override the default behavior of searching spfile.You can use PFILE to start your database using below command :

SQL> STARTUP PFILE='/oradata/initORCL.ora'

[kelfro]

Changing the parameter of spfile :

As we know the SPFILE of the database can't not be updated by user and it is only updated by database itself.On other hand,the PFILE can be updated by DBA user using Notepad and other editors.You can also change the parameters of SPFILE using ALTER COMMAND.An example here which will change the parameter of SPFILE.

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE
	COMMENT='Altered by me on 6 Jan 2010'
	SCOPE=BOTH
 	SID='*';

Using Scope parameter we can specify particular changes in SPFILE.

[Zecho]

Converting between PFILES and SPFILES:

Its a procedure to make the changes in PFILE and reflect same changes into SPFILES. This mechanism is called conversion in manner of task.
In this case,We will update some parameter of PFILE first using Notepad or any different editor and next ,a command will make the same change in SPFILE also.The command is as follows :

SQL> CREATE SPFILE from PFILE ;

This command will take the changes and prints it into SPFILE.But if your database is started using SPFILE then you need to shut down the system and then fire the above command and need to start it again.