Initialization Parameter files: SPFILE and PFILE

"To start an instance, the Oracle server must read the initialization parameter file."

To start an Oracle instance, the Oracle server reads the initialization parameter file.

Two types of initialization of parameter files exist:

• PFILE (Parameter File) :Static File
• SPFILE (Server Parameter File) :

Initialization 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 about undo segments

PFILE

• PFILE is a text file. (commonly reffered as initSID.ora)
• You can change the values of PFILE manually
• If the file is modified, the instance must be shutdown and restarted in order to make the new parameter values effective.
• Default location of PFILE is $ORACLE_HOME/dbs

Creating PFILE

• A sample init.ora file is created by the Universal Installer during installation. (Location of init.ora file is $ORACLE_HOME/dbs)
• Create a initSID.ora file from init.ora file by using OS copy command.

#cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initprod.ora (Here SID is System Identifier: Name of the Instance. example:PROD)

• Now, modify the parameters using text editor within the initSID.ora file (Here ex: initprod.ora)

_______________________________________________________

SPFILE

• SPFILE is a binary file.(commonly reffered as spfileSID.ora)
• It is maintained by the Oracle server
• If modified manually, the SPFILE is rendered useless
• The SPFILE provides the ability to make changes to the database persistent across shutdown and startup.
• By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora
• SPFILE can be backed up with RMAN (RMAN cannot backup PFILE)

Modifying the parameters in SPFILE

The ALTER SYSTEM SET command is used to change the values of instance parameters.

General syntax:

ALTER SYSTEM SET parameter_name=parameter value [SCOPE=SPFILEMEMORYBOTH] [COMMENT 'text'][SID='sid''*']

where

• parameter_name: Name of the parameter to be changed
• parameter_value: Value the parameter is being changed to
• COMMENT: A comment to be added into the SPFILE next to the parameter being altered
• SCOPE: Determines if change should be made in memory, SPFILE, or in both areas
• MEMORY: Changes the parameter value only in the currently running instance
• SPFILE: Changes the parameter value in the SPFILE only
• BOTH: Changes the parameter value in the currently running instance and the SPFILE
• SID: Identifies the ORACLE_SID for the SPFILE being used
• 'sid': Specific SID to be used in altering the SPFILE
• '*': Uses the default SPFILE

Example: Change the parameter value in SPFILE and currently running instance.

SQL> ALTER SYSTEM SET undo_tablespace='undotbs1' SCOPE=BOTH;

_______________________________________________________

Example: How to know my database is using SPFILE or PFILE

SQL>SELECT value FROM V$PARAMETER where name='spfile';

VALUE

----------

/u01/oracle/product/10.2/dbs/spfileprod.ora

If you find any value in VALUE column, your database is using SPFILE

If you find NULL value in VALUE column, your database is using PFILE.

_________________________________________________________

If you've any problem with PFILE or SPFILE, just leave your message here

I'll Help you.