Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 27 June 2012

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

1 comments:

  1. Quantum Binary Signals

    Get professional trading signals delivered to your cell phone every day.

    Follow our trades right now & profit up to 270% daily.

    ReplyDelete