When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. For any initialization parameters not specifically included in the initialization parameter file, the database supplies defaults. The initialization parameter file can be either a read-only text file, or a read/write binary file. The binary file is called a server parameter file.

The text initialization parameter file (PFILE) contains name/value pairs in one of the following forms:

For parameters that accept only a single value:

parameter_name=value

For parameters that accept one or more values (such as the CONTROL_FILES parameter):

parameter_name=(value[,value] …)

Even the format of the file is quite simple, there are a few things to note:

1. Parameter values of type string must be enclosed in single quotes or double quotes.
2. Case (upper or lower) in filenames is significant only if case is significant on the host operating system.
3. Although spfile is a binary file, it has an ASCII section which you can view parameters and their values.
4. Special characters in the file.
5. Multiple values for one parameter.
6. Scope of a parameter in RAC.
7. parameters with leading “SID.__” format when using AMM or ASMM.

For the item 1 – 3 listed above, it’s easy to understand. I’m giving some more information below regarding the rest of items.

Special Characters in Initialization Parameter Files

Character	Name	Description
#	Number sign	Comment
(	Left parenthesis	Start list of values
)	Right parenthesis	End list of values
"	Double quotation mark	Start or end of quoted string
'	Single quotation mark	Start or end of quoted string
=	Equal sign	Separator of keyword and values
,	Comma	Separator of elements
-	Minus sign	Precedes UNIX-style keywords
\	Backslash	Escape character

Some parameters, such as ROLLBACK_SEGMENTS, accept multiple values. Any of the following represent valid syntax.

— Enter multiple values enclosed in parentheses and separated by commas. For example:

ROLLBACK_SEGMENTS = (SEG1, SEG2, SEG3)

Enter multiple values without parentheses and commas if values don’t contain space. For example:

ROLLBACK_SEGMENTS = SEG1 SEG2 SEG3

Enter multiple values, one per line consecutively. For example:

ROLLBACK_SEGMENTS = SEG1
ROLLBACK_SEGMENTS = SEG2
ROLLBACK_SEGMENTS = SEG3

If you enter values for one parameter on multiple lines, then the entries must be on consecutive lines. The parameter is associated with a list of multiple values. If they are not defined consecutively, then the last consecutive list of values is used. For example, in the following entry the setting for the control file 3 will override the setting for the control file 1 and 2.

control_files='/u01/app/oracle/oradata/orcl/control01.ctl'
control_files='/u01/app/oracle/oradata/orcl/control02.ctl'
open_cursors = 10
control_files='/u01/app/oracle/oradata/orcl/control03.ctl’

In a RAC environment, the value before the period (.) in an SPFILE entry identifies the instance to which the particular parameter value belongs. When an asterisk (*) precedes the period, the value is applied to all instances that do not have a subsequent, individual value listed in the SPFILE. For example:

*.OPEN_CURSORS=500
prod1.OPEN_CURSORS=1000

For the instance with the Oracle system identifier (SID) prod1, the OPEN_CURSORS parameter is set to 1000 even though it has a database-wide setting of 500. Parameter file entries that have the asterisk (*) wildcard character only affect the instances without an instance-specific entry. This gives you control over parameter settings for instance prod1. These two types of settings can appear in any order in the parameter file.

When changing a parameter using “alter system”, you can specify “sid=’*’” to apply the new value to all instances or “sid=’prod1′” for the instance “prod1” only.

For a non-RAC environment, when you create a pfile from spfile, you will also see the format of “*.” or “SID.” as the leading characters. Then parameters with sid name in spfile takes precedence over parameters with ‘*’

When using AMM or ASMM, you will see one more special charater — double underscore “__”, not the single underscore which means the parameter is a hidden (non documented) one. Those parameters starting with double underscore are at the top of the lis. For example:

PROD.__data_transfer_cache_size=0
PROD.__db_cache_size=12700352512
PROD.__inmemory_ext_roarea=0
PROD.__inmemory_ext_rwarea=0
PROD.__java_pool_size=8388608
PROD.__large_pool_size=8388608
PROD.__pga_aggregate_target=26663190528
PROD.__sga_target=15212740608
PROD.__shared_io_pool_size=536870912
PROD.__shared_pool_size=1941962752
PROD.__streams_pool_size=0

Oracle writes the actual values of the SGA & PGA used in the spfile, with a special set of “__” (double underscore) ones, which records the values at that time in memory in the spfile with prefix.

The idea behind this is if the database were to crash at this time, spfile would have already recorded the values in effect at that time and it would read the value during subsequent restart and immediately set the parameters to those values. You can check those dynamic values in internal views such as V$SGA_DYNAMIC_COMPONENTS, V$SGA_DYNAMIC_FREE_MEMORY.

• Oracle