Somebody from LinkedIN was asking about the difference between SPFILE and PFILE in Oracle 10g. The following is a bit of explanation about the two initialization files.

The default initialization parameter file used by Oracle 10g is the SPFILE. The SPFILE (also known as the "Server Binary Parameter File" or simply "Binary Parameter File") is the binary form of your parameter file. The SPFILE, being a binary file, cannot be edited. If you need to do any changes on your initialization parameters for any reasons, you can only do the changes on your PFILE, which is the text form of your parameter file. Please do remember that you must explicitly declare use of the PFILE. You can do that by the command:

[Suppose your database is called MarlonDB]

STARTUP PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’

If you use your SPFILE to initialize the database, your command is simply:

STARTUP

Take note that if your database has no existing SPFILE and you did not explicitly declare use of a PFILE, you will get an error. To create a SPFILE from your PFILE, use this command:

CREATE SPFILE=’C:\oracle\product\10.2.0\db_1\database\spfileMarlonDB01.ora’ FROM PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’;

Similarly, you can create your PFILE from the SPFILE with this command:

CREATE PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’ FROM SPFILE=’C:\oracle\product\10.2.0\db_1\database\spfileMarlonDB01.ora’;

In summary, PFILE is the text form of the initialization parameter file. You can edit the PFILE and you must explicitly declare use of the PFILE when you are starting up the database with it. The SPFILE, on the other hand, cannot be edited. You do not need to explicitly declare use of it on startup because this is the default initialization file used by Oracle 10g.

 

[author's note: same article is also published in my other blog, http://dbalink.wordpress.com. -Marlon Ribunal 

---

Posted 02-03-2008 2:49 AM by marl