Initialization Parameter Files

Oracle provides two different types of parameter files that you can use, PFILE and SPFILE. When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. Static parameter file Pfile commonly referred to as intiSID.ora and Persistent parameter file SPfile commonly referred to as spfileSID.ora. A PFILE is a traditional text based init.ora parameter file. Typically this resides on the server in the $ORACLE_BASE/admin/SID/pfile directory.Parameter File contents...

• A list of instance parameters

• The name of the database the instance is associated with

• Allocations for memory structures of the System Global Area (SGA)

• What to do with filled online redo log files

• The names and locations of control files

• Information on undo segments

Pfile:- Until Oracle 8i, we were using a text file called the PFILE. The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE.

By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. DBA may keep copies of this file on their local PC to allow remote startup.

SQL> CONNECT sys/password AS SYSDBA

SQL> STARTUP PFILE=C:LocalInit.ora

SPfile:- In Oracle9i, a new feature called SPFILE. SPFILE is a binary file that contains the same information as the old PFILE. If a parameter is changed using the ALTER SYSTEM SET command Oracle will apply this parameter change to the current SPFILE. The file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup. The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change. The scope of the ALTER SYSTEM SET command can be defined using the following.

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

The actions of the scope parameters are listed below.

BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.

SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.

MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

Creating an SPFILE

An SPFILE is created from an initSID.ora file using the CREATE SPFILE command. This can be executed before or after the database is open.

SQL> CREATE SPFILE FROM PFILE;

One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters

SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Related Post:-