Initialization Parameters

The initialization parameters can be set in the init.ora file.
There are two common ways to find out to what value an initialization parameter is set:

* show parameter in sql*plus, or
* select value from v$parameter where name = lower(‘param_name’)

The parameters can be changed for the currently connected session with a alter session set … command.
If a parameter should be set in another session, dbms_system.set_bool_param_in_session or dbms_system.set_int_param_in_session can be used.

Audit related parameters
* audit_file_dest
* audit_syslog_level
* audit_sys_operations
* audit_trail
* transaction_auditing

NLS related parameters
* nls_calendar
* nls_comp
* nls_characterset
* nls_currency
* nls_date_format
* nls_date_language
* nls_dual_currency
* nls_iso_currency
* nls_language
* nls_length_semantics
* nls_nchar_conv_excp
* nls_numeric_characters
* nls_sort
* nls_territory
* nls_time_format
* nls_time_tz_format
* nls_timestamp_format
* nls_timestamp_tz_format

Optimizer related parameters
* optimizer_dynamic_sampling
* optimizer_features_enable
* optimizer_index_caching
* optimizer_index_cost_adj
* optimizer_max_permutations
* optimizer_mode
* optimizer_percent_parallel
* optimizer_secure_view_merging

Parameters affecting SGA
The following initialization parameters affect the size of the SGA:

* db_block_buffers,
* db_block_size,
* db_cache_size,
* db_keep_cache_size,
* db_recycle_cache_size,
* java_pool_size.
* large_pool_size,
* log_buffer,
* shared_pool_size,
* streams_pool_size,

Other parameters
ALLOW_FREELIST_GROUPS
This parameter was obsoleted after Oracle V6 and default to true since. At that time, it needed to be set in order to specify the freelist groups parameter in the storage clause.
ALWAYS_ANTI_JOIN
This parameter became obsolete in 9i.
BACKGROUND_DUMP_DEST
background_dump_dest specifies the directory (folder) where trace files of background processes are being written. It also specifies the location for the alert.log file.
It is also used for ORA-00600 errors.
See also max_dump_file_size.
BITMAP_MERGE_AREA_SIZE
BUFFER_POOL_KEEP

BUFFER_POOL_RECYCLE
Deprecated in favour of db_recycle_cache_size
COMMIT_WRITE

COMMIT_WORK

COMPATIBLE
The value of this parameter specifies the version that the database must adhere to.
With Oracle 10g, the value of this parameter must be set at least to 9.2; and once it was set to 10 it cannot be lowered afterwards.
The value of this parameter can be determined with dbms_utility.db_version.
CONTROL_FILES
Every database must have at least one control file that describes important characteristics of the database. This parameter specifies their location.

CONTROL_FILE_RECORD_KEEP_TIME
This parameter controls the minimum number of days that a reusable record is kept in the control file.
Its range is 0 .. 365 (=1 year)
control_file_record_keep_time also governs the size of controlfiles.
CORE_DUMP_DEST
See also max_dump_file_size.
CURSOR_SHARING
This parameter influences hard parses and soft parses and is, according to metalink note 223299.1, one of the top parameters affecting performance.The parameter can be set to either exact, similar or force.
DB_BLOCK_CHECKSUM
Specifies if integrity checking is enabled as block level.
See also log_block_checksum
DB_BLOCK_LRU_LATCHES
This parameter became obsolete in 9i.
DB_BLOCK_MAX_DIRTY_TARGET
This parameter became obsolete in 9i.
DB_CACHE_ADVICE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_CREATE_FILE_DEST
DB_CREATE_FILE_DEST sets the default location for Oracle-managed datafiles. This location is also used as the default for Oracle-managed control files and online redo logs if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
You can specify a file system directory as the default location for the creation of datafiles, control files, and online redo logs. However, the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database.
DB_CREATE_ONLINE_LOG_DEST_n
DB_CREATE_ONLINE_LOG_DEST_ n(where n= 1, 2, 3, … 5) sets the default location for Oracle-managed control files and online redo logs. You should specify at least two parameters: DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2. This provides greater fault tolerance for the logs if one of the destinations should fail.
If more than one directory is specified, then the control file or online redo log is multiplexed across the directories. One member of each online redo log is created in each directory, and one control file is created in each directory.
The directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database
DB_DOMAIN
DB_FILE_DIRECT_IO_COUNT
This parameter became obsolete in 9i.
DB_FILE_MULTIBLOCK_READ_COUNT
This parameter specifies how many blocks will be read at once when Oracle performs a full table scan or an index range scan. It doesn’t affect reads on blocks that are indexed (in which case only one block is read).
The value for this parameter should be chosen carefully. The OS on which Oracle is running should be capable of reading db_file_multiblock_read_count*db_block_size in one I/O request. If it is set too high, the optimizer will think that full table scan are cheap and will prefer them to the usage of indexes. On the other hand, setting it to low makes the optimizer choose indexes more often than necessary. By the way, the preference of indexes or full table scans is also influenced by optimizer_index_cost_adj.
DB_FILE_NAME_CONVERT
This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.
See also log_file_name_convert
DB_FILES
The maximum number of database files that can be opened for a database.
DB_FLASHBACK_RETENTION_TARGET
This is one of the relevant parameters for Flashback DB.
DB_NAME
This parameter must have the same value as the database name.
DB_RECOVERY_FILE_DEST
This is one of the relevant parameters for Flashback DB.
DB_RECOVERY_FILE_DEST_SIZE
This is one of the relevant parameters for Flashback DB.
DB_WRITER_IO_SLAVES
db_writer_io_slaves simulates asynchronous IO, but they do not perform asynchronous IO, and thus, they’re only meaningful if the OS does not support asynchronous IO.
If the OS supports asynchronous, multible dbwr processes should be used and disk_asynch_io be set to true.
DB_16K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_2K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_32K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_4K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_8K_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DISK_ASYNCH_IO
See also db_writer_io_slaves.

GC_DEFER_TIME
This parameter became obsolete in 9i.
GC_RELEASABLE_LOCKS
This parameter became obsolete in 9i.
GC_ROLLBACK_LOCKS
This parameter became obsolete in 9i.
GLOBAL_NAMES
HASH_AREA_SIZE
The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a hash join (up to the value specified with hash_area_size) is allocated from the cursor work heap (in the uga.)
See also sort_area_size.
HASH_MULTIBLOCK_IO_COUNT
This parameter became obsolete in 9i.
INSTANCE_NAME
INSTANCE_NUMBER
INSTANCE_NODESET
This parameter became obsolete in 9i.
JOB_QUEUE_INTERVAL
This parameter became obsolete in 9i.
JOB_QUEUE_PROCESSES
Controls how many jobs can run; see also dbms_job.
LM_LOCK
This parameter became obsolete in 9i.
LM_RESS
This parameter became obsolete in 9i.
LOCK_NAME_SPACE
LOCK_SGA
On platform that support it, this parameter can be set to true which will lock the entire SGA into physical memory.
LOG_ARCHIVE_DEST
Deprectated in Enterprise Edition in favour of log_archive_dest_n.
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_n (as well as log_archive_dest) can only be used if the database is running in archive log mode.
A common misstake when moving from the (deprecated) log_archive_dest to log_archive_dest_n is to forget one of the attributes such as SERVICE= or LOCATION= which causes a ORA-16179: incremental changes to “log_archive_dest_1” not allowed with SPFILE when it altered with the alter system command.
Attributes:

* SERVICE
A standby destination
See archiving to standby.
Use the lgwr option to specify LGWR transmission or the arch option to specify ARCH transmission.
* LOCATION
A local file system path, must be defined at least once.
* DELAY=minutes
delays applying of the redo log at the standby site.

v$archive_dest_status allows to query the status (and possibly the errors) for each of the defined archive destinations.
LOG_ARCHIVE_DEST_STATE_n
log_archive_dest_state_N specifies the state for log_archive_dest_N.
LOG_ARCHIVE_FORMAT
The following expandables can be used:

* %s: log sequence number
* %S: log sequence number, zero filled
* %t: thread number
* %T: thread number, zero filled
* %d: DBID

LOG_ARCHIVE_START
This parameter is deprecated in Oracle 10g
This parameter determines if the background process ARCH is started. It can be set to either true or false.
Of course, it makes no sense, if this parameter is set to true if the database is running in noarchive log mode. If ARCH is started with the database being in noarchive log mode, messages like media recovery disabled will be written into the alert.log file.
LOG_BLOCK_CHECKSUM
See also DB_BLOCK_CHECKSUM
LOG_CHECKPOINT_INTERVAL
See Events that trigger a checkpoint
The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
LOG_CHECKPOINT_TIMEOUT
See Events that trigger a checkpoint
LOG_FILE_NAME_CONVERT
This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.

FIXED_DATE
Fixed date can be set to a date in the following format:
YYYY-MM-DD HH24-MI-SS
If set, sysdate returns this date instead of the current date.

alter session set nls_date_format = ‘dd.mon.yyyy hh24:mi:ss’;
alter system set fixed_date=’2004-03-02 22:23:24′;
select sysdate from dual;

SYSDATE
——————–
02.mar.2004 22:23:24

MAX_DUMP_FILE_SIZE
This parameter specifies the maximum size for dump files such as trace files.
The unit of this parameter is measured in physical operating system blocks unless it has a suffix M or K, in which case the unit is Megabyte and Kilobyte, respectively. Note, the size of physical operating system blocks is not equal to the size of DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
MAX_IDLE_TIME
O7_DICTIONARY_ACCESSIBILITY
Default was true until 8i, and is false since 9i.
false: only privileged users can access the data dictionary.
true: any user who has been granted select any table can select from tables owned by sys. Alternatively, select_catalog_role can be granted.
The parameter should (probably) be set to false. Users that need access to sys owned table should then be granted the select any dictionary privilege.
The setting of this parameter influences grant … ANY .. to … statements.
OS_AUTHENT_PREFIX
See OS authentication
OPEN_CURSORS
This parameter defines how many cursors a session (not the cumulative sum of all sessions) can open at most.
PGA_AGGREGATE_TARGET
According to metalink note 223299.1, this is one of the top parameters affecting performance.
PROCESSES
The value of processes affects the value that the kernel parameter SEMMSL (Maximum number of semaphores in a semaphore set): it should be equal to the value of processes + 10.
If there are more than on instance on a box, the value of the instance with the greatest processes must be taken.
It affects also the optimal setting for SEMMNS (Number of semaphores in the system): 2*highets process value + 1*other process values + 10 * count of instances.
QUERY_REWRITE_ENABLED
See Create Materialized View
This parameter must be set to true to make use of function based indexes. Additionally query_rewrite_integrity must be set to trusted.
QUERY_REWRITE_INTEGRITY
This parameter can be set to either

* enforced
* trusted
* stale_tolerated

This parameter must be set to trusted to make use of function based indexes. Additionally query_rewrite_enabled must be set to true.
See also create materialized view
REMOTE_ARCHIVE_ENABLE
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
remote_login_passwordfile specifies if Oracle checks for a password file and if this password file is shared among databases.
The following values are possible:

* none
Oracle ignores the password file if it exists.
* exclusive
Password file is exclusively used by one database. Any user can be added to the password file.
* internal
Used for Oracle Parallel Server
* shared
The password file is shared among databases. However, the only users that can be authenticated are sys (and obsoletly: internal).
If the password file is shared, only SYS can be added to the password file.

RESOURCE_LIMIT
This parameter must be set to true to enforce resource limits assigned to a user through profiles
See also On profile.
RESOURCE_MANAGER_PLAN
Setting this parameter activates the resource manager.
If the paramter is set with a prepending FORCE:, the plan can only be changed by the database administrator.
ROLLBACK_SEGMENTS
Defines the rollback segments that the instance will aquire at startup
On startup, Oracle devides transactions by transactions_per_rollback_segment. If the result is greater than the number of rollback segments actually brought online by the rollback_segments init param, additional rollback segments will be brought online.
SESSIONS
SESSION_CACHED_CURSORS
See Cached cursors.
SHARED_SERVERS
SORT_AREA_SIZE
The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a sort (up to the value specified with sort_area_size) is allocated from the cursor work heap (in the uga).
See also hash_area_size.
SORT_AREA_RETAINED_SIZE
SORT_MULTIBLOCK_READ_COUNT
This parameter became obsolete in 9i.
SPFILE
Specifies the spfile to be used.
STANDBY_ARCHIVE_DEST
This parameter specifies the location of archived redo logs that come from a primary database.
The value of this parameter is displayed in the v$archive_dest view.
STAR_TRANSFORMATION_ENABLED
STANDBY_FILE_MANAGEMENT
If not set to auto, newly created tablespaces in a standby environment must be recreated manually on the standby servers as well. Similarly, newly added datafiles must be copied to the standby servers as well.
STATISTICS_LEVEL
According to metalink note 223299.1, this is one of the top parameters affecting performance.
It can be set to one of

* ALL
* TYPICAL
* BASIC

SQL_TRACE
Setting sql_trace=true is a prerequisite for using tkprof. It can also be set for a single session with alter session set sql_trace.
After setting sql_trace to true, a trace file will be written.
There is also dbms_support that should allow to trace sessions with more information.
See also diagnostic event 10046.
sql_trace seems to be deprecated since 10.2, but not removed. It still behaves as in earlier versions of Oracle.
TEXT_ENABLE
This parameter became obsolete in 9i.
TIMED_STATISTICS
This parameter must be true in order to gather timing information in v$system_event
It is also useful when using tk prof.
TRANSACTIONS
TRANSACTIONS_PER_ROLLBACK_SEGMENT
UNDO_MANAGEMENT
Set to AUTO to use Oracle 9i’s new automatic undo management.
See Undo Tablespaces.
UNDO_RETENTION
Specifies for how many seconds undo information is kept.
See Undo Tablespaces and dbms_flashback.
UNDO_SUPPRESS_ERRORS
This parameter is important if

* An Oracle database is upgraded to version 9i,
* the upgraded database uses Undo Tablespaces,
* There are still applications that use SET TRANSACTION USE ROLLBACK SEGMENT

If in such a case the parameter is set to TRUE (default is FALSE), there won’t be any errors; although it gets written into the alert log.
See undo tablespaces.
UNDO_TABLESPACE
Specifies the undo tablespaces when using automatic undo management.
USER_DUMP_DEST
The value of user_dump_dest specifies the destination (path to a operating system directory) where user processes will write trace files.
It is also used for ORA-00600 errors.
See also max_dump_file_size.
USE_POST_WAIT_DRIVER
Setting this value to true makes Oracle use post-wait drivers instead of semaphores.
UTL_FILE_DIR
This parameter specifies one more more locations to where files can be written and from where files can be read using utl_file.
Specifying multiple directories in the spfile:

alter system set utl_file_dir=’/foo/bar/dir1′,’/foo/baz/dir2′,’/tmp’ scope=spfile

Thanks to Timothy Trauernicht who notified me of an error here.
WORKAREA_SIZE_POLICY
According to metalink note 223299.1, this is one of the top parameters affecting performance.
Hidden parameters
Parameters whose name starts with an underscore are hidden. Usually, they should not be touched! Oracle won’t probably support the database if one of these parameters were changed.
_ALLOW_RESETLOGS_CORRUPTION
Allows resetlogs even if it will cause corruption.
_COLUMN_TRACKING_LEVEL
If set to 1 (the default), will cause SMON to update sys.col_usage$ with information regarding access patterns on table columns.
_DB_AGING_COOL_COUNT
Touch count set when buffer cooled.
_DB_AGING_FREEZE_CR
Make CR buffers always be too cold to keep in cache.
_DB_AGING_HOT_CRITERIA
Touch count which sends a buffer to head of replacement list.
_DB_AGING_STAY_COUNT
Touch count set when buffer moved to head of replacement list.
_DB_AGING_TOUCH_TIME
This parameter specifies a time period in which the touch count of a buffer within the buffer cache can at most be increased once.
_DB_PERCENT_HOT_DEFAULT
Percent of default buffer pool considered hot.
_DB_PERCENT_HOT_KEEP
Percent keep buffer pool considered hot.
_DB_PERCENT_HOT_RECYCLE
Percent recycle buffer pool considered hot.
_INIT_SQL_FILE
This parameter points to the file that is executed upon creation of the database (create database). As of 9i, the value is ?/rdbms/admin/sql.bsq.
_KGHDSIDX_COUNT
Controls the number of shared area subpools.
_LOG_IO_SIZE
The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.
_REALFREE_HEAP_PAGESIZE_HINT
_RECYCLEBIN
This hidden parameter is available in 10g. If set to false, then tables are purged immediately at a drop table.
_SMALL_TABLE_THRESHOLD
_SYSTEM_TRIG_ENABLED
Defaults to true and Oracle recommends setting it to false only during database upgrade.
If this parameter is set to false, then system triggers won’t be executed.
_TRACE_FILES_PUBLIC
Trace files (such as those created by a block dump are only readable by oracle, unless this parameter is set to true.
Setting this parameter to true causes a security risk as sensitive data might be written into the trace files!
_USE_ISM
If a system features ISM (intimate shared memory), Oracle uses it by default. This can be disabled by setting _use_ism to false.
As far as I can see, solaris is the only OS that has ISM.
_USE_ISM_FOR_PGA
_WAIT_FOR_SYNC
If set to false, a transaction that commits does not wait until the redo is flushed. However, a database can then not be restored if it crashes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: