Implementing a Database - Pro Oracle Database 12c Administration - page 31

Ensure that the initialization file is named correctly and located in the appropriate directory. This is critical
because when starting your instance, Oracle first looks in the ORACLE_HOME/dbs directory for parameter files with
specific formats, in this order:

spfile<SID>.ora •

spfile.ora •

init<SID>.ora •

In other words, Oracle first looks for a file named spfile<SID>.ora . If found, the instance is started; if not, Oracle
looks for spfile.ora and then init<SID>.ora . If one of these files is not found, Oracle throws an error.
This may cause some confusion if you're not aware of the files that Oracle looks for, and in what order. For
example, you may make a change to an init<SID>.ora file and expect the parameter to be instantiated after stopping
and starting your instance. If there is an spfile<SID>.ora in place, the init<SID>.ora is completely ignored.

■ You can manually instruct Oracle to look for a text parameter file in a directory, using the
pfile=<directory/filename> clause with the startup command; under normal circumstances, you shouldn't need
to do this. You want the default behavior, which is for Oracle to find a parameter file in the ORACLE_HOME/dbs directory
(for linux/Unix). the default directory on Windows is ORACLE_HOME/database .

Note

Table 2-1 lists best practices to consider when configuring an Oracle initialization file.

Table 2-1. Initialization File Best Practices

Best Practice Reasoning

Oracle recommends that you use a binary server
parameter file ( spfile ). However, I still use the old
text init.ora files in some cases.

Use whichever type of initialization parameter file
you're comfortable with. If you have a requirement
to use an spfile , then by all means, implement one.

In general, don't set initialization parameters if
you're not sure of their intended purpose. When
in doubt, use the default.

Setting initialization parameters can have
far-reaching consequences in terms of
database performance. Only modify parameters
if you know what the resulting behavior will be.

For 11g and higher, set the memory_target and
memory_max_target initialization parameters.

Doing this allows Oracle to manage all memory
components for you.

For 10g, set the sga_target and sga_target_max
initialization parameters.

Doing this lets Oracle manage most memory
components for you.

For 10g, set pga_aggregate_target and
workarea_size_policy .

Doing this allows Oracle to manage the memory
used for the sort space.

Starting with 10g, use the automatic UNDO feature.
This is set using the undo_management and
undo_tablespace parameters.

Doing this allows Oracle to manage most features of
the UNDO tablespace.

Set open_cursors to a higher value than the default.
I typically set it to 500. Active online transaction processing
(OLTP) databases may need a much higher value.

The default value of 50 is almost never enough.
Even a small, one-user application can exceed
the default value of 50 open cursors.

( continued )