Actually content wise both are nearly same.
These files contain the different parameter values of Oracle.
However the pfile can be opened in a text editor and edited whereas the spfile is a binary file.Oracle reads the value of different parameters from the spfile. If any value has to vbe changed the same has to be changed in the pfile and the binary spfile is to be created from this pfile.
Pl award suitably.
the spfile is the "binary version" of the pfile. If you change parameters, do not edit the pfile, as it will get overwritten any time you start br-tools. See this message at the beginning of e.g. brbackup:
ls3809:lopadm 51> brbackup -u /
BR0051I BRBACKUP 7.00 (18)
BR0055I Start of database backup: bdwbvmoo.aft 2007-09-05 08.02.42
<b>BR0477I Oracle pfile /oracle/LOP/102_64/dbs/initLOP.ora created from spfile /oracle/LOP/102_64/dbs/spfileLOP.ora</b>
To change Oracle parameters, use brtools instead (brtools, 1=>Instance Management, 4=>Change database parameters).
on windows (Ora le 10g):
pfile is at D:\oracle\[SID]\102\database\init[SID].ora
spfile is at D:\oracle\QPR\102\database\SPFILE[SID].ORA
if you have both, Oracle will use SPFILE, brtools uses the pfile. if you edit the pfile, it will contain list of parameters and they will all start with a start and a dot
I personnaly hate the SPFILE and prefer the pfile. I found it easier to use.
The one advantage with respect to an SPFILE is that you can use brtools to change parameters to the Oracle DB on the fly.
I also agree that it is very annoying to have to use brtools or sqlplus to change these parameters instead of using vi or Notepad to just change the pfile flatfile.
It's worth noting that while the spfile almost looks like plaintext in an editor, it does have a hash of its content in the beginning which is an integrity check to ensure that no changes are made directly to it.
To see if you are using an spfile, I believe the syntax in SQLPLUS is 'SHOW PARAMETER SPFILE' and see if it returns you a result with a filename. If you currently have an SPFILE and want to just use a PFILE, you can go to SQLPLUS and do: CREATE PFILE FROM SPFILE; and then stop Oracle, and delete/rename the SPFILE, and then start Oracle back up. Conversely, if you want to use an SPFILE, you would do CREATE SPFILE FROM PFILE; and restart Oracle.
Personally, I find that if I am changing a significant number of Oracle parameters, it's easiest to switch to PFILE from SPFILE, make my changes in the PFILE, and then re-create the SPFILE.
Again, it's a bit silly, but I suppose for the of integrity of the parameters, it's a benefit.