Oracle pfile and spfile

Oracle pfile is the parameter file which is used for initilazing the oracle database. This is usualy init.ora in the ${ORACLE_HOME}/dbs/ which is /u01/app/oracle/product/11.1.0/db_1/dbs in my case.

spfile is the binary counterpart of pfile which was introduced starting from Oracle 9i. spfile is used to store dynamic changes done via ALTER SYSTEM or ALTER SESSION statements.

On startup database first looks the spfile and if not found checks for pfile.

Creating spfile
spfile can be created as privilaged user by following command
SQL> create spfile from pfile;
The file is created in ${ORACLE_HOME}/dbs/ and named spfile{ORACLE_SID}.ora. The command will over write if a old file exist in the path and if its in use following error will throw.
ORA-32002: cannot create SPFILE already being used by the instance

Starting up with different spfile.
On startup the system checks the parameter file in following order.
spfile${ORACLE_SID}.ora file in ${ORACLE_HOME}/dbs/
spfile.ora in ${ORACLE_HOME}/dbs/
init${ORACLESID}.ora in ${ORACLE_HOME}/dbs/

To specify the parameter file on startup use the following command
SQL> startup pfile='/db2/ORACLE/SID/spfile/init.ora';

SCOPE and ALTER SYSTEM statements

Changes done by ALTER SYSTEM and ALTER SESSION statements are normally persistent in spfile. However this behaviour can be changed.

Specifying SCOPE=MEMORY and the changes are valid until the next restart and the spfile will not affected. To do this use the following commands.

SQL> SHOW PARAMETER timed_statistics
SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=memory;
SQL> SHOW PARAMETER timed_statistics

Specifying SCOPE=SPFILE and the changes will not affect the current running instance but will be visible after a database restart. For this use the following command.

SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=spfile;

Specifying SCOPE=BOTH will affect both the running instance and the spfile hence will be persistent. Following is the command to do this.

SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=both;

To check this parameter in both contexts memory and spfile use the following commands

SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';
SQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';

Check what paramter file (spfile or pfile) used in the database.

SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';

Export the spfile to pfile

SQL> create pfile from spfile;

OR can specify the file names as

SQL> create pfile='/db2/ORACLE/SID/spfile/init.ora' from spfile='/db2/ORACLE/SID/spfile/spfileorcl.ora'

===================