Important INIT.ORA Parameters description
You can modify certain parameters dynamically either database
or session level. Please check the v$parameter column
isses_modifiable and issys_modifiable for more details about
parameter behavior. If the first one is ‘TRUE’ indicating that Alter Session privilege
is able to modify this parameter and if second one is ‘TRUE’ indicating that
Alter system privilege can only change these parameters. It also indicates
“Immediate” that is parameter is changeable and will change takes effect
immediately and “Deferred” that is changeable but that it will not take effect
until to the next session. Never assume the settings in the init.ora
correctly represent the settings of the database. Many of them can be modified
dynamically, and can take on new settings. These new settings do not reset when
the database is restarted.
The Aim of this article is to understand the parameters impact
either negative or positive before use.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Some of the important INIT.ORA Parameters description
log_archive_dest
It Specifies the location of the archive log for recovery
purpose.
LOG_ARCHIVE_DEST = filespec
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_dest';
Default Value: Null
Range
of Values: Any valid path
or device name, except raw partitions
Modifiable: ALTER SYSTEM
Real Application Clusters: Multiple instances can have
different values.
log_archive_dest_n
It defines a destination and attributes for the archive redo
log file group.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_dest_1';
This parameter is valid only if you have installed the
Oracle Enterprise Edition, however you cannot use both
LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST as they are not
compatible.
log_archive_dest_state_n (n =
1...10)
This parameters (where n = 1, 2, 3, ... 10) specify the
availability state of the corresponding destination. The parameter 1-10
specifies one of the ten corresponding Archive destination.
Value: ENABLE (Default)
Specifies that a valid log archive destination can be used
for archiving operation.
Value: DEFER
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_dest_state_1';
Specifies valid destination but the destination is excluded
from archiving operations until re-enabled. This parameters have no effect on
the ENABLE state for the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
parameters.
log_archive_format
It specifies archive destination format. The string
generated from this format is appended to the string specified in the
LOG_ARCHIVE_DEST parameter.
%s log sequence number
%t thread number
Note: using uppercase letters (for example, %S)
for the variables causes the value to be a fixed length padded to the left with
zeros.
In Oracle 10g the log format is: %s (sequence), %t (thread),
and %r (resetlogs ID) to ensure that all archive log file names are unique.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_format';
log_archive_max_prcoesses
It specifies the number of ARCH processes to be invoked.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where
name=
'log_archive_max_processes';
Default value: 2
Range of values: 1 - 10
Modifiable: ALTER SYSTEM
log_archive_min_succeed_dest
Specifies the minimum number of archive destinations that
must succeed in order for the online logfile to be available for reuse.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_min_succeed_dest';
Default Value: 1
Range
of Values: 1 to 10 if you
are using log_archive_dest_n and 1 or 2 if you are using log_archive_dest and
log_archive_duplex_dest
Modifiable: ALTER SESSION, ALTER SYSTEM
log_archive_start
Enables (TRUE) or disables (FALSE) archiving in oracle 10g
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_archive_start';
Default Value: FALSE
Dependencies: ARCHIVELOG
log_file_name_convert
Converts the filename of a new log file on the primary
database to the filename of a log file on the standby database.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'log_file_name_convert';
Set the value of this parameter to two strings: the first
string is the pattern found in the log file names on the primary database; the
second string is the pattern found in the log file names on the standby
database.
db_flashback_retention_target
It specifies the upper limit (in minutes) on how far back in
time the database may be flashed back. It is clearly depends on how much
flashback data Oracle has kept in the flash recovery area.
Default Value: 1440 (minutes)
Range
of Values: 0 to 2322 - 1
(max value represented by 32 bits)
Parameter class: Dynamic: ALTER SYSTEM
db_recovery_file_dest
It specifies the default location for the flash recovery
area. The flash recovery area contains multiplexed copies of current control
files and online redo logs, as well as archived redo logs, flashback logs, and
RMAN backups.
You cannot specify this parameter without specify
DB_RECOVERY_FILE_DEST_SIZE.
DB_RECOVERY_FILE_DEST = directory | disk group
Parameter class: Dynamic: ALTER SYSTEM ... SID='*'
Real Application Clusters: You must set this parameter for
every instance, and multiple instances must have the same value.
db_block_checksum
If it is set to TRUE, DBWn and the direct loader will
calculate a checksum and store it in the cache header of every data block when
writing it to disk. Checksums will be verified when a block is read only if
this parameter is TRUE and the last write of the block stored a checksum.
If it is set to TRUE, every log block will also be given a
checksum before it is written to the current log.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_block_checksum';
Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause
performance overhead.
Parameter type: Boolean
Parameter class: dynamic, scope = ALTER SYSTEM
Default value: TRUE
audit_file_dest
It specifies the operating system directory into which the
audit trail is written when the audit_trail initialization parameter is set to
OS. It is also the default location for mandatory auditing information.
Dependencies: O/S
Parameter type: string
Parameter class: static
Default value: $ORACLE_HOME/RDBMS/AUDIT
asm diskgroups
Specifies a list of names of disk groups to be mounted by an
ASM instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement
is issued. It automatically adds a disk group to this parameter when a disk
group is successfully mounted, and automatically removes the disk group when it
is dismounted.
Note: This parameter may only be specified in an Automatic
Storage Management instance.
Parameter type: String
Default value: There is no default value
Parameter class: Dynamic: ALTER SYSTEM
Range of values: Comma-separated list of strings, of up to
30 characters
Real Application Clusters: Multiple instances can have
different values.
Other INIT.ORA Parameters description
active_instance_count
It designate one instance in a two-instance cluster as the primary instance and the other instance as the
secondary instance. When you set this parameter to 1,
the first instance you start up becomes the primary instance and accepts client connections. The second instance starts up as a
secondary instance, and can accept client connections
only if the first instance fails. In such an event, the secondary instance becomes the primary instance. When the failed instance can
once again be started up, it starts up as the
secondary instance, and will not accept client connections unless the now-primary instance fails.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name='active_instance_count';
Note: This parameter functions only in a cluster
with only two instances. and it has no functionality in
a cluster with more than two instances
Parameter class: Static
Range of values: 1 or >=
the number of instances in the cluster. (Values other than 1 have
no effect on the active or standby status of any instances.)
Oracle Parallel Server: must set this parameter for every instance, and multiple
instances must have the same value.
aq_tm_processes
Specifies the number of AQ Time Managers to start. If set to
1, then one queue monitor process is created to monitor the messages. If
AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not
created.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name='aq_tm_processes';
Default Value:0
Dependencies: None
Parameter class: Dynamic, scope= ALTER SYSTEM
Range of values: 0-10
archive_lag_target
Specifies the maximum number of seconds of redos the standby
could lose. The typical, or recommended value is 1800 (30 minutes). Extremely
low values can result in frequent log switches, which could degrade
performance; and also make the archiver process too busy to archive the continuously
generated logs. Values larger than 7200 seconds are not of much use in
maintaining a reasonable lag in the standby database. A value 0 disables the
time-based thread advance feature;
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name='archive_lag_target';
Default value 0: (disabled)
Parameter class: Dynamic: ALTER SYSTEM
Range of values: 0 or any integer in [60, 7200]
disk_asynch_io
Controls whether I/O to datafiles, controlfiles and logfiles
are asynchronous that is for randomly accessing the device. However, if the
asynchronous I/O implementation is not stable, it is set to FALSE to disable
asynchronous I/O, so in that case DBWR_IO_SLAVES should also be set to FALSE.
Note: If a platform supports asynchronous I/O to
disk, it is recommended that this parameter is left to its default. It has no
effect incase it is not supported by platform.
Parameter Type: Boolean
Default Value: TRUE
audit_sys_operations
It enables or disables the auditing of operations issued by user
SYS, and users connecting with SYSDBA or SYSOPER privileges.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name='audit_sys_operations';
buffer_pool_keep
It is used to improve buffer cache performance. It allows you
to keep an object in the buffer cache. In 10g it replaced by
DB_KEEP_CACHE_SIZE.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where
name='buffer_pool_keep';
Parameter type: string
Parameter class: static
buffer_pool_recycle
It enables number of database blocks/latches in recycle
buffer pool. In 10g it is replaced by db_recycle_cache_size.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name='buffer_pool_recycle';
Parameter type: string
Parameter class: static
commit_logging
It is an advanced parameter used to control how redo is
batched by Log Writer. If it is altered after setting commit_wait to force_wait,
then the force_wait option is no longer valid.
Syntax: commit_logging = { immediate | batch }
Modifiable: Yes (at both session-level and system-level)
Real Application Clusters: Each instance may have its own
setting
_corrupted_rollback_segments
You can use this command to start a
database that has corrupted rollback segments. For example
_corrupted_rollback_segments = (rbs1, rbs2, rbs3)
The problem with using this parameter is that there is a
strong possibility that your database will have data integrity problems.
cursor_sharing
It determines what kind of SQL statements can share the same
cursors.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'cursor_sharing';
Parameter type: String
Syntax: CURSOR_SHARING = {FORCE | EXACT}
Parameter class: Dynamic. Scope = ALTER SESSION, ALTER
SYSTEM.
Default value: EXACT
EXACT: causes only identical SQL statements to share
a cursor.
FORCE: force share among similar (but not identical)
statements.
Note: Forcing cursor can have unexpected results
in some DSS applications and if your applications use stored outlines.
cursor_space_for_time
Setting it to TRUE gives you a minor performance improvement
by pinning SQL areas in the shared context area and stopping private SQL areas
from being de-allocated until the application cursors are closed. If it is set
to FALSE, a check has to be made to see whether the SQL statement is contained
in a shared SQL area in the library cache.
You must allocate a shared pool that is large enough. If it
is not large enough and this parameter is set to TRUE, Oracle will display a
message “can't parse the statement”. If it can't maintain the private SQL area
because of insufficient memory, Oracle displays a message “run out of
memory”.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'cursor_space_for_time';
Parameter Type: Boolean
Default Value: FALSE
db_nk_cache_size
It (where n = 2, 4, 8, 16, 32) specifies the size of the
cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE
has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is
illegal to specify the parameter DB_4K_cache_size (because the size for the 4
KB block cache is already specified by db_cache_size).
Do not set this parameter to zero if there are any online
tablespaces with an nK block size.
You cannot set DB_32K_cache_size if the operating system's
maximum block size is less than 32 KB. Also, you cannot set DB_2K_cache_size if
the minimum block size is greater than 2 KB.
Default Value:0 (Additional block size caches are not
configured by default)
Values: Minimum: 0 (values greater than zero are rounded up
to the nearest granule size)
Maximum: operating system-dependent
db_blocking_checking
It is used to control whether block checking is done for
transaction managed blocks. This parameter is dynamic overrides any setting of
events 10210 and 10211.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_block_checksum';where
name=
'db_block_checking';
Parameter type: Boolean
Parameter class: dynamic, scope = ALTER SESSION, ALTER
SYSTEM DEFERRED
Default value: TRUE
_db_block_compute_checksum
It calculates a checksum when a block is written. When the
block is read again, the checksum is re-calculated and compared against the
stored value. If the values are different, an error is reported. Note that
setting this parameter can have an adverse effect on your performance.
db_block_size
It specifies (in bytes) the size of Oracle database blocks.
Typical values are 4096 and 8192. The value of this parameter must be a multiple
of the physical block size at the device level.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_block_size';
In Oracle Database 10g, the default value of DB_BLOCK_SIZE
is operating system specific, but is typically 8 KB (8192 bytes). If
DB_BLOCK_SIZE is not specified in the parameter file when upgrading to the new
Oracle Database 10g release, then you will receive an error when attempting to
start up your Oracle Database. Add the following to your parameter file:
Caution: Set this parameter at the time of database
creation. Do not alter it afterward.
db_file_multiblock_read_count
It specifies the maximum number of blocks read in one I/O
operation during a sequential scan. The total number of I/Os needed to perform
a full table scan depends on such factors as the size of the table, the
multiblock read count, and whether parallel execution is being utilized for the
operation. It is one of the parameters you can use to minimize I/O during table
scans.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_file_multiblock_read_count';
The maximum value is always less than the operating system's
maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE).
Default value: 8
Range of values: Operating system-dependent
Parameter class: Dynamic: ALTER SESSION, ALTER SYSTEM
db_keep_cache_size
It specifies the number of buffers in the KEEP buffer pool.
The size of the buffers in the KEEP buffer pool is the primary block size (the
block size defined by the DB_BLOCK_SIZE parameter).
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_keep_cache_size';
Default value: 0 (KEEP cache is not configured by default)
Range of values: Minimum: 0 (values greater than zero are
rounded up to the nearest granule size)
Maximum: operating system-dependent
Parameter class: Dynamic: ALTER SYSTEM
db_recycle_cache_size
It specifies the size of the RECYCLE buffer pool. The size
of buffers in the RECYCLE pool is the primary block size defined in
DB_BLOCK_SIZE).
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_recycle_cache_size';
Default value: 0M (RECYCLE cache is not configured by
default)
db_files
It specifies the maximum number of database files that can
be opened for this database. The maximum valid value is for this depends on
operating system.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'db_files';
Default value: 200
Range of values: Minimum: the largest among the absolute
file numbers of the datafiles in the database
Maximum: operating system-dependent
Real Application Clusters: must set this parameter for every
instance, each instances must have the same value.
ddl_lock_timeout
It specifies a time limit for how long DDL statements will
wait in a DML lock queue. The default value of zero indicates a status of
NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement
waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires,
then an error is returned.
Default value: 0
Modifiable: ALTER SESSION
Range of values: 0 to 1,000,000 (in seconds)
distributed_lock_timeout
Time (in seconds) a distributed transaction will wait for
locked resources.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where
name=
'distributed_lock_timeout';
Default Value: 60
Range
of Values: 1 to unlimited
dml_locks
The Maximum number of DML_LOCK (one for each table modified
in a transaction).
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'dml_locks';
If three users are modifying data on two tables, you will
need six DML locks. If this value is set to 0, enqueues are disabled and
performance is improved, particularly if you are using parallel server. The
downside is that you cannot use DDL statements, such as CREATE INDEX, and you also
can't perform LOCK TABLE tname in EXCLUSIVE MODE. If you do set the parameter
to 0, which we recommend that you do for a parallel server, you must set
DML_LOCKS equal to 0 for all instances.
Default Value: 4*Transactions
Values: 20 to unlimited or 0
Dependencies: TRANSACTIONS
fast_start_mttr_target
It enables you to specify the number of seconds the database
takes to perform crash recovery of a single instance. When specified, fast_start_mttr_target
is overridden by fast_start_io_target is overridden by log_checkpoint_interval
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'fast_start_mttr_target';
Default value: 0
Parameter class: Dynamic: ALTER SYSTEM
Range of values: 0 to 3600 seconds
Real Application Clusters: Multiple instances can have
different values, and you can change the values at runtime.
hash_area_size
Provides memory for each process performing a hash join.
According to Oracle, this parameter can be increased considerably in size and
continue to provide excellent performance improvements.
ALTER SYSTEM SET parameter=value; to change the parameter at
system level
ALTER SESSION SET parameter=value; to set it for one session
only.
select
name,
value,
ISDEFAULT, ISSES_MODIFIABLE, DESCRIPTION from v$parameter
where name= 'hash_area_size';
Caution: It is used on a per-process basis
and that there is potential for damaging paging and swapping if it is set too
high.
Default Value: 2 * SORT_AREA_SIZE
Range of Values: 0 to operating system-dependent
Modifiable: ALTER SESSION
