|
 |
|
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.
Initialization File
Although not normally discussed as a database
file a database will not startup without a valid initialization
parameter file. The file is only read at startup and contains the
information required to set up the SGA, control file locations, trace
and dump file locations and setup parameters for multi-threaded
server, parallel query and Oracle Parallel Server. I suggest keeping a
copy in a secure place incase yours ever becomes corrupted, deleted or
damaged. The Oracle Reference, which is included in the Oracle8 and
Oracle8i documentation set, provides descriptions of all
initialization parameters. There are also undocumented initialization
parameters that allow specialized behaviors to be configured. Both the
documented and undocumented initialization parameters are covered in
my book ?ORACLE8i Administration and Management? from Wiley and Sons.
It should be obvious that the most important
file regarding database setup and operation is probably the INIT<sid>.ORA,
or initialization file. This file contains the assignments for the
database initialization parameters.
For Oracle7, version 7.3, there are 154
initialization parameters, for Oracle8, version 8.0.5, there are 184.
In Oracle8i there are 194. Table 11 is a list of the Oracle8i INIT.ORA
parameters, their default values, and descriptions. Note that on your
platform there may be more or less than this number of parameters.
|
NAME |
VALUE |
DESCRIPTION |
|
O7_DICTIONARY_ACCESSIBILITY |
TRUE |
V7 Dictionary accessibility Support
(Migration only) |
|
ALWAYS_ANTI_JOIN |
NESTED_LOOPS |
Always use this anti-join when possible |
|
ALWAYS_SEMI_JOIN |
standard |
Always use this semi-join when possible
|
|
AQ_TM_PROCESSES |
0 |
Number of AQ Time Managers to start |
|
AUDIT_FILE_DEST |
(PD) |
Destination for audit files |
|
AUDIT_TRAIL |
NONE |
Enable system auditing |
|
BACKGROUND_CORE_DUMP |
PARTIAL |
Sets whether SGA is dumped with core file
dump, PARTIAL means don't dump SGA. |
|
BACKGROUND_DUMP_DEST |
(PD) |
Detached process dump directory |
|
BACKUP_TAPE_IO_SLAVES |
FALSE |
BACKUP Tape I/O slaves |
|
BITMAP_MERGE_AREA_SIZE |
1048576 |
Maximum memory allow for BITMAP MERGE |
|
BLANK_TRIMMING |
FALSE |
Blank trimming semantics parameter |
|
BUFFER_POOL_KEEP |
0 |
Number of database blocks/latches in KEEP
buffer pool |
|
BUFFER_POOL_RECYCLE |
0 |
Number of database blocks/latches in
recycle buffer pool |
|
COMMIT_POINT_STRENGTH |
1 |
Bias this node has toward not preparing in
a two-phase commit |
|
COMPATIBLE |
8.1.0 |
Database will be compatible with this
software version |
|
CONTROL_FILE_RECORD_KEEP_TIME |
7 |
Control file record keep time in days |
|
CONTROL_FILES |
(PD) |
Control file names list |
|
CORE_DUMP_DEST |
(PD) |
Destination for core dump files. |
|
CPU_COUNT |
(PD) |
Number of cpu's for this instance |
|
CREATE_BITMAP_AREA_SIZE |
8388608 |
Size of create bitmap buffer for bitmap
index |
|
CURSOR_SPACE_FOR_TIME |
FALSE |
Use more memory in order to get faster
execution |
|
DB_BLOCK_BUFFERS |
8000 |
Number of database blocks cached in
memory |
|
DB_BLOCK_CHECKING |
TRUE |
Data and index block checking overrides
events 10210 and 10211 |
|
DB_BLOCK_CHECKSUM |
FALSE |
Store checksum in db blocks and check
during reads |
|
DB_BLOCK_LRU_LATCHES |
1|CPU_COUNT/2 |
Number of lru latches |
|
DB_BLOCK_MAX_DIRTY_TARGET |
DB_BLOCK_BUFFERS |
Upper bound on modified buffers/recovery
reads |
|
DB_BLOCK_SIZE |
(PD) |
Size of database block in bytes |
|
DB_DOMAIN |
WORLD |
Directory part of global database name
stored with CREATE DATABASE |
|
DB_FILE_DIRECT_IO_COUNT |
64 |
Sequential I/O block count |
|
DB_FILE_MULTIBLOCK_READ_COUNT |
8 |
Db blocks read for each IO |
|
DB_FILE_NAME_CONVERT |
NULL |
Datafile name convert pattern and string
for standby/clone database |
|
DB_FILES |
MAXDATAFILES |
Max allowable # db files |
|
DB_NAME |
(PD) |
Database name specified in CREATE DATABASE |
|
DB_WRITER_PROCESSES |
1 |
Number of background database writer
processes to start |
|
DBLINK_ENCRYPT_LOGIN |
FALSE |
Enforce password for distributed login
always be encrypted |
|
DBWR_IO_SLAVES |
0 |
Number of DBWR I/O slaves |
|
DELAYED_LOGGING_BLOCK_CLEANOUTS |
TRUE |
Turns delayed block cleanout on or off |
|
DISK_ASYNCH_IO |
TRUE |
Use asynch I/O for random access devices |
|
DISTRIBUTED_TRANSACTIONS |
(PD) |
Max. number of concurrent distributed
transactions |
|
DML_LOCKS |
4*Trans. |
Dml locks - one for each table modified in
a transaction |
|
ENQUEUE_RESOURCES |
Derived |
Resources for enqueues |
|
EVENT |
NULL |
Debug event control |
|
FIXED_DATE |
NULL |
Fixed SYSDATE value |
|
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY |
FALSE |
Freeze database during instance recovery
(OPS) |
|
GC_DEFER_TIME |
10 |
How long to defer down converts for hot
buffers (DFS)(OPS) |
|
GC_FILES_TO_LOCKS |
NULL |
Mapping between file numbers and lock
buckets (DFS)(OPS) |
|
GC_RELEASABLE_LOCKS |
0 |
Number of releasable locks (DFS)(OPS) |
|
GC_ROLLBACK_LOCKS |
20 |
Locks for the rollback segments (DFS)(OPS) |
|
GLOBAL_NAMES |
TRUE |
Enforce that database links have same name
as remote database |
|
HASH_AREA_SIZE |
2*SORT |
Size of in-memory hash work area |
|
HASH_JOIN_ENABLED |
TRUE |
Enable/disable hash join |
|
HASH_MULTIBLOCK_IO_COUNT |
1 |
Number of blocks hash join will read/write
at once |
|
HI_SHARED_MEMORY_ADDRESS |
0 |
SGA starting address (high order 32-bits
on 64-bit platforms) |
|
HS_AUTOREGISTER |
TRUE |
Enable automatic server DD updates in HS
agent self-registration |
|
IFILE |
NULL |
Include file in init.ora |
|
INSTANCE_GROUPS |
NULL |
List of instance group names |
|
INSTANCE_NAME |
NULL |
Instance name supported by the instance |
|
|
|
|
|
INSTANCE_NUMBER |
0 |
Instance number |
|
JAVA_POOL_SIZE |
10000K |
Size in bytes of the Java pool |
|
JOB_QUEUE_INTERVAL |
60 |
Wakeup interval in seconds for job queue
processes |
|
JOB_QUEUE_KEEP_CONNECTIONS |
FALSE |
Keep network connections between execution
of jobs |
|
JOB_QUEUE_PROCESSES |
0 |
Number of job queue processes to start |
|
LARGE_POOL_SIZE |
0 |
Size in bytes of the large allocation pool
(auto set at 600k) |
|
LICENSE_MAX_SESSIONS |
0 |
Maximum number of non-system user sessions
allowed |
|
LICENSE_MAX_USERS |
0 |
Maximum number of named users that can be
created in the database |
|
LICENSE_SESSIONS_WARNING |
0 |
Warning level for number of non-system
user sessions |
|
LM_LOCKS |
12000 |
Number of locks configured for the lock
manager (OPS) |
|
LM_PROCS |
64 |
Number of client processes configured for
the lock manager (OPS) |
|
LM_RESS |
6000 |
Number of resources configured for the
lock manager (OPS) |
|
LOCAL_LISTENER |
NULL |
Local listener |
|
LOCK_NAME_SPACE |
NULL |
Lock name space used for generating lock
names for standby/clone database |
|
LOCK_SGA |
FALSE |
Lock entire SGA in physical memory |
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. |
|
and include the URL for the page.
