Oracle Concepts - initialization
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.
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.
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
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
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.