7
Oracle9i Database Specifications for Windows

Oracle9i for Windows uses initialization parameters to enable various features of the database every time an instance is started.

This chapter contains these topics:

• Initialization Parameter File Overview
  
  
• Sample File
  
  
• SGA_MAX_SIZE Parameter
  
  
• Initialization Parameters Without Windows NT-Specific Values
  
  
• Displaying Initialization Parameter Values
  
  
• Uneditable Database Initialization Parameters
  
  
• Calculating Database Limits

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 (SID) 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.

Location of Initialization Parameter File

By default, Oracle9i uses initialization parameter files located in

ORACLE_HOME\admin\db_name\pfile\init.ora

unless you specify a different initialization file with option PFILE at database startup.

Editing Initialization Parameter File

To customize Oracle9i database functions, you may be required to edit the initialization parameter file. Use only an ASCII text editor to modify the file.

Sample File

A sample file called initsmpl.ora is located in directory

ORACLE_BASE\ORACLE_HOME\admin\sample\pfile. 

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

1. Rename the file to 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.

If you installed a starter database, the initialization parameter file used by the starter database is located in

ORACLE_BASE\ORACLE_HOME\admin\sample\pfile

You can use either initsmpl.ora or the starter database init.ora as a basis for creating a new Oracle9i database initialization parameter file.

The annotated, sample initialization parameter file contains 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.

For example, several initialization parameters are specified with three different values to create a small, medium, or large System Global Area (SGA), respectively. The parameter that creates a small SGA is active in the following 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 follows:

# 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 release 1 (9.0.1), Oracle9i 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 an instance's SGA is displayed on instance startup when using Oracle Enterprise Manager (or SQL*Plus). You can also display the current instance's SGA size by using the SQL*Plus SHOW statement with the SGA clause.

Initialization Parameters Without Windows NT-Specific Values

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

Table 7-1  Initialization Parameters Without Windows NT-Specific Values

Parameter	Description
AUDIT_FILE_DEST	Not supported on Windows NT and should not be added to the initialization parameter file
DB_WRITER_PROCESSES	Supported, but typically unnecessary due to Windows NT asynchronous I/O capabilities
COMPATIBLE_NO_RECOVERY	Uses default value set in Oracle9i kernel (no Windows NT-specific value)
BACKGROUND_CORE_DUMP	Specifies whether Oracle includes SGA in core file for Oracle background processes
SHADOW_CORE_DUMP	Specifies whether Oracle includes SGA in core file for foreground (client) processes
CORE_DUMP_DEST	Specifies directory where Oracle dumps core files
CPU_COUNT	Oracle9i automatically sets value to number of processors available for your Oracle instance
HI_SHARED_MEMORY_ADDRESS	Not applicable to Windows NT
SHARED_MEMORY_ADDRESS	Not applicable to Windows NT
LARGE_POOL_SIZE	Uses maximum value limited by available memory
LOG_BUFFER	Starter database uses value set in Oracle9i kernel (no Windows NT-specific value). The Custom database creation option of Database Configuration Assistant enables you to customize the value for this parameter.
ORACLE_TRACE_COLLECTION_PATH	Uses default value set in Oracle9i kernel (no Windows NT-specific value)
ORACLE_TRACE_FACILITY_NAME	Uses default value set in Oracle9i kernel (no Windows NT-specific value)
ORACLE_TRACE_FACILITY_PATH	Uses default value set in Oracle9i kernel (no Windows NT-specific value)
SPIN_COUNT	Uses default value set in Oracle9i kernel (no Windows NT-specific value)

Displaying Initialization Parameter Values

Windows NT-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 7-2 when creating a new database. They cannot be modified after you have created the database. See Chapter 1, "Postinstallation Database Creation" for details on creating a new database, including the part of the procedure when you modify these parameters.

Table 7-2 Uneditable Database Initialization Parameters

Parameter	Description
CHARACTER SETFoot 1	Specifies database Globalization Support character set to use. This parameter can be set only when you create the database.
DB_BLOCK_SIZE	Specifies size in bytes of standard Oracle database blocks.
DB_NAME	Specifies name of the database to be created. Database name is a string of eight characters or less. You cannot change the name of a database.

1 Not an initialization parameter, but rather a clause in the CREATE DATABASE statement. See Chapter 1, "Postinstallation Database Creation" for an example of using this clause.

Calculating Database Limits

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

Table 7-3  Block Size Guidelines

Type	Size
Maximum block size	16,384 bytes or 16 kilobytes (KB)
Minimum block size	2 kilobytes (KB)
Maximum blocks for each file	4,194,304 blocks
Maximum possible file size with 16 K sized blocks	64 Gigabytes (GB) (4,194,304 * 16,384) = 64 gigabytes (GB)

Table 7-4  Maximum Number of Files for Each Database

Block Size	Number of Files
2 KB	20,000
4 KB	40,000
8 KB	65,536
16 KB	65,536

Table 7-5  Maximum File Sizes

Type	Size
Maximum file size for a FAT file	4 GB
Maximum file size in NTFS	16 Exabytes (EB)
Maximum database size	65,536 * 64 GB equals approximately 4 Petabytes (PB)
Maximum control file size	20,000 blocks

Table 7-6  Maximum Number of Extents for Each Database

Typical Block Sizes	Number of Extents
2 KB	121
4 KB	255
8 KB	504
16 KB	1032
32 KB	2070

Table 7-7  Shadow Process Memory

Release Number	Memory
Oracle9i release 2 (9.2)	444 KB
Oracle9i release 1 (9.0.1)	444 KB
Oracle8i release 8.1.7	335 KB
Oracle8i release 8.1.6	335 KB
Oracle8 release 8.1.3	265 KB
Oracle8 release 8.0.5	254 KB
Oracle8 release 8.0.4	254 KB