Oracle Initialization File SPFILE and PFILE

When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. For any initialization parameters not specifically included in the initialization parameter file, the database supplies defaults.

The default location where these files are store is $ORACLE_HOME/dbs

There are two types of parameter files:
- Server Parameter file (SPFILE)
- Text initialization parameter file (PFILE)

Server Parameter file (SPFILE)
A server parameter file can be thought of as a repository for initialization parameters that is maintained on the machine running the Oracle Database server. It is, by design, a server-side initialization parameter file. 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 arrangement eliminates the need to manually update initialization parameters to make persistent any changes effected by ALTER SYSTEM statements. It also provides a basis for self-tuning by the Oracle Database server.

A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. (It can also be created directly by the Database Configuration Assistant.) The server parameter file is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file.

When you issue a STARTUP command with no PFILE clause, the Oracle instance searches an operating system–specific default location for a SPFILE from which to read initialization parameter settings. If no SPFILE is found, the instance searches for a PFILE. If a SPFILE exists but you want to override it with settings in a PFILE, you must specify the PFILE clause when issuing the STARTUP command.

Text initialization parameter file (PFILE)

The text initialization parameter file can also be thought of as a backup. There are a few scenarios where the SPFILE can be corrupted and the parameter cannot be changed using the ALTER SYSTEM command. In that case, you use the CREATE PFILE ... from SPFILE... command to create the text pfile that can then be edited. Then CREATE SPFILE .... from PFILE command to recreate the spfile.

If is recommended that you create an SPFILE as a dynamic way to maintain initialization parameter. By using an SPFILE, you can store an manage your initialization parameters persistently in a server-side disk file.

1. I creating backup of my spfile to pfile to /tmp/backup_parameter_file.ora

The command I issue is

CREATE PFILE = '/tmp/backup_parameter_file.ora' FROM SPFILE;

to view the result of the backup copy is more /tmp/backup_parameter_file.ora (note: since I am still in SQL prompt I am using ! to invoke OS command)

2. There are various way to backup the database parameter.

Since Oracle allow user to change the parameter with in 3 type of scopes:
1. MEMORY, following changes will not take effect if you reboot the system, because afte rebooting the system Oracle will read the parameter file.
2. SPFILE only, the database instance is not experiencing the changes until it is restart.
3. BOTH, following changes is update SPFILE and taking the changes immediately.

In my example, I want to change the parameter open_cursors from original value of 300 to 400 and my scope is MEMORY.

ALTER SYSTEM SET open_cursors = 400 SCOPE=MEMORY;

To backup the new changes to PFILE I have to specify from MEMORY

CREATE PFILE = '/tmp/parameter_from_memory.ora' FROM MEMORY;


To backup the original setting to PFILE I have to specify from SPFILE

CREATE PFILE = '/tmp/parameter_from_spfile.ora' FROM SPFILE;