What is spfile in Oracle Database

What is an SPFILE?

A server parameter file (SPFILE) is a new feature introduced in Oracle9i. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters in an init.ora and have the instance restarted to get changes effective.

When is the SPFILE used?

When the database is created with the Database Configuration Assistant the service is created without pointing out a specific parameter file. An SPFILE is created and placed under $ORACLE_HOME/database.

In this case the file that is used during startup of the database via a service or the command startup with no pfile argument will be the binary file spfile located in the $ORACLE_HOME/database directory. If you don’t want the service to use the spfile you must recreate the service or edit the service to point out a specific pfile.

On the other hand if you have created a service with the oradim command pointing out the pfile and you want to use the spfile instead. Edit the registry in HKEY_LOCAL_MACHINE/SOFTWARE/oRACLE/HOMEX by removing the key ORA_SID_PFILE which is created when the service was created with the oradim command.

Does the changes of the parameter file (pfile) get reflected in the database after restart?

It is very important to know that any changes in the init.ora file will not be reflected in the database when restarted unless you start the database pointing out the pfile explicitly when issuing the startup command. If you later on startup the database with the service it will again pick the values from the spfile.

How do I change the parameter values in an SPFILE?

The following command changes the sort_area_size setting in the SPFILE only.

SQL> alter system set sort_area_size=65536 scope=spfile;

The following command changes the sort_area_size setting in memory and in the SPFILE.

SQL> alter system set sort_area_size=65536 scope=both;

Can I create a SPFILE if I don’t have any?

The following command creates an SPFILE from a PFILE:

SQL> create SPFILE='d:\oracle\ora92\database\spfilep902.ora' from Pfile='d:\oracle\ora92\database\initp902.ora';

If I have created a new service which is using the pfile how do I change this to use the SPFILE instead?

Remove the entry ORA_[SID]_PFILE parameter from your HOME in the registry.