Oracle Parameter (SP) File

1. Parameter file is a text/binary file used to store the Oracle database parameters and values.
2. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance.

Parameter Like:

• Memory allocation (SGA & PGA)
• Startup of Optional background Processes.
• Setting of NLS (National Language Support) Parameters etc.

There are 2 types of parameter files:

• pfile (parameter file) normal text file – Older way (not recommended)
• spfile (server parameter file) binary file – newer way (recommended) – introduced starting from 9i

Difference between pfile and spfile

pfile	spfile
Text file	Binary file
It can be edited	It can’t be edited. It can only the altered using the ALTER SYSTEM command.
Default location is $ORACLE_HOME/dbs/init[SID].ora	$ORACLE_HOME/dbs/spfile[SID].ora
The RMAN utility can’t take bakup	The RMAN utility can take backup

show parameter file;

create pfile='<pfile location>' from spfile;

(OR)

create pfile='<pfile location>' from spfile='<spfile location>';

create spfile from pfile='<pfile location>';

create spfile='<spfile location>' from pfile='<pfile location>';

show parameter;

alter system set <parameter_name>='<value>' scope=[spfile/memory/both];

startup pfile='<pfile location>'

startup spfile='<spfile location>'

show parameter spfile;