Skip Headers
Oracle® Database Platform Guide
11g Release 2 (11.2) for Microsoft Windows

E10845-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

14 Oracle Database Specifications for Windows

Oracle Database 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

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

  • Amount of memory Oracle Database uses

  • Whether to archive filled online redo logs

  • Which control files currently exist

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

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

Note:

Your init.ora file for initialization parameters is set by Oracle Universal Installer during database installation. These parameter settings may vary depending on your hardware configuration. For descriptions of all initialization parameters and instructions for setting and displaying their values, see Oracle Database Reference.

Location of the Initialization Parameter File

If you do not specify a different initialization file with option PFILE at database startup, then by default Oracle Database uses initialization parameter files located in

ORACLE_HOME\Database\init.ora

Note:

If you create a database manually using a SQL script, then you are required to create an initialization parameter file or copy an existing initialization parameter file and modify the contents. If you use Database Configuration Assistant to create a database, then the initialization parameter file is automatically created for you.

Editing The Initialization Parameter File

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

Database Configuration Assistant Renames init.ora

When you create a database using Database Configuration Assistant, a Server Parameter File (SPFILE) is created from the initialization parameter file, and the initialization parameter file is renamed. Oracle does not recognize the renamed file as an initialization parameter file, and it is not used after the instance is started.

If you want to modify an instance created with Database Configuration Assistant after it starts, you must use ALTER SYSTEM statements. You cannot change the Server Parameter File itself, because it is a binary file that cannot be browsed or edited using a text editor. The location of the newly-created Server Parameter File is ORACLE_HOME\database. The Server Parameter File file name is spfileSID.ora.

See Also:

"Managing Initialization Parameters Using a Server Parameter File" in Oracle Database Administrator's Guide

Sample File

Oracle Database 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_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 Oracle 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 Database can change its SGA configuration while the instance is. This allows sizes of buffer cache, shared pool, and large pool to be changed without instance shutdown.

Oracle Database 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 Database 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 serves as an upper bound.

See Also:

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 SGA size of the current instance by using the SQL*Plus SHOW statement with the SGA clause.

Initialization Parameters Without Windows-Specific Values

Oracle 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 the default value set in the Oracle Database kernel or does not use the parameter. Table 14-1 describes these initialization parameters:

Table 14-1 Initialization Parameters Without Windows-Specific Values

Parameter Description

AUDIT_FILE_DEST

Supported on Windows to write XML format audit files

DB_WRITER_PROCESSES

Supported, but typically unnecessary due to Windows asynchronous I/O capabilities

COMPATIBLE_NO_RECOVERY

Uses default value set in Oracle Database kernel (no Windows-specific value)

BACKGROUND_CORE_DUMP

Specifies whether Oracle Database includes SGA in core file for Oracle Database background processes

SHADOW_CORE_DUMP

Specifies whether Oracle Database includes SGA in core file for foreground (client) processes

CORE_DUMP_DEST

Specifies directory where Oracle Database dumps core files

CPU_COUNT

Oracle Database automatically sets value to number of processors available for your Oracle Database instance

HI_SHARED_MEMORY_ADDRESS

Not applicable to Windows

SHARED_MEMORY_ADDRESS

Not applicable to Windows

LARGE_POOL_SIZE

Uses maximum value limited by available memory

LOG_BUFFER

Starter database uses value set in Oracle Database kernel (no Windows-specific value). The Custom database creation option of Database Configuration Assistant enables you to customize the value for this parameter.

SPIN_COUNT

Uses default value set in Oracle Database kernel (no Windows-specific value)


Displaying Initialization Parameter Values

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

ORACLE_HOME\admin\db_name\pfile\init.ora

To display any parameter value (whether set in the initialization parameter file or the Oracle Database 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 initialization parameters in Table 14-2 when creating a new database. They cannot be modified after you have created the database. See Chapter 3, "Postinstallation Database Creation on Windows" for details on creating a new database, including the part of the procedure where you modify these parameters.

Table 14-2 Uneditable Database Initialization Parameters

Parameter Description

CHARACTER SET

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.


Note:

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

Calculating Database Limits

Use size guidelines in this section to calculate Oracle Database limits.

Table 14-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 14-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 14-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