Oracle Concepts - initialization parameters init.ora Oracle Tips by Burleson Consulting

The Oracle initialization parameter file

When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

Here is a sample init.ora file:

db_cache_size = 176000M

db_2k_cache_size = 2048M

db_16k_cache_size = 99000M

db_keep_cache_size = 600000M

db_recycle_cache_size = 64000M

shared_pool_size = 14000M

In this section we will look at the database parameter file in more detail. First we will look at the two different types of parameter files you can use, PFILE’s and SPFILE’s, followed by a look at the parameters that are maintained in the database parameter file. Finally, we will look at how to manage the initialization parameter file.

Oracle init.ora Parameters

The initialization parameters are a very important part of the Oracle database. Oracle reads the initialization parameter values from either a PFILE or SPFILE as the database is starting. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and the location of existing datafiles.

A parameter has a name and a value. In this example, we have a parameter named db_block_size. This parameter tells oracle how big each individual block in the database is (we discussed blocks in an earlier chapter in this book). In this case, each block is 8192 bytes, or 8k, in size.

db_cache_size=8192

The next parameter you see is the background_dump_dest parameter. This parameter defines the location of Oracle trace files (log files) that are created by the Oracle background processes (we talked about the Oracle processes earlier in this book) and the important alert log where database messages reside. In this case, all files written by Oracle background processes will be in the /u01/oracle/admin/mydb/bdump directory.

background_dump_dest=/u01/oracle/admin/mydb/bdump

You can find all the Oracle Database parameters at Oracle’s web site, tahiti.oracle.com. Look in the reference guide and you will find them all listed, except for undocumented hidden parameters.  These parameters, while changeable, should only be modified with the aid of an experienced DBA or Oracle Technical Support (called MOSC).

Some parameters are dynamic and they can be changed while the database is running. For example, you can decrease the database buffer cache in many cases while the database instance is running with the alter system command:

alter system set db_recovery_file_dest_size=10g;

In this example, we dynamically changed the parameter db_recovery_file_dest to a value of 10 gigabytes (10g). The database will maintain this parameter until it is rebooted, unless a SPFILE is used. We will talk about SPFILE’s shortly.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It’s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.