PFILE:
PFILE is a text file which can be modified by text editor. Oracle server required PFILE to start an oracle Instance.
SPFILE:
SPFILE is a binary file which can not be modified by text editor. It is created from PFILE.
How the Oracle Instance is initialized
When the Oracle instance start, first it looks to the $ORACLE_HOME/dbs (UNIX, Linux) or ORACLE_HOME/database (Windows) directory for the following files (in this order):
1. spfileSID.ora (SPFILE)
2. Default SPFILE (SPFILE)
3. initSID.ora (PFILE)
4. Default PFILE (PFILE)
SPFILE advantages
1. No need to restart the database in order to have a parameter changed and the new value stored in the initialization file
2. Reduce human errors: Parameters are checked before changes are accepted
3. An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
How could I switch from SPFILE to PFILE and vice-versa?
Switch from SPFILE to PFILE:
1) CREATE PFILE FROM SPFILE;
2) Backup and delete SPFILE
3) Restart the instance
Switch from PFILE to SPFILE:
1) CREATE SPFILE FROM PFILE=’Location of the PFILE’;
2) Restart the instance (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)
Converting SPFILE to PFILE and vice-versa
This could be done in order to have a backup in the other format or to change the initialization file for the database instance.
CREATE PFILE FROM SPFILE;
CREATE SPFILE FROM PFILE='/oradata/initORCL.ora';
CREATE SPFILE = '/oradata/spfileORCL.ora' FROM PFILE = '/oradata/initORCL.ora' ;