PFILE and SPFILE – introduction to Oracle initialization files

To start instance, Oracle need to read initialization parameter files. There are two types of those files: PFILE and SPFILE and can contains following informations:

– instance parameter list

– database name related to instance

– size of System Global Area (SGA)

– information what to do with filled redo logs

– name and localization of control files

– informations about UNDO segments

Those parameters can be declared or not, if not then Oracle will use their default values.

1. PFILE

This is static initialization parameter file usually called initSID.ora, where SID is your instance name. It is text file which can be edited manually. Can be read only during instance startup so any changes in it need to be  followed by database instance restart. Of course some of initialization parameters can be modified dynamically but those changes will not affect PFILE. Default path where we can find it is: $ORACLE_HOME/dbs(unix/linux) or $ORACLE_HOME/database (windows). During installation Oracle Universal Installer creates  init.ora file which can be use to create init<SID>.ora file. We can also create PFILE from SPFILE using command CREATE PFILE:

create pfile=’/u01/app/oracle/product/10.2.0/dbs/initSolA.ora’ from spfile=’/u01/app/oracle/product/10.2.0/dbs/spfileSolA.ora’;

All parameters in PFILE files are optional and can be placed in random order. Each undefined parameter will use it’s default value if not mentioned in PFILE (those default values can depend on used operating system).

2. SPFILE

This is dynamic initialization parameter file usually called spfileSID.ora Is binary and should not be modified manually.

You can create SPFILE from FILE using comand:

create spfile=’/u01/app/oracle/product/10.2.0/dbs/spfileSolA.ora’ from pfile=’/u01/app/oracle/product/10.2.0/dbs/initSolA.ora’;

Usually is stored in $ORACLE_HOME/dbs folder and it’s content can be modified from database using ALTER SYSTEM SET command :

ALTER SYSTEM SET parameter = value SCOPE = MEMORY|SPFILE|BOTH

Clause SCOPE is used to determine range of changes:

MEMORY – change is made to parameter only for currently running instance, does not modify SPFILE.

SFPILE – change is made to parmater only inside active SPFILE file, does not affect running instance.

BOTH – change is made  in both instance and SPFILE.

During startup Oracle is looking for initialization files in following order:

spfielSID.ora

spfile.ora

initSID.ora

init.ora

in folder $ORACLE_HOME/dbs in unix/linux and $ORACLE_HOME/database in Windows systems.

Mostly modified parameters in PFILE and SPFILE are:

IFILE – point to internal parameter file.  Oracle will also look in this file for parameters values.

LOG_BUFFER – size of REDO log buffer.

MAX_DUMP_FILE_SIZE – specifies the maximum size of trace files excluding alert log.

PROCESSES – maximum number of processes connected to instance.

SQL_TRACE – enables or disables trace for all user sessions.

TIMED_STATISTICS – enables or disables time measure in trace files and mon srceens.

Full list of parameters can be obtained  from V$PARAMETER perspective or using SHOW PARAMETER command.

SYS@SolA> select name,value from v$parameter where name =’control_files’;

NAME                                               VALUE

———————————– ———————————————

control_files                                 /u01/app/oracle/product/10.2.0/dbs/ora_control1,

/u01/app/oracle/product/10.2.0/dbs/ora_control2

SYS@SolA> show parameter undo_management

NAME                                                  TYPE        VALUE

———————————— ———– ——————————

undo_management                       string      AUTO

Leave a Response