Friday, January 29, 2010

Difference between spfile and pfile

1. spfile resides at server side. it is a binary file. pfile is static client-side file.
2. Pfile must be updated using text editor. Updating spfile manually will corrupt it. To update spfile, Alter System set Command is used.
3. Spfile can be backed up using RMAN.

database is using pfile or spfile

How can I know whether my database is started using spfile or pfile?

Execute the following query

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

If result displays pfile, it means that database has been started with pfile and vice-versa.