Oracle Initialization File and Parameters

The database initialization file is a text file containing a number of parameters that control how the Oracle database works. At a very high-level, you could categorize those parameters as:

1. A pointer to the database control files
2. Information about the database itself, such as the physical block size used when reading and writing datafiles
3. Parameters that control the Oracle background processes
4. Parameters that control how Oracle allocates memory
5. Pointers to the directories containing the alert log and trace files for the instance

Here are some lines from a typical initialization file:

db_name = devl
db_files = 1024
control_files = 
 (/m01oracle/oradata/SCTPROD/control01.ctl,
  /m21/oradata/SCTPROD/control02.ctl,
  /m57/oradata/SCTPROD/control03.ctl)
db_block_size = 8192

• Parameter Details
  There are many parameters that you can set in the initialization file. Oracle documents all of them in the Oracle Server Reference Manual. Look through the following SlideShow to learn about the most important parameters in a bit more detail:

Oracle reads the initialization file whenever you start an instance.
Any changes that you make to the initialization file afterwards will not take effect until you shut down and restart your system.
Strictly speaking, Oracle does not consider the initialization file to be part of a database.

It is an important file though, and you want to preserve it with the same care that you do all the other files that are database files.

The initialization file is also often called a parameter file. It is a text file containing a number of parameter settings that affect how the Oracle processes operate when you start an instance and open a database. In that sense, the initialization file is more related to an Oracle instance than to a database. However, one of the most important parameters in the initialization file points to the database control files. For the most part, opening your database is not dependent on having one specific set of parameters. It is not unheard of for a site to have two initialization files, both used with the same database but for different purposes. One may be optimized for use during the day when most of the workload comes from small, interactive transactions. The other may be optimized for use at night, when the work comes from large, batch processes.

• initialization file Since the initialization file is read when an Oracle instance is started, changes to these parameters do not take effect until an instance is stopped and restarted. Remember, though, that turning on automatic archiving does not put the database in ARCHIVELOG mode. Similarly, placing the database in ARCHIVELOG mode does not enable the automatic archiving process. You should also make sure that the archive log destination has enough room for the logs Oracle will automatically write to it. If the archive logfile destination is full, Oracle will hang since it cannot archive additional redo logfiles.
  The archived redo logs are critical for database recovery. Just as you can duplex the online redo logs, you can also specify multiple archive log destinations. Oracle will copy filled redo logs to specified destinations and you can also specify whether all copies must succeed or not.
  The initialization parameters for this functionality are as follows:
  

  LOG_ARCHIVE_DUPLEX_DEST
  

  
  Specifies an additional location for redundant redo logs.
  

  LOG_ARCHIVE_MIN_SUCCEED_DEST
  

  
  Indicates whether the redo log must be successfully written to one or all of the locations. Valid values are 1 through 10 if multiplexing and 1 or 2 if duplexing. See your Oracle documentation for the additional parameters and views that enable and control this functionality.

Initialization parameters are configuration parameters that affect the basic operation of an instance. The instance reads initialization parameters from a file at startup. Oracle Database provides many initialization parameters to optimize its operation in diverse environments. Only a few of these parameters must be explicitly set because the default values are usually adequate.

• Functional Groups of Initialization Parameters
  Most initialization parameters belong to one of the following functional groups:
  1. Parameters that name entities such as files or directories
  2. Parameters that set limits for a process, database resource, or the database itself
  3. Parameters that affect capacity, such as the size of the SGA (these parameters are called variable parameters)
  
  Variable parameters are of particular interest to database administrators because they can use these parameters to improve database performance.

Initialization parameters are divided into two groups:

1. basic and
2. advanced.

Typically, you must set and tune only the approximately 30 basic parameters to obtain reasonable performance. The basic parameters set characteristics such as the database name, locations of the control files, database block size, and undo tablespace. In rare situations, modification to the advanced parameters may be required for optimal performance. The advanced parameters enable expert DBAs to adapt the behavior of the Oracle Database to meet unique requirements. Oracle Database provides values in the starter initialization parameter file provided with your database software, or as created for you by the Database Configuration Assistant.
You can edit these Oracle-supplied initialization parameters and add others, depending on your configuration and how you plan to tune the database. For relevant initialization parameters not included in the parameter file, Oracle Database supplies defaults.

First, click the Exercise link below to find and look at your database's initialization file.
Initialization File - Exercise