Skip to Content
avatar image
Former Member

pfile VS spfile

Hi,

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

Regards

Lauran

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Sep 05, 2007 at 06:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

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

  • avatar image
    Former Member
    Sep 05, 2007 at 06:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 05, 2007 at 06:06 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 05, 2007 at 01:53 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

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