Initialization Parameter File

Database: 10.1.0.5.0
OS: Windows


Parameter file is use for instance startup time.
Three types of parameters file

1. spfile (Server Parameter File)
Name: spfile.ora
Located: $ORACLE_HOME/dbs/spfile.ora (*nix platform)
Located: $ORACLE_HOME/database/spfile.ora(win platform)


Note: Never ever edit directly SPFILE file otherwise it will corrupted.


2. pfile (ASCII text file)
Name: init.ora
Located: $ORACLE_HOME/dbs/spfile.ora (*nix platform)
Located: $ORACLE_HOME/database/spfile.ora(win platform)


Note: you can edit this file in default TEXT EDITOR


3. pfile (text file)
Name: init.ora
Located: $ORACLE_BASE\admin\pfile\init.ora


Note: you can directly edit this file in any editor.

Parameter File Precedence

During instance time Oracle Server check SPFILE if found then using this file instance started. If SPFILE is not found then check PFILE if found then using this file instance stared. if both files is not found then you will get below error
message.

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE
\INITORCL.ORA'

How to know which file is database currently using ?

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.1.0\DB_1\
DATABASE\SPFILEORCL.ORA

Note: if parameter showing value mean spfile is currently used.


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string


Note: if parameter showing NULL in value column then PFILE(init.ora) is used.

How can edit parameter in spfile

1. alter system set [parameter_name] SCOPE=SPFILE
2. shutdown
3. startup

or

1. shutdown
2. edit parameter in pfile [init.ora]
3. create spfile from pfile
4. startup