SPFILE or PFILE

Someone ask me the other day the differences between spfile and pfile

Here are my input:

PFILE or SPFILE are file that is used to store database intialization parameters.

The pfile or spfile is read during instance startup to get instance specific settings.

SPFILE was implemented or introduced from 9i only.In 10g pfile is still supported
You only can use one at a time but both files can exist in the default location.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use the spfile.

Changes to pfile can be made by editing the file and do a shutdown startup but for spfile, changes are made using the alter system command.

To create a server parameter file from a parameter file
login as sys
create spfile from pfile;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

By using spfile changes can be made in 3 ways as specified below
SCOPE = SPFILE
e.g. alter system set sql_trace=true scope =spfile;
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)

SCOPE = MEMORY
e.g alter system set undo_tablespace=undotbs1 scope=memory;
(For dynamic parameters, changes are applied in memory only. No static parameter changes is allowed.)

SCOPE = BOTH
e.g alter system set log_archive_dest_1 =/u01/arch2 scope=both;
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameters changes is allowed.)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

We can also use the server parameter file to create a pfile
This would be useful for:
1) Creating backups of the spfile.
2) For diagnostic purposes
3) Modifying the spfile by first exporting it, editing the output file, and then recreating it.

The following example creates a pfile from spfile
login as sys
CREATE PFILE FROM SPFILE;
(both files will bin located in $ORACLE_HOME/dbs for Unix and $ORACLE_HOME/database for windows)

Fell free to comment or ask questions....Thanks