SP File and Pfile in Oracle

Hey guys, in this blog I am going to explain to you about SP File and Pfile in Oracle.

Spfile: -

It refers to Server Parameter file (spfile$ORACLE_SID.ora). Server parameter files are binary files that exist only on the server and are called from client locations to start up the database. Since spfile is a binary file, we cannot edit the file directly. Rather, we need to use ALTER SYSTEM SET parameter to modify/update the parameters.

You must have the SYSDBA system privilege to create spfile. You can execute this statement before or after instance startup. However, if you have already started an instance using spfile_name, you cannot specify the same spfile_name in this statement. Text Initialization parameter file (PFILE)

Show parameter spfile: -

To create spfile: -

CREATE SPFILE='$PATH/spfile_name' FROM PFILE='$PATH/pfile_name';

Pfile: -

Pfile refers to Parameter file(init$ORACLE_SID.ora). Pfile is a text file created from spfile, used to get a list of the current parameter settings being used by the database. We can easily edit the pfile using editor like vi and applied to the database by creating spfile from the pfile.

You must have the SYSDBA or the SYSOPER system privilege to create pfile. We can execute this statement either before or after, instance startup.

To create pfile: -

CREATE PFILE='$PATH/pfile_name' FROM SPFILE='$PATH/spfile_name';

Both the spfile and pfile are located in the following path by default: -

$ORACLE_HOME/dbs

 

Changing Initialization Parameter Values: -

• Static Parameters: -
• Can be changed only in the parameter file
• Require restarting the instance before taking effect.

Dynamic Parameters: -

• Can be changed while database is online
• Can be altered at session level or system level
•  Are valid for duration of session or based on SCOPE setting
• Are changed by using ALTER SESSION or ALTER SYSTEM command

 

Scope of SP_File in Oracle: -

 

MEMORY: -

Parameters with a MEMORY scope is dynamic and can be modified without requiring the instance to be restarted. Changes to parameters with a MEMORY scope take effect immediately in the running instance but are not persisted across instance restarts. They revert to their default values when the instance is restarted. You can use the ALTER SYSTEM command to change parameters with a MEMORY scope.

Example: -

ALTER SYSTEM SET parameter_name = parameter_value SCOPE=MEMORY;

 

SPFILE: -

Parameters with an SPFILE scope can only be changed by modifying the SPFILE itself. These changes will take effect after the next instance restart. This is typically used for configuration changes that should persist across instance restarts.

 

Example: -

ALTER SYSTEM SET parameter_name = parameter_value SCOPE=SPFILE;

BOTH: -

Parameters with  BOTH scopes can be modified using either the MEMORY or SPFILE scope, depending on the situation. If you modify the parameter with the MEMORY scope, the change takes effect immediately, but it's not persisted in the SPFILE. If you modify it with the SPFILE scope, the change takes effect after the next instance restart, and it's also persisted in the SPFILE.

Example: -

ALTER SYSTEM SET parameter_name = parameter_value SCOPE=BOTH;

The scope of a parameter in the SPFILE determines how and when changes to that parameter are applied. It's important to use the appropriate scope based on your requirements and the desired behavior of the parameter. Additionally, not all parameters can be modified dynamically; some require a restart of the instance regardless of their scope. Always consult Oracle documentation for specific parameter details and recommended practices.