What Is a Parameter File Actually?
A parameter file is a file
that contains a list of initialization parameters and a value for each
parameter. You specify initialization parameters in a parameter file that
reflect your particular installation. Oracle supports the following two types
of parameter files:
1.Server Parameter Files - It's a
Binary version(it is in the form of 0's and 1's, Persistent.
2.Initialization Parameter Files -
It's a Text version and Not persistent.
Server Parameter File (SPFILE):
A server parameter file is a binary file
that acts as a repository for initialization parameters. The server parameter
file can reside on the machine where the Oracle database server executes.
Initialization parameters stored in a server parameter file are persistent, in
that any changes made to the parameters while an instance is running can
persist across instance shutdown and startup.
Initialization Parameter File(PFILE):
An initialization parameter file is
a text file that contains a list of initialization parameters. The file should
be written in the client's default character set. Sample initialization
parameter files are provided on the Oracle distribution medium for each
operating system. A sample file is sufficient for initial use, but you will probably
want to modify the file to tune the database for best performance. Any changes
will take effect after you completely shut down and restart the instance.
How the Oracle Instance is initialized by
Using these files ?
When the Oracle instance start, first it
looks to the $ORACLE_HOME/dbs (UNIX, Linux) or ORACLE_HOME/database (Windows)
directory for the following files (in this order):
1.
spfile${ORACLE_SID}.ora -------->
(SPFILE = Server Parameter File)
2.
spfile.ora
--------->
(SPFILE)
3.
init${ORACLE_SID}.ora --------->
(PFILE)
4.
init.ora --------->
(PFILE)
The first found file is used for the
instance initialization. So, Oracle first look for a SPFILE and after that
after a PFILE. PFILE is a text file, which can be modified and SPFILE
is a binary file which cannot be modified using a text editor.
Uses
of SPFILE:
1. No
need to restart the database in order to have a parameter changed and the
new value stored in the initialization file.
2. Reduce
human errors: Parameters are checked before changes are accepted.
3. SPFILE
can be backed-up with RMAN (RMAN cannot backup PFILEs).
How can we know Our database using SPFILE
or PFILE ?
The following query gives the result:
SELECT DECODE
(value, NULL, 'PFILE', 'SPFILE') "Init File
Type"
FROM sys.v_$parameter WHERE name
= 'spfile';
How could I switch from SPFILE to PFILE
and vice-versa ?
Switch from SPFILE to PFILE:
1) SQL>CREATE PFILE FROM SPFILE;
2) Backup
and delete SPFILE
3) Restart
the instance
Switch from PFILE to SPFILE :
1) SQL>CREATE SPFILE FROM PFILE;
2) Restart
the instance (the PFILE will be in the same directory but will not be
used. SPFILE will be used as per first priority) .
How can we Change SPFILE parameter
values ?
SQL>ALTER SYSTEM SET timed_statistics =
TRUE COMMENT = 'Changed by Hari on January
1st 2012' SCOPE = BOTH SID =
'*'
The SCOPE parameter can be set
to SPFILE, MEMORY or BOTH:
- MEMORY: Set for the current instance
only. This is the default behavior if a PFILE was used at STARTUP.
- SPFILE: update the SPFILE, the
parameter will take effect with next database startup
- BOTH: affect the current instance and
persist to the SPFILE. This is the default behavior if an SPFILE was used at
STARTUP.
Note :
1.The COMMENT parameter (optional)
specifies a user remark.
2.The SID parameter (optional; only used
with RAC) indicates the instance for which the parameter applies. (Default is
*: all Instances).
