Oracle Pfile & Spfile Overview

Pfile

Pfile is the text (initSID.ora) file

User can change parameters in manually

Changes will be effect on next startup

It’s open during the instance startup time

Pfile default location is “$ORACLE_HOME/dbs”

How to create pfile?

Pfile and spfile create its required a sysdba privileges

SQL>create pfile from spfile;

SQL> CREATE PFILE='/oracle/CLONE/db/11.2.0/dbs/inittest.ora' FROM 

SPFILE='/oracle/CLONE/db/11.2.0/dbs/spfileCLONE.ora';

SPFILE:

SPfile is the binary file

SPfile is oracle 9i new feature

It’s contains same information from the PFile

It’s maintained by the server process

RMAN support to using backup to the instillation parameter file

How to create spfile?

Spfile created from a pfile

EX:

SQL>create spfile from pfile;

SQL> CREATE SPFILE='/oracle/CLONE/db/11.2.0/dbs/spfileTEST.ora' FROM PFILE='/oracle/CLONE/db/11.2.0/dbs/inittest.ora';

Altering SPfile:

Some initialization parameters are dynamic so they can be modified using the ALTER SESSION or ALTER SYSTEM statement.

EX:

ALTER SESSION SET parameter_name = value

ALTER SYSTEM SET parameter_name = value scope=memory|spfile|both

SQL>ALTER SYSTEM SET undo_tablespace = UNDO2;

Meaning of scope= memory|spfile|both

Scope:

Changes should be made in memory spfile and both areas

Scope=Memory:

Change the parameter values in current running instance and dynamic parameters, changes are applied in memory only. No static parameter change is allowed

Scope=Spfile:

Change the parameter values in spfile only. For static and dynamic parameters, changes are recorded in the spfile. If using spfile parameter system must be restart.

Scope=Both:

Change parameter values in current running instance and spfile and dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.