In this short article, we will discuss the server parameter
file and initialization parameter file i.e., spfile and pfile
pfile - parameter file, is a text-based
file, readable and editable with any text editor
spfile- server parameter file,
binary file, readable with only some utility and can not be edited.
pfile is
called the initialization parameter file because this is the first file used to
start (initialize) the database, spfile is created later on.
The default
extension of these files is ".ora", in an ASM file name can be with
any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is
database name)
The following
parameters can be seen in the parameter files
- memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
- database and instance name (can be different)
- parameters related to different physical files such as control file, datafiles, archive files
- processes and session details
- trace file location, audit settings, and many more
the very
first stage of the database startup is "nomount" and that’s where
this parameter file will get read, it will load database memory and process
settings and takes the location of control files.
The default
location of the parameter file is based on the operating system you are using
in Linux/Unix
it is located at $ORACLE_HOME/dbs location and for windows operation system it
is %ORACLE_HOME%\database
Parameter change
Assume your
database is running on pfile and you want to change the parameter of the
database, then you need to perform the below steps
- shutdown the database
- add/change the parameter in pfile
- start the database
Now assume
your database is running on spfile- then based on condition, you may not at all
need to restart the database
- change the parameter using alter system command
- Restart the database if required