17 Oracle9i Database Specifications for Windows

Initialization Parameter File Overview

An initialization parameter file is an ASCII text file containing parameters. By changing parameters and values in an initialization file, you can specify, for example:

• Amount of memory Oracle9i database uses

• Whether to archive filled online redo logs

• Which control files currently exist

Every database instance has a corresponding initialization parameter file and ORACLE_SID registry parameter that points to the system identifier for the instance.

The initialization parameter filename takes the form init.ora. A single instance might have several initialization parameter files, each having some differences that affect system performance.

ORACLE_HOME\admin\db_name\pfile\init.ora

Sample File

Oracle provides an annotated sample initialization parameter file with alternative values for initialization parameters. These values and annotations are preceded by comment signs (#), which prevent them from being processed. To activate a particular parameter, remove the preceding # sign. To de-activate a particular parameter, edit the initialization parameter file to add a comment sign. The sample file is called initsmpl.ora. and is located in

ORACLE_BASE\ORACLE_HOME\admin\sample\pfile. 

If you installed a starter database, the initialization parameter file used by the starter database is located in the same directory. You can use either initsmpl.ora or the starter database init.ora as a basis for creating a new Oracle9i database initialization parameter file.

To use sample file initsmpl.ora as part of database creation:

1. Rename the sample file init.ora.

2. Edit this file to reflect the correct location of your database control files and the name of your database, as a minimum.

Here are two examples of activation and de-activation of alternative parameters. Several initialization parameters are specified with three different values to create a small, medium, or large System Global Area, respectively. The parameter that creates a small SGA is active in this first example:

  db_block_buffers = 200  # SMALL 
# db_block_buffers = 550  # MEDIUM 
# db_block_buffers = 3200 # LARGE 

To create a medium-sized SGA, comment out the small parameter definition and activate the medium parameter definition. Edit the initialization parameter file as in this second example:

# db_block_buffers = 200  # SMALL 
  db_block_buffers = 550  # MEDIUM 
# db_block_buffers = 3200 # LARGE 

SGA_MAX_SIZE Parameter

Parameter SGA_MAX_SIZE holds the maximum size that System Global Area (SGA) can reach for a particular instance. Beginning with Oracle9i release 1 (9.0.1), Oracle can change its SGA configuration while the instance is running. This allows sizes of buffer cache, shared pool, and large pool to be changed without instance shutdown.

Oracle9i can start instances underconfigured and allow the instance to use more memory by growing SGA up to a maximum of SGA_MAX_SIZE. If no SGA_MAX_SIZE value is specified, then Oracle selects a default value that is the sum of all components specified or defaulted at initialization time. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted to at initialization time, then the setting of SGA_MAX_SIZE in the initialization parameter file is ignored.

Memory allocated for the SGA of an instance is displayed on instance startup when using Oracle Enterprise Manager (or SQL*Plus). You can also display the current SGA size of an instance by using the SQL*Plus SHOW statement with the SGA clause.

Initialization Parameters Without Windows-Specific Values

Oracle9i Database Reference describes default values for many initialization parameters as being operating system-specific. However, not all parameters that it describes as having operating system-specific values affect Windows. In these cases, Windows uses either default value set in the Oracle9i kernel or does not use the parameter. Table 17-1 describes these initialization parameters:

Table 17-1 Initialization Parameters Without Windows-Specific Values

Displaying Initialization Parameter Values

Windows-specific parameter values can be viewed by using an ASCII editor to open the initialization parameter file:

ORACLE_BASE\ORACLE_HOME\admin\db_name\pfile\init.ora

To display any parameter value (whether set in the initialization parameter file or the Oracle9i kernel), enter the following command at the SQL*Plus command prompt:

SQL> SHOW PARAMETER parameter_name

where parameter_name is the name of a specific initialization parameter.

Uneditable Database Initialization Parameters

Check the following initialization parameters in Table 17-2 when creating a new database. They cannot be modified after you have created the database. See Chapter 5, " Postinstallation Database Creation on Windows" for details on creating a new database, including the part of the procedure when you modify these parameters.

Table 17-2 Uneditable Database Initialization Parameters

Calculating Database Limits

Use size guidelines in this section to calculate Oracle9i database limits.

Table 17-3 Block Size Guidelines

Table 17-4 Maximum Number of Files for Each Database

Table 17-5 Maximum File Sizes

Table 17-6 Shadow Process Memory