pfile VS spfile

[Former Member]

Hi,

Can anyone tell me what is the different between pfile and spfile ? What is their usage and advantage/disadvantage ?

Regards

Lauran

[former_member204746]

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.

[mike_howles4]

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.

[Former Member]

Hi All,

Thanks for the info. You mean in a SAP system, there will be pfile as well as spfile ? If so, where are they located each ?

Thanks

Lauran

[Former Member]

pfile and spfile are located in <b>$ORACLE_HOME/dbs</b>, example:

/oracle/SID/102_64/dbs

regards

Michael

[Former Member]

<i>> You mean in a SAP system, there will be pfile as well as spfile ?</i>

Yes - the spfile is used by Oracle during the startup; AFAIK only the br*-tools use the pfile.

<i>> If so, where are they located each ?</i>

See mho's answer.

Best regards,

Elmar.

[Former Member]

Hi Lauran,

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).

Best regards,

Elmar.

[Former Member]

Hello,

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.

Regards