Oracle Startup Parameters
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose Startup Parameters, back in the days of the dinosaurs were written into the static initSID.ora file and a restart was required to activate or deactive them. We now have the dynamic SPFILE which allows us to, in many cases, dynamically modify configuration parameter without an outage.

A small number of the parameters below are required ... the vast majority are optional. Those that are mandatory are highlighted with a light-green background.
Removing a parameter from an SPFILE ALTER SYSTEM RESET <parameter_name>;
SQL> ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
SQL> ORACLE instance started.
-- read the alert.log's tail, find the parameter name and remove it from the spfile then restart the instance

SQL> ALTER SYSTEM RESET sec_case_sensitive_logon SCOPE=SPFILE SID='*';
Page Index
 
Documented
Advanced Queuing (AQ) & Streams
AQ_TM_PROCESSES

Default: 0
Required for Advanced Queuing. Valid range of vaues is 0 to 10. Zero means Oracle manages the resource and is recommended for AQ.
aq_tm_processes=0
STREAMS_POOL_SIZE

Default: 0
Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.
streams_pool_size=0
 
ANSI Compliance
BLANK_TRIMMING

Default: FALSE
Specifies the data assignment semantics of character datatypes. Allows the data assignment of a source character string or variable to a destination character column or variable even though the source length is longer than the destination length. In this case, however, the additional length over the destination length is all blanks.
Range of values: {TRUE | FALSE}
blank_trimming=TRUE
 
Archive Logging
ARCHIVE_LAG_TARGET

Default: 0
Limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses.

The ARCHIVE_LAG_TARGET initialization parameter causes Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:
  • The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
  • The current log contains redo records.
In an Oracle Real Application Clusters environment, the instance also kicks other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is less active than the other instances.

The parameter specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.

Value 0 or any integer in [60, 7200]
archive_lag_target=0
LOG_ARCHIVE_DEST Deprecated in Enterprise Edition in favour of log_archive_dest_n
LOG_ARCHIVE_DEST_n

Default: NULL
Defines up to 10 (where n = 1, 2, 3, ... 10) destinations, each of which must specify either the LOCATION or the SERVICE attribute to specify where to archive the redo data. All other attributes are optional. Whether specifying the LOCATION or SERVICE attribute, it must be the first attribute supplied in the list of attributes.

LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | .. | 31] =
{ null_string |
{ LOCATION=path_name | SERVICE=service_name }
[ MANDATORY ]
[ REOPEN[=seconds] ]
[ DELAY[=minutes] ]
[ NOREGISTER ]
[ TEMPLATE=template ]
[ ALTERNATE=destination ]
[ DEPENDENCY=destination ]
[ MAX_FAILURE=count ]
[ SYNC | ASYNC ]
[ AFFIRM | NOAFFIRM ]
[ NET_TIMEOUT=seconds ]
[ VALID_FOR=(redo_log_type,database_role) ]
[ DB_UNIQUE_NAME ]
[ MAX_CONNECTIONS=count ]
[ COMPRESSION={ENABLE|DISABLE} ]
}
log_archive_dest_1= 'LOCATION =/app/oracle/product/flash_recovery_area/arch
valid_for=(all_logfiles,all_roles) db_unique_name=proda'

log_archive_dest_2='service=prodb valid_for=(online_logfile,primary_role) db_unique_name=prodb
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'
LOG_ARCHIVE_DEST_STATE_n

Default: ENABLE
LOG_ARCHIVE_DEST_STATE_n[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | .. | 31] =
Specifies the state for log_archive_dest_n.
Range of values: {ENABLE | DEFER | ALTERNATE}
log_archive_dest_state_1='ENABLE'
LOG_ARCHIVE_DUPLEX_DEST

Default: NULL
Similar to LOG_ARCHIVE_DEST specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).

The default setting of a null string ("") or (' ') indicates that a duplex archive destination does not exist.
log_archive_dest='/app/oracle/product/flash_recovery_area/arch'
log_archive_duplex_dest='/u02/arch'
LOG_ARCHIVE_FORMAT

Default: Operating system dependent. For OEL %t_%s_%r.dbf
Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID ensures unique names are constructed for the archived log files across multiple DB incarnations

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows:
log_archive_format=%t+%s+%r.arc
LOG_ARCHIVE_LOCAL_FIRST Deprecated
LOG_ARCHIVE_MAX_PROCESSES

Default: 4
Specifies the number of archiver background processes (ARC0 through ARCn) Oracle initially invokes. If the LOG_ARCHIVE_START initialization parameter has the value true, then this value is evaluated at instance startup though LOG_ARCHIVE_START initialization parameter was deprecated as of 10gR1. Otherwise, this parameter is evaluated when the archiver process is first invoked by SQL*Plus or SQL syntax.
Range of values:
{1 .. 40}
log_archive_max_processes=6
LOG_ARCHIVE_MIN_SUCCEED_DEST

Default: 1
The minimum number of destinations that must succeed for the online logfile to be available for reuse.
log_archive_min_succeed_dest=2
LOG_ARCHIVE_TRACE

Default 0
Value Description
0 Disable archivelog tracing
1 Track archival of redo log file
2 Track archival status of each archivelog destination
4 Track archival operational phase
8 Track archivelog destination activity
16 Track detailed archivelog destination activity
32 Track archivelog destination parameter modifications
64 Track ARCn process state activity
128 Track FAL (fetch archived log) server related activities
256 Track RFS Logical Client
512 Track LGWR redo shipping network activity
1024 Track RFS Physical Client
2048 Track RFS/ARCn Ping Heartbeat
4096 Track Real Time Apply
8192 Track Redo Apply (Media Recovery or Physical Standby)
log_archive_trace=128
 
ASH and AWR Reporting
_ASH_COMPRESSION_ENABLE

Default: TRUE
To enable or disable string compression in ASH
Range of values: {TRUE | FALSE}
_ash_compression_enable=TRUE
_ASH_DISK_FILTER_RATIO

Default: 10
Ratio of the number of in-memory samples to the number of samples actually written to disk
_ash_disk_filter_ratio=10
_ASH_DISK_WRITE_ENABLE

Default: TRUE
To enable or disable Active Session History flushing
Range of values: {TRUE | FALSE}
_ash_disk_write_enable=TRUE
_ASH_DUMMY_TEST_PARAM

Default: 0
Oracle internal dummy ASH parameter used ONLY for testing!
_ash_dummy_test_param=0
_ASH_EFLUSH_TRIGGER

Default: 66
The percentage above which if the in-memory ASH is full the emergency flusher will be triggered
_ash_eflush_trigger=66
_ASH_ENABLE

Default: TRUE
To enable or disable Active Session sampling and flushing
Range of values: {TRUE | FALSE}
_ash_enable=FALSE
_ASH_MIN_MMNL_DUMP

Default: 90
Minimum Time interval passed to consider MMNL Dump
_ash_min_mmnl_dump=90
_ASH_SAMPLE_ALL

Default: FALSE
To enable or disable sampling every connected session including ones waiting for idle waits
Range of values: {TRUE | FALSE}
_ash_sample_all=TRUE
_ASH_SAMPLING_INTERVAL 1000 Time interval between two successive Active Session samples in millisecs
_ash_sampling_interval=2000
AWR_SNAPSHOT_TIME_OFFSET

Default: 0
Setting for AWR Snapshot Time Offset
awr_snapshot_time_offset=0
_AWR_CDBPERF_THRESHOLD

Default: 21
Setting for AWR CDBPERF Threshold
_awr_cdbperf_threshold=21
_AWR_CORRUPT_MODE

Default: FALSE
AWR Corrupt Mode
Range of values: {TRUE | FALSE}
_awr_corrupt_mode=TRUE
_AWR_DISABLED_FLUSH_TABLES Disable flushing of specified AWR tables
TBD
_AWR_DISABLED_PURGE_TABLES Disable purging of specified AWR tables
TBD
_AWR_FLUSH_THRESHOLD_METRICS

Default TRUE
Enable/Disable Flushing AWR Threshold Metrics
Range of values: {TRUE | FALSE}
_awr_flush_threshold_metrics=TRUE
_AWR_FLUSH_WORKLOAD_METRICS

Default: FALSE
Enable/Disable Flushing AWR Workload Metrics
Range of values: {TRUE | FALSE}
_awr_flush_workload_metrics=TRUE
_AWR_MMON_CPUUSAGE

Default: TRUE
Enable/disable AWR MMON CPU Usage Tracking
Range of values: {TRUE | FALSE}
_awr_mmn_cpuusage=TRUE
_AWR_MMON_DEEP_PURGE_ALL_EXPIRED

Default: FALSE
Allows deep purge to purge AWR data for all expired snapshots
Range of values: {TRUE | FALSE}
_awr_mmon_deep_purge_all_expired=TRUE
_AWR_MMON_DEEP_PURGE_EXTENT

Default: 7
Set extent of rows to check each deep purge run
_awrmmon_deep_purge_extent=7
_AWR_MMON_DEEP_PURGE_INTERVAL

Default: 7
Set interval for deep purge of AWR contents
_awr_mmon_deep_purge_interval=7
_AWR_MMON_DEEP_PURGE_NUMROWS

Default: 5000
Set max number of rows per table to delete each deep purge run
_awr_mmon_deep_purge_numrows=5000
_AWR_PARTITION_INTERVAL

Default: 0
Setting for AWR Partition Interval
_awr_partition_interval=0
_AWR_PDB_REGISTRATION_ENABLED

Default: FALSE
Parameter to enable/disable AWR PDB Registration
Range of values: {TRUE | FALSE}
_awr_pdb_registration_enabled=TRUE
_AWR_REMOTE_TARGET_DBLINK AWR Remote Target DBLink for Flushing
TBD
_AWR_RESTRICT_MODE

Default: FALSE
AWR Restrict Mode
Range of values: {TRUE | FALSE}
_awr_restrict_mode=TRUE
_AWR_SNAPSHOT_LEVEL

Default: BESTFIT
Set Default AWR snapshot level
_awr_snapshot_level=BESTFIT
_AWR_SQL_CHILD_LIMIT

Default: 200
Setting for AWR SQL Child Limit
_awr_sql_child_limit=100
_FLUSH_PLAN_IN_AWR_SQL

Default: 0
Plan is being flushed from an AWR flush SQL
_flush_plan_in_awr_sql=0
_REMOTE_AWR_ENABLED

Default: FALSE
Enable/disable Remote AWR Mode
_remote_awr_enabled=TRUE
 
ASM
ASM_DISKGROUPS

Default: NULL
Specifies a list of names of disk groups to be mounted by an Automatic Storage Management instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement is issued. Range of values Comma-separated list of strings, of up to 30 characters in length.
asm_diskgroups=dgroupA, dgroupB
ASM_DISKSTRING

Default: NULL
An operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. Value is a comma delimited string of disks.
asm_diskstring='dev/rdsk/*s2, /dev/rdsk/c1*'
ASM_POWER_LIMIT

Default: 1
Specifies the maximum power on an Automatic Storage Management instance for disk rebalancing.
Range of values
 {1 to 11}
asm_power_limit=5
ASM_PREFERRED_READ_FAILURE_ GROUPS

Default: NULL
Specifies the failure groups that contain preferred read disks. Preferred disks are instance specific.
TBD
_ASMSID

Default: asm
ASM instance id
TBD
 
Auditing
AUDIT_FILE_DEST

Default:
$ORACLE_BASE/admin/ORACLE_SID/adump
The operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os.
audit_file_dest=/app/oracle/product/admin/orabase/adump
AUDIT_SYS_OPERATIONS

Default: FALSE
Enables or disables auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges.
Syntax AUDIT_SYS_OPERATIONS = {TRUE | FALSE}
audit_sys_operations=TRUE
AUDIT_SYSLOG_LEVEL

Default: NULL
Allows SYS and standard OS audit records to be written to the system audit log using the SYSLOG utility. If you use this parameter, it is best to assign a file corresponding to every combination of facility and priority (especially KERN.EMERG) in syslog.conf. Sometimes these are assigned to print to the console in the default syslog.conf file. This can become annoying and will be useless as audit logs. Also, if you use this parameter, it is best to set the maximum length of syslog messages in the system to 512 bytes. If AUDIT_SYSLOG_LEVEL is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), then SYS audit records are written to the system audit log. If AUDIT_SYSLOG_LEVEL is set and standard audit records are being sent to the operating system (AUDIT_TRAIL = os), then standard audit records are written to the system audit log.

AUDIT_SYSLOG_LEVEL = 'facility_clause.priority_clause'

Facility Clause Options: { USER | LOCAL[0 | 1 | 2 | 3 | 4 | 5 | 6 | 7] | SYSLOG | DAEMON | KERN | MAIL | AUTH | LPR | NEWS | UUCP | CRON }
Priority Options: { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
audit_syslog_level='KERN.EMERG'
AUDIT_TRAIL

Default: DB
Enables or disables database auditing.
Syntax: AUDIT_TRAIL = {DB | OS | NONE | TRUE | FALSE | DB_EXTENDED}
audit_trail='DB'
 
Backup and Restore
BACKUP_TAPE_IO_SLAVES

Default: FALSE
Specifies if I/O server processes (also called slaves) are used by Recovery Manager to back up, copy, or restore data to tape.
Range of values: {TRUE | FALSE}
backup_tape_io_slaves=FALSE
CONTROL_FILE_RECORD_KEEP_TIME

Default: 7
specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.
Range of values: 0 to 365 (days)
ALTER SYSTEM SET control_file_record_keep_time=30 SCOPE=BOTH;
FAST_START_IO_TARGET Deprecated in favor of FAST_START_MTTR_TARGET
FAST_START_MTTR_TARGET Specify the number of seconds the database takes to perform crash recovery of a single instance. The value Is overridden by FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL so do not set them when using this parameter. The estimated MTTR can be found in v$intance_recovery.
Range of values: 1 to 3600 (seconds)
fast_start_mttr_target=15
RECOVERY_PARALLELISM

Default: System-determined parallel recovery
Specifies the number of processes to participate in instance or crash recovery. To force serial crash and instance recovery, set the parameter to 0 or 1. 0 disables parallel instance and crash recovery on a system that has multiple CPUs. An alert log displays the degree of parallelism that was chosen when the database instance/recovery starts.
recovery_parallelism=0
TAPE_ASYNCH_IO

Default: TRUE
Controls whether I/O to sequential devices (for example, backup or restore of Oracle data to or from tape) is asynchronous.
Range of values: {TRUE | FALSE}
tape_asynch_io=FALSE
 
BFILEs
SESSION_MAX_OPEN_FILES

Default: 10
Specifies the maximum number of BFILEs that can be opened in any session. Once this number is reached, subsequent attempts to open more files in the session by using DBMS_LOB.FILEOPEN() or OCILobFileOpen() will fail.
session_max_open_files=6
 
Buffer Cache and I/O
DB_nK_CACHE_SIZE

Defaults: 0
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.
Range of values: 0 or (values greater than zero are rounded up to the nearest granule size)
db_4K_cache_size=8M
DB_BLOCK_BUFFERS

Default: 0
Specifies the number of database buffers in the buffer cache. It is one of several parameters that contribute to the total memory requirements of the SGA of an instance. Range of values 50 to an operating system-specific maximum. DB_BLOCK_BUFFERS cannot be combined with the dynamic DB_CACHE_SIZE parameter.
db_block_buffers=10000
DB_BLOCK_SIZE

Default: 8192
Specifies (in bytes) the size of Oracle database blocks. For Real Application Clusters, this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. Oracle uses one database block for each freelist group. Decision support system (DSS) and data warehouse database environments tend to benefit from larger block size values.
Range of values 2048 to 32768, but your operating system may have a narrower range
db_block_size=8192
DB_CACHE_ADVICE

Default: ON
Enables or disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.
Syntax DB_CACHE_ADVICE = {ON | READY | OFF}
db_cache_advice=READY
DB_CACHE_SIZE

Default 0 ... if SGA_TARGET is set..
Specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

Syntax DB_CACHE_SIZE = integer [K | M | G]. A value of zero is illegal
db_cache_size=48M
DB_FILE_MULTIBLOCK_READ_COUNT

Default: 8
One of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan (applies to full table scans and index fast full scans, so non-random I/O). 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.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/Osize) / DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

Depending on your exact version and the use of system statistics with version 9+ larger values for the db_file_multiblock_read_count parameter influence the cost based optimizer to favor full table scans over using indexes.
Range of values Operating system-dependent
db_file_multiblock_read_count=128

ALTER SYSTEM RESET db_file_multiblock_read_count SCOPE=SPFILE SID='*';
DB_KEEP_CACHE_SIZE

Default: 0
Specifies the size of 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 initialization parameter). Minimum: 0 (values greater than zero are rounded up to the nearest granule size) Maximum: operating system-dependent.
db_keep_cache_size=128K
DB_RECYCLE_CACHE_SIZE

Default: 0
Specifies the size of the RECYCLE buffer pool. The size of the buffers in the RECYCLE pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter). Minimum: 0 (values greater than zero are rounded up to the nearest granule size) Maximum: operating system-dependent.
db_recycle_cache_size=64K
DB_WRITER_PROCESSES

Default: 1 or CPU_COUNT / 8, whichever is greater
Specifies the initial number of database writer processes for an instance.
Range of values: 1 to 36
db_writer_processes=2
DBWR_IO_SLAVES

Default: 0
Relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.

If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false).

Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.

I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.

Range of values: 0 to operating system-dependent
dbwr_io_slaves=1
DISK_ASYNCH_IO

Default: TRUE
Controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans. Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.

Range of values: {TRUE | FALSE}
disk_asynch_io=FALSE
FILESYSTEMIO_OPTIONS

Default: not set
Enables and disables asynchronous I/O or direct I/O on file system files. SETALL enables both. This parameter can not be modified in memory.

Range of values: {NONE | SETALL | DIRECTIO | ASYNCH}
filesystemio_options=SETALL
READ_ONLY_OPEN_DELAYED

Default: FALSE
Determines when datafiles in read-only tablespaces are accessed.
If TRUE the datafiles are accessed for the first time only when an attempt is made to read data stored within them.
If FALSE the datafiles are accessed at database open time.

Range of values: {TRUE | FALSE}
read_only_open_delayed=TRUE
USE_INDIRECT_DATA_BUFFERS

Default: FALSE
Controls how the system global area (SGA) uses memory. It enables or disables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory it is ignored.

Range of values: {TRUE | FALSE}
use_indirect_data_buffers=TRUE
 
Compression
USE_INDIRECT_DATA_BUFFERS

Default: FALSE
Controls how the system global area (SGA) uses memory. It enables or disables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory it is ignored.

Range of values: {TRUE | FALSE}
use_indirect_data_buffers=TRUE
 
 
Cursors and Library Cache
CURSOR_SHARING

Default: Exact
FORCE: Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR: Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
EXACT: Only allows statements with identical text to share the same cursor.

Range of values: {SIMILAR | EXACT | FORCE}
cursor_sharing=SIMILAR
CURSOR_SPACE_FOR_TIME Deprecated
OPEN_CURSORS

Default: 50
Specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. This parameter can be used to prevent a session from opening an excessive number of cursors.
Range of values: {0 - 65536}
open_cursors=320
SESSION_CACHED_CURSORS

Default: 50
Specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
Range of values: 0 to an operating system dependent limit
session_cached_cursors=220
 
Data Guard
DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

Default: Operating system dependent
Specifies the names for the Data Guard broker configuration files. Every database that is part of a Data Guard broker configuration has two broker configuration files, which contain entries that describe the state and properties of the configuration (such as the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration). Two files are provided so as to always maintain the last known good state of the configuration.

If DG_BROKER_CONFIG_FILEn is not explicitly defined, then it is set to an operating system-specific default value at instance startup. The parameter can only be altered when the DMON (Data Guard broker) process is not running.
Range of values per "n" listed: One filename
dg_broker_config_file=$ORACLE_BASE/admin/orabasee/dr1db_orabase.dat
dg_broker_config_file=$ORACLE_BASE/admin/orabase/dr2db_orabase.dat
DG_BROKER_START

Default: FALSE
Enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started.
Range of values: {TRUE | FALSE}
dg_broker_start=TRUE
DB_FILE_NAME_CONVERT

Defalt: NULL
Useful for creating a duplicate database for recovery purposes. You can also use DB_FILE_NAME_CONVERT to rename the datafiles in the clone controlfile when setting up a clone database during tablespace point-in-time recovery.
db_file_name_convert='/dbs/proda/','/dbs/prodb/s_','dbs/proda/ ','dbs/prodb/s_'
DB_LOST_WRITE_PROTECT

Default: NONE
Enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

When the parameter is set to TYPICAL on the primary database, the instance logs buffer cache reads for read-write tablespaces in the redo log, which is necessary for detection of lost writes.

When the parameter is set to FULL on the primary database, the instance logs reads for read-only tablespaces as well as read-write tablespaces.

When the parameter is set to TYPICAL or FULL on the standby database or on the primary database during media recovery, the instance performs lost write detection.

When the parameter is set to NONE on either the primary database or the standby database, no lost write detection functionality is enabled.

Range of values: {NONE | TYPICAL | FULL}
db_lost_write_protect=FULL
FAL_CLIENT Specifies the FAL (fetch archive log) client name that is used by the FAL service
fal_client=proda
FAL_SERVER Specifies the FAL (fetch archive log) server for a standby database
fal_server=prodb
LOG_ARCHIVE_CONFIG

Default: 'SEND,RECEIVE,NODG_CONFIG'
Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.
Range of values: {DG_CONFIG | NODG_CONFIG | NORECEIVE | NOSEND | RECEIVE | SEND}
log_archive_config='DG_CONFIG=(proda,prodb)'
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. If you add a log file to the  primary database, you must add a corresponding file to the standby database.
log_file_name_convert='/dbs/proda/','/dbs/prodb/s_','dbs/proda/ ','dbs/prodb/s_'
STANDBY_ARCHIVE_DEST Deprecated
STANDBY_FILE_MANAGEMENT

Default: MANUAL
Enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. STANDBY_FILE_MANAGEMENT is only applicable on physical standbys.
Range of values: {MANUAL | AUTO}
standby_file_management=MANUAL
 
Database / Instance / Identification
DB_DOMAIN

Default: NULL
In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure.
Range of values is any legal string of name components, separated by periods and up to 128 characters long (including the periods). The value cannot be NULL.
db_domain='MLIB.ORG'
DB_NAME Specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.
db_name=orabase
 
Diagnostics / Statistics
BACKGROUND_CORE_DUMP

Default: partial
Specifies whether Oracle includes the SGA in the core file for Oracle background processes.
Range of Values: {PARTIAL | FULL}
background_core_dump=partial
BACKGROUND_DUMP_DEST Deprecated
COMMIT_LOGGING Controls how redo is batched by Log Writer. If COMMIT_LOGGING is altered after setting COMMIT_WAIT to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.
Range of Values: {IMMEDIATE | BATCH}
commit_logging=BATCH
CORE_DUMP_DEST
Default: {ORACLE_HOME}/dbs
Primarily a UNIX parameter not supported on all platforms. Specifies the directory for core file dumps
core_dump_dest='/app/oracle/product/diag/rdbms/orabase/orabase/cdump'
DB_BLOCK_CHECKING

Default FALSE
Controls whether Oracle performs block checking for data blocks. When this parameter is set to TRUE, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on.
Range of values: {TRUE | FALSE}
db_block_checking=TRUE
DB_BLOCK_CHECKSUM

Default: TRUE
Determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is TRUE and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.
Range of values: {TRUE | FALSE}
db_block_checksum=FALSE
DIAGNOSTIC_DEST Replacement for BDUMP and UDUMP related parameters. Points to ADR_BASE
diagnostic_dest='/app/oracle/product'
ENABLE_DDL_LOGGING

Default: FALSE
Enables or disables the writing of DDL statements to the alert log
enable_ddl_logging=TRUE
EVENT

No Default
Debug event control - default null string [CHAR]
Do not alter the value of this parameter except under the supervision of Oracle Support Services staff
MAX_DUMP_FILE_SIZE

Default: UNLIMITED
Specifies the maximum size of trace files (excluding the alert file)
Valid values are: 0 to unlimited in the form <integer>[K | M | G | UNLIMITED]
max_dump_file_size=1G
SHADOW_CORE_DUMP

Default: PARTIAL
Specifies whether Oracle includes the SGA in the core file for foreground (client) processes.
Valid values are: {PARTIAL | FULL | NONE}
shadow_core_dump=FULL
STATISTICS_LEVEL

Default: TYPICAL
Specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
Valid values are: {ALL | TYPICAL | BASIC}
statistics_level=ALL
TIMED_OS_STATISTICS

Default: 0
When TRUE statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views
Valid values are: {0 | 5} - 0 for BASIC and TYPICAL, 5 for ALL
timed_os_statistics=5
TIMED_STATISTICS

Default: TRUE
When TRUE statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views
Valid values are: {TRUE | FALSE}
timed_statistics=FALSE
TRACE_ENABLED

Default: FALSE
Controls tracing of the execution history, or code path, of Oracle. Oracle Support Services uses this information for debugging.
Valid values are: {TRUE | FALSE}
trace_enable=FALSE
TRACEFILE_IDENTIFIER

No default value is set
Specifies a custom identifier that becomes part of the Oracle Trace file name. A custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.
tracefile_identifier='test_plan1';
USER_DUMP_DEST Deprecated
 
Distributed Replication
COMMIT_POINT_STRENGTH

Default: 1
Relevant only in distributed database systems. Specifies a value that determines the commit point site in a distributed transaction. The node in the transaction with the highest value for COMMIT_POINT_STRENGTH will be the commit point site.
Range of values: {0 - 255}
commit_point_strength=50
DISTRIBUTED_LOCK_TIMEOUT

Default: 60
Specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
Range of values: {1 to unlimited}
distributed_lock_timeout=120
GLOBAL_NAMES

Default: FALSE
Specifies whether a database link is required to have the same name as the database to which it connects.
Range of values: {TRUE | FALSE}
global_names=TRUE
HS_AUTOREGISTER

Default: TRUE
Enables or disables automatic self-registration of Heterogeneous Services (HS) agents. When enabled, information is uploaded into the server's data dictionary to describe a previously unknown agent class or a new agent version.
Range of values: {TRUE | FALSE}
hs_autoregister=FALSE
OPEN_LINKS

Default: 4
Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.
Range of values: {0 - 255}
open_links=10
OPEN_LINKS_PER_INSTANCE

Default: 4
Specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.
Range of values: {0 - 255}
open_links_per_instance=5
REPLICATION_DEPENDENCY_TRACKING

Default: TRUE
Enables or disables dependency tracking for read/write operations to the database. Dependency tracking is essential for propagating changes in a replicated environment in parallel.
Range of values: {TRUE | FALSE}
 replication_dependency_tracking=FALSE
 
Exadata Storage Server
CELL_OFFLOAD_COMPACTION

Default: ADAPTIVE
Cell packet compaction strategy
Range of values: {ADAPTIVE |(if others they appear to be undocumented) }
cell_offload_compaction=ADAPTIVE
CELL_OFFLOAD_DECRYPTION

Default: TRUE
Enable SQL processing offload of encrypted data to cells
Range of values: {TRUE | FALSE}
cell_offload_decryption=TRUE
CELL_OFFLOAD_PARAMETERS

additional cell offload parameters
Default: Not set
TBD
CELL_OFFLOAD_PLAN_DISPLAY

cell offload explain plan display
Default: AUTO
cell_offload_plan_display=MANUAL
CELL_OFFLOAD_PROCESSING

enable SQL processing offload to cells
Default: TRUE
cell_offload_processing=FALSE
CELL_OFFLOADGROUP_NAME

set the offload group name
Default: Not set
TBD
 
File Locations, Names, and Sizes
CONTROL_FILES

Default: No default set
Control file names list [file_path,file_path..]
*.control_files='c:\oracle\product\oradata\orabase\control01.ctl', 'c:\oracle\product\oradata\orabase\control02.ctl', 'c:\oracle\product\oradata\orabase\control03.ctl'
DB_CREATE_FILE_DEST

Default: NULL
Specifies the default location for Oracle-Managed datafiles (OMF).
db_create_file_dest = <directory | disk group>
db_create_file_dest='/u01/oradata/orabase'
DB_CREATE_ONLINE_LOG_DEST_n

Default: NULL
Specifies the default location for Oracle-managed control files and online redo logs.
Syntax DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] = directory | disk group
db_create_online_log_dest_1='/app/oracle/product/oradata/logs'
DB_FILES

Default: 200
Specifies the maximum number of database files that can be opened for this database.
Minimum: the largest among the absolute file numbers of the datafiles in the database
Maximum: operating system-dependent
db_files=100
DB_RECOVERY_FILE_DEST Specifies the default location for the flash recovery area.
Syntax DB_RECOVERY_FILE_DEST = {DIRECTORY | DISK GROUP}
db_recovery_file_dest='c:\oracle\product\flash_recovery_area'
DB_RECOVERY_FILE_DEST_SIZE Specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area
Syntax DB_RECOVERY_FILE_DEST_SIZE = integer [K | M | G]
db_recovery_file_dest_size=4G
FILE_MAPPING Deprecated but still supported for backward compatibility
IFILE

No Default
Embed the named parameter file in the init.ora as it is read.
ifile=?/dbs/proda_dg.ora
SPFILE

No Default
The value of this parameter is the name of the current server parameter file (SPFILE) in use. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use.
spfile=/app/oracle/product/11.1.0/db_1/dbs/spfileorabase.ora
UTL_FILE_DIR

No Default
Alllow the specification of one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file. This parameter should not be used in favor of DIRECTORY objects.
utl_file_dir=*
utl_file_dir=/stage
 
Flashback
DB_FLASHBACK_RETENTION_TARGET

Default: 1440
Specifies the upper limit (in minutes) on how far back in time the database may be flashed back.
Range of values 0 to 232 - 1 (max value represented by 32 bits)
db_flashback_retention_target=2880
RECYCLEBIN

Default: ON
Specifies if the recyclebin holds dropped tables and associated indexes.
Range of values: {ON | OFF}
recyclebin=ON
 
Group By
_GROUPBY_NOPUSHDOWN_CUT_RATIO

Default: 3
Group by nopushdown cut ratio
_groupby_nopushdown_cut_ratio=3
_GROUPBY_ORDERBY_COMBINE

Default: 5000
Group By/Order By don't combine threshold
_groupby_orderby_combine=5000
_OPTIMIZER_AGGR_GROUPBY_ELIM

Default: TRUE
Group-By and aggregation elimination
Range of values: {TRUE | FALSE}
_optimizer_aggr_groupby_elim=TRUE
_OPTIMIZER_GROUP_BY_PLACEMENT

Default: TRUE
Consider group-by placement optimization
Range of values: {TRUE | FALSE}
_optimizer_group_by_placement=TRUE
_OPTIMIZER_REDUCE_GROUPBY_KEY

Default: TRUE
Group-By key reduction
Range of values: {TRUE | FALSE}
_optimizer_reduce_groupby_key=TRUE
_PX_GROUPBY_PUSHDOWN_FORCE

Default: ON
Perform group-by pushdown for parallel query
Range of values: {ON | OFF}
_px_groupby_pushdown_force=ON
 
In-Memory
INMEMORY_CLAUSE_DEFAULT

Default: No Default
Default in-memory clause for new tables
TBD
INMEMORY_FORCE

Default: DEFAULT
Force tables to be in-memory or not
inmemory_force=DEFAULT
INMEMORY_MAX_POPULATE_SERVERS

Default: 2
Maximum inmemory populate servers
inmemory_max_populate_servers=2
INMEMORY_QUERY

Default: ENABLE
Specifies whether in-memory queries are allowed
Range of values: {ENABLE | DISABLE}
inmemory_query=ENABLE
INMEMORY_SIZE Size in bytes of in-memory area
inmemory_size=1073741824
INMEMORY_TRICKLE_REPOPULATE
_SERVERS_PERCENT

Default: 1
Inmemory trickle repopulate servers percent
inmemory_trickle_repopulate_servers_percent=1
OPTIMIZER_INMEMORY_AWARE

Default: TRUE
Optimizer in-memory columnar awareness
Range of values: {TRUE | FALSE}
optimizer_inmemory_aware=TRUE
 
I/O
FILEIO_NETWORK_ADAPTERS

No Default
Specifies a list of network adapters that can be used to access the disk storage. On platforms where the database files reside in network attached storage, this parameter provides the storage access library the list of network adapters that can be used to access the storage.
fileio_metwork_adapters=netapp1.mlib.org
FILESYSTEMIO_OPTIONS

No Default
Enables or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
Range of values: {ASYNCH, DIRECTIO, SETALL, NONE}
filesystemio_options=setall
 
Java
JAVA_JIT_ENABLED

Default: Operating system dependant
Enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.
Range of values: {TRUE | FALSE}
java_jit_enabled=TRUE
JAVA_MAX_SESSIONSPACE_SIZE

Default: 0
Java session space is the memory that holds Java state from one database call to another. JAVA_MAX_SESSIONSPACE_SIZE specifies (in bytes) the maximum amount of session space made available to a Java program executing in the server. When a user's session-duration Java state attempts to exceed this amount, the Java virtual machine kills the session with an out-of-memory failure.
java_max_sessionspace_size=0
JAVA_POOL_SIZE

Default: 0 (in most situations)
Specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during runtime execution. This memory includes the shared in-memory representation of Java method and class definitions, as well as the Java objects that are migrated to the Java session space at end-of-call.
java_pool_size=0
JAVA_SOFT_SESSIONSPACE_LIMIT

Default: 0
Java session space is the memory that holds Java state from one database call to another. JAVA_SOFT_SESSIONSPACE_LIMIT specifies (in bytes) a soft limit on Java memory usage in a session, as a means to warn you if a user's session-duration Java state is using too much memory. When a user's session-duration Java state exceeds this size, Oracle generates a warning that goes into the trace files.
java_soft_sessionspace_limit=0
 
Memory Allocation, Sizing, and Usage
BITMAP_MERGE_AREA_SIZE

Default: 1048576 for W2K & Linux
For systems containing bitmap indexes: Specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index. The default value is 1 MB. A larger value usually improves performance, because the bitmap segments must be sorted before being merged into a single bitmap. Range of values Operating system-dependent
bitmap_merge_area_size=1048576
BUFFER_POOL_KEEP Deprecated
BUFFER_POOL_RECYCLE Deprecated
CREATE_BITMAP_AREA_SIZE

Default: 8388608
Relevant only for systems containing bitmap indexes. It specifies the amount of memory (in bytes) allocated for bitmap creation. The default value is 8 MB. A larger value may speed up index creation.
create_bitmap_area_size=8388608
HASH_AREA_SIZE

Default: Derived: 2 * SORT_AREA_SIZE
Relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.
hash_area_size=2097152
HI_SHARED_MEMORY_ADDRESS
Default: 0
Specifies the starting address at runtime of the SGA. It is ignored on platforms that specify the SGA's starting address at link time.
hi_shared_memory_address=0
LARGE_POOL_SIZE

No Default
Specifies (in bytes) the size of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.
large_pool_size=0
LOCK_SGA

Default: False
Locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.
lock_sga=FALSE
LOG_BUFFER

No Default
Specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
Range of values: 2 MB to 64 MB (32-bit O/S), 2 MB to 256 MB (64-bit O/S)
log_buffer=4964352
MEMORY_MAX_TARGET

Default: 0
Specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter. See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and MEMORY_TARGET affect each other.
memory_max_target=820M
MEMORY_TARGET

Default: 0
Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.
memory_target=820M
OBJECT_CACHE_MAX_SIZE_PERCENT

Default: 10
The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. OBJECT_CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size. The maximum size is equal to the optimal size plus the product of this percentage and the optimal size. When the cache size exceeds this maximum size, the system will attempt to shrink the cache to the optimal size.
object_cache_max_size_percent=10
OBJECT_CACHE_OPTIMAL_SIZE

Default: 100K
Specifies (in bytes) the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.
object_cache_optimal_size=102400
OLAP_PAGE_POOL_SIZE

Default: 0
Specifies (in bytes) the size of the OLAP page pool.
olap_page_pool_size=0
PGA_AGGREGATE_TARGET
Default: 10 MB or 20% of the size of the SGA, whichever is greater
Specifies the target aggregate PGA memory available to all server processes attached to the instance.
Minimum: 10 MB, Maximum: 4096 GB - 1
pga_aggregate_target=0
PRE_PAGE_SGA

Default: FALSE
Determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.
pre_page_sga=TRUE
SGA_MAX_SIZE
No Default
Specifies the maximum size of the SGA for the lifetime of the instance.
sga_max_size=4G
SGA_TARGET

Default: 0
Specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
* Buffer cache (DB_CACHE_SIZE)
* Shared pool (SHARED_POOL_SIZE)
* Large pool (LARGE_POOL_SIZE)
* Java pool (JAVA_POOL_SIZE)
* Streams pool (STREAMS_POOL_SIZE)


If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
* Log buffer
* Other buffer caches, such as KEEP, RECYCLE, and other block sizes
* Fixed SGA and other internal allocations


Range of values: {64 MB to operating system-dependent}
sga_target=0
SHARED_MEMORY_ADDRESS

Default: 0
SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS specify the starting address at runtime of the system global area (SGA). This parameter is ignored on the many platforms that specify the SGA's starting address at linktime.

This parameter to specifies the entire address on 32-bit platforms and to specify the low-order 32 bits of a 64-bit address on 64-bit platforms. Use HI_SHARED_MEMORY_ADDRESS to specify the high-order 32 bits of a 64-bit address on 64-bit platforms. If both parameters are 0 or unspecified, the SGA address defaults to a platform-specific location.
shared_memory_address=0
SHARED_POOL_RESERVED_SIZE

Default: 5% of the value of SHARED_POOL_SIZE
Specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
share_pool_reservde_size=16567500
SHARED_POOL_SIZE

Default: 0
Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. Larger values improve performance in multi-user systems. Smaller values use less memory.
shared_pool_size=0
SORT_AREA_RETAINED_SIZE

Default: 0 (Derived from SORT_AREA_SIZE)
Specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.
sort_area_retained_size=0
SORT_AREA_SIZE

Default: 65536
Specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
sort_area_size=1048576
 
Miscellaneous
COMPATIBLE Allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
compatible=11.2.0.0.0
CONTROL_MANAGEMENT_PACK_ACCESS

EE Default: DIAGNOSTIC+TUNING
Specifies which of the Server Manageability Packs should be active.
Range of values: {NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING}
control_management_pack_access=DIAGNOSTIC+TUNING
CPU_COUNT Specifies the number of CPUs available to Oracle. Range of values 0 to unlimited.
cpu_count=2
DB_SECUREFILE

Default: PERMITTED
Specifies whether to treat LOB files as SecureFiles. Requires the tablespace is created with SEGMENT SPACE MANAGEMENT = AUTO
Range of values: {NEVER | PERMITTED | ALWAYS | IGNORE}
db_securefile=PERMITTED
DB_UNIQUE_NAME Specifies a globally unique name for the database. Databases with the same DB_NAME within the same DB_DOMAIN. Every database's DB_UNIQUE_NAME must be unique within the enterprise.
Syntax DB_UNIQUE_NAME = database_unique_name
db_unique_name=orabase
DB_UNRECOVERABLE_SCN_TRACKING When TRUE turns off control file writes to update fields that track the highest unrecoverable SCN and Timing during a NOLOGGING Direct Path operation.
Syntax DB_UNRECOVERABLE_SCN_TRACKING = [TRUE | FALSE]
db_unique_name=orabase
DDL_LOCK_TIMEOUT

Default: 0
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.
ddl_lock_timeout=0
FIXED_DATE Enables you to set a constant date that SYSDATE will always return instead of the current date. To undo a fixed date setting, specify FIXED_DATE=NONE. This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time.
Format: FIXED_DATE = [YYYY-MM-DD-HH24:MI:SS (or the default date format) | NONE]
fixed_date=NONE
JOB_QUEUE_PROCESSES

Default: 1000
Specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package.
job_queue_processes=1000
LOG_CHECKPOINT_INTERVAL

Default: 0
Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical o/s blocks, not database blocks.
Range of values: {0 to 2^31 - 1}
log_checkpoint_interval=0
LOG_CHECKPOINT_TIMEOUT

Default: 1800
Specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty for more than integer seconds.
Range of values {0 to 2^31 - 1}
log_checkpoint_timeout=1800
LOG_CHECKPOINTS_TO_ALERT

Default: FALSE
True writes checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency and whether exceptions are being generated.
Range of values {TRUE | FALSE}
max_checkpoints_to_alert=TRUE
OS_AUTHENT_PREFIX

Default: OPS$
Specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.
os_authent_prefix=OPS$
PROCESSES

Default: 100
Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.
processes=1250
RDBMS_SERVER_DN

No Default
Specifies the Distinguished Name (DN) of the Oracle server. It is used for retrieving Enterprise Roles from an enterprise directory service.
Syntax: rdbms_server_dn=<X.500 Distinguished Name>
TBD
REDO_TRANSPORT_USER

No Default
Specifies the name of the user whose password verifier is used when a remote login password file is used for redo transport authentication. This user must have the SYSDBA or SYSOPER privilege and must have the same password in the database that initiates the redo transport session and in the database that is the target of the redo transport session.
redo_transport_user=uwdba
REMOTE_DEPENDENCIES_MODE

Default: TIMESTAMP
Specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.
Range of values: {SIGNATURE | TRANSPORT}
remote_dependencies_mode=TIMESTAMP
REMOTE_LISTENER

No Default
Specifies a network name that resolves to an address or address list of Oracle Net remote listeners (that is, listeners that are not running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repositories.
Syntax: remote_listener=<network_name>
TBD
RESOURCE_LIMIT

Default: FALSE
Determines whether resource limits are enforced in database profiles.
resource_limit=TRUE
RESOURCE_MANAGER_CPU_ALLOCATION Deprecated
RESOURCE_MANAGER_PLAN

No Default
Specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.
resource_manager_plan='SCHEDULER[0x2C58]:DEFAULT_MAINTENANCE_PLAN'
RESUMABLE_TIMEOUTS

Default: 0 seconds
Enables or disables resumable statements and specifies resumable timeout at the system level.
Range of Values: {0 to 2^31 - 1 (in seconds)}
resumable_timeouts=6000
ROLLBACK_SEGMENTS

Default: No specified as UNDO is used
Allocates one or more rollback segments by name to this instance. If you set this parameter, the instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated as TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
Range of Values: {Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM}
TBD
SERIAL_REUSE Deprecated
SERVICE_NAMES

Default:
DB_UNIQUE_NAME.DB_DOMAIN if defined
Specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.
service_name=orabase
SESSIONS

Default: Derived: (1.1 * PROCESSES) + 5
Specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.
sessions=200
SKIP_UNUSABLE_INDEXES

Default: TRUE
Enables or disables the use and reporting of tables with unusable indexes or index partitions. If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result.
skip_unusable_indexes=TRUE
SMTP_OUT_SERVER

No Default
Specifies the SMTP host and port to which UTL_MAIL delivers out-bound E-mail. Multiple servers may be specified, separated by commas.
Syntax: SMTP_OUT_SERVER = server_clause [, server_clause ] ...
smtp_out_server=smtp.drizzle.com

Equivalent to:
ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;
SQL_TRACE Deprecated
SQLTUNE_CATEGORY

Default: DEFAULT
Specifies the category name for use by sessions to qualify the lookup of SQL profiles during SQL compilation.
sqltune_category=DEFAULT
SQL_TRANSFORMATION_ENABLED

Default: FALSE
Determines whether a cost-based query transformation will be applied to star queries.
Range of values: {FALSE | TRUE | TEMP_DISABLE}
star_transformation_enabled=TRUE
WORKAREA_SIZE_POLICY

Default: AUTO
Specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.
Range of values: {AUTO | MANUAL}
workarea_size_policy=AUTO
XML_DB_EVENTS

Default: ENABLE
Enables or disables XML DB Events.
Range of values: {ENABLE | DISABLE}
xml_db_events=ENABLE
 
Network & Connections Parameters
CIRCUITS

No Default
Specifies the total number of virtual circuits that are available for inbound and outbound network sessions. It is one of several parameters that contribute to the total SGA requirements of an instance. Do not specify a value unless you want to limit the number of virtual circuits.
circuits=2
DISPATCHERS

Default: If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to '(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
Configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner.

Range of values:
dispatch_clause::= (PROTOCOL = protocol) | (ADDRESS = address) | (DESCRIPTION = description )
options_clause::=(DISPATCHERS = integer | SESSIONS = integer | CONNECTIONS = integer |
TICKS = seconds | POOL = {1 | ON | YES | TRUE | BOTH | ({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} |
MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} | LISTENER = tnsname | SERVICE = service | INDEX = integer)
dispatchers='(PROTOCOL=TCP) (SERVICE=orabaseXDB)'
dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)'
LICENSE_MAX_SESSIONS

Default: 0
Specifies the maximum number of concurrent user sessions allowed. When this limit is reached, only users with the RESTRICTED SESSION privilege can connect to the database. Users who are not able to connect receive a warning message indicating that the system has reached maximum capacity.
license_max_sessions=0
LICENSE_MAX_USERS

Default: 0
Specifies the maximum number of users you can create in the database. When you reach this limit, you cannot create more users. You can, however, increase the limit. Do not enable both concurrent usage (session) licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.
license_max_users=0
LICENSE_SESSIONS_WARNING

Default: 0
Specifies a warning limit on the number of concurrent user sessions. When this limit is reached, additional users can connect, but Oracle writes a message in the alert file for each new connection. Users with RESTRICTED SESSION privilege who connect after the limit is reached receive a warning message stating that the system is nearing its maximum capacity.
license_sessions_warning=0
LISTENER_NETWORKS

No Default
Specifies one or more sets of local & remote listeners for cross-registration. All listeners within the same network_name will cross-register.
Syntax: LISTENER_NETWORKS =
'((NAME=network_name) (LOCAL_LISTENER=["]listener_address[,...]["]) [(REMOTE_LISTENER=["]listener_address[,...]["])])' [,...]
local_listener=listenerdev
LOCAL_LISTENER

Default: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
Specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.
Syntax: LOCAL_LISTENER = <network_name>
local_listener=(PROTOCOL=TCP)(HOST=bigdog.mlog.org)(PORT=1521))
MAX_DISPATCHERS

No Default
Specifies the maximum number of dispatcher processes allowed to be running simultaneously. It can be overridden by the DISPATCHERS parameter and is maintained for backward compatibility with older releases.
max_dispatchers=10
MAX_SHARED_SERVERS

No Default
Specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.
max_shared_servers=10
SHARED_SERVER_SESSIONS

No Default
Specifies the number of shared server sessions to allow. Setting this parameter enables reserving user sessions for dedicated servers.
shared_server_sessions=60
SHARED_SERVERS

Default: 0 (not enabled)
Specifies the number of server processes that you want to create when an instance is started. If system load decreases, then this minimum number of servers is maintained. Therefore, you should take care not to set SHARED_SERVERS too high at system startup.
shared_servers=10
 
NLS Parameters
NLS_CALENDAR

No Default
Specifies which calendar system Oracle uses
Range of values {Arabic Hijrah | English Hijrah | Gregorian | Japanese Imperial | Persian | ROC Official (Republic of China) | Thai Buddha}
TBD
NLS_COMP

Default: BINARY
Specifies which calendar system Oracle uses
Range of values: {BINARY | LINGUISTIC | ANSI}
nls_comp=BINARY
NLS_CURRENCY Specifies the string to use as the local currency symbol for the L number format element. The default value of this parameter is determined by NLS_TERRITORY.
Range of values: Any valid character string, with a maximum of 10 bytes (not including null)
nls_currency=$
NLS_DATE_FORMAT Specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.
nls_date_format="MM/DD/YYYY"
NLS_DATE_LANGUAGE Specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.
nls_date_language=AMERICAN
NLS_DUAL_CURRENCY Specifies the dual currency symbol (such as "Euro") for the territory. The default is the dual currency symbol defined in the territory of your current language environment.
TBD
NLS_ISO_CURRENCY Specifies the string to use as the international currency symbol for the C number format element.
TBD
NLS_LANGUAGE Specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.
nls_language=AMERICAN
NLS_LENGTH_SEMANTICS Enables creationof CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
nls_length_semantics=BYTE
NLS_NCHAR_CONV_EXCP

Default: FALSE
Determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR/NVARCHAR and CHAR/VARCHAR2. The default value results in no error being reported.
nls_nchar_conv_excp=TRUE
NLS_NUMERIC_CHARACTERS Specifies the characters to use as the group separator and decimal character. It overrides those characters defined implicitly by NLS_TERRITORY. The group separator separates integer groups (that is, thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.
nls_numeric_characters=", "
NLS_SORT Specifies the collating sequence for ORDER BY queries.
Range of values: {BINARY | linguistic_definition}
nls_sort=BINARY
NLS_TERRITORY Specifies the name of the territory whose conventions are to be followed for day and week numbering.
nls_territory=AMERICA
NLS_TIMESTAMP_FORMAT Defines the default timestamp format to use with the TO_CHAR and TO_TIMESTAMP functions.
nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF'
NLS_TIMESTAMP_TZ_FORMAT Defines the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.
nls_timestamp_tz_format='YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
 
Optimizer
CREATE_STORED_OUTLINES

No Default
Enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, then that outline will remain and a new outline will not be created.
Syntax CREATE_STORED_OUTLINES = {TRUE | FALSE | CATEGORY_NAME} [NOOVERRIDE]
create_stored_outlines=FALSE
OPTIMIZER_ADAPTIVE_REPORTING_ONLY

Defult: FALSE
Performs Adaptive Plan analysis but does not adopt the adaptive plan.
Range of values: {TRUE | FALSE}
optimizer_adaptive_reporting_only=TRUE
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

Defult: FALSE
Enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.
optimizer_capture_sql_plan_baselines=TRUE
OPTIMIZER_DYNAMIC_SAMPLING

Default: 2
Controls the level of dynamic sampling performed by the optimizer.
Range of values: {0 - 10}
optimizer_dynamic_sampling=2
OPTIMIZER_FEATURES_ENABLE

Default: 11.2.0.2
Umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
optimizer_features_enable=11.2.0.4
OPTIMIZER_INDEX_CACHING A point that is often overlooked with optimizer_index_caching is that it supposed to apply only for index costs during nested loops (although it does seem to be applied for inlist iterators).

Consequently it is not really a measure of what fraction of an index is cached, it is a measure of how many rows I
select from a driving table that require me to collect the same data from the inner table repeatedly.

For example:
If I select 5 rows from table A and then join to table B, it is possible that I will do a physical read for the index blocks for tableB after reading the first row from table A - but then use the same index blocks for the join to the next 4 rows from table A.

In this case, an optimizer_index_caching value of 80% would be the representative value.

by: Jonathan Lewis / 4 Nov 2004

A relevant metalink note (62286.1) would suggest that Oracle intend the
value to be considered for in-list as well as NL.

by: Niall Litchfield
optimizer_index_caching=20
OPTIMIZER_INDEX_COST_ADJ

Default: 100 (%)
The default instructs the optimizer to use its default index use costing. A lower value makes the cost of index usage lower and a larger value makes the cost of index usage appear more expensive.
Range of values 1 to 10000
optimizer_index_cost_adj=90
OPTIMIZER_MODE

Default: ALL_ROWS
The default behavior of the instance's optimization approach
Range of values: {first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows}
optimizer_mode='ALL_ROWS'
OPTIMIZER_SECURE_VIEW_MERGING

Default: TRUE
Enables the optimizer to use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of a view's creator.
Range of values: {TRUE | FALSE}
optimizer_secure_view_merging=TRUE
OPTIMIZER_USE_INVISIBLE_INDEXES

Default: FALSE
Enables or disables the use of invisible indexes by the CBO
Range of values: TRUE FALSE
optimizer_use_invisible_indexes=FALSE
OPTIMIZER_USE_PENDING_STATISTICS

Default: FALSE
Specifies if the optimizer can use pending statistics when compiling SQL statements
Range of values: {TRUE | FALSE}
optimizer_use_pending_statistics=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES

Default: TRUE
Enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Range of values: {TRUE | FALSE}
optimizer_use_sql_plan_baselines=TRUE
PLSQL_OPTIMIZE_LEVEL

Default: 2
Specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Range of values: 0 - 3
plsql_optimize_level=3
QUERY_REWRITE_ENABLED

Default: TRUE
Allows you to enable or disable query rewriting globally for the database.
Range of values: {FALSE | FORCE | TRUE}
query_rewrite_enabled=TRUE
QUERY_REWRITE_INTEGRITY

Default: ENFORCED
Determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.
Range of values: {ENFORCED |  STALE _TOLERATED | TRUSTED}
query_rewrite_integrity='TRUSTED'
 
PL/SQL
PLSCOPE_SETTINGS

Default: IDENTIFIERS:NONE
Controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data.
Range of values {IDENTIFIERS:NONE | IDENTIFIERS:ALL}
plscope_settings=IDENTIFIERS:ALL

equivalent to:
ALTER SYSTEM SET plscope_settings = 'IDENTIFIERS:ALL' scope=spfile;
PLSQL_CCFLAGS

Default: empty string
Provides a mechanism that allows PL/SQL programmers to control conditional compilation of each PL/SQL library unit independently.
plsql_ccflags='DEBUG:TRUE'

equivalent to:
ALTER SESSION SET plsql_ccflags = 'DEBUG:TRUE';
PLSQL_CODE_TYPE

Default: INTERPRETED
Specifies the compilation mode for PL/SQL library units.
plsql_code_type=NATIVE

equivalent to:
ALTER SYSTEM SET plsql_code_type = 'NATIVE' SCOPE=BOTH;
PLSQL_DEBUG

Default: FALSE
Specifies whether or not PL/SQL library units will be compiled for debugging.
plsql_debug=FALSE
PLSQL_WARNINGS

Default: DISABLE:ALL
Enables or disables feedback on Critical, Informational, and Performance warnings during PL/SQL object compilation.
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
 
Real Application Clusters
ACTIVE_INSTANCE_COUNT Deprecated
CLUSTER_DATABASE

Default: FALSE
Specifies whether or not Real Application Clusters is enabled.
Range of values: {TRUE | FALSE}
cluster_database=TRUE
CLUSTER_DATABASE_INSTANCES

Default: 1
Specifies the number of instances that are configured as part of a RAC database.
Range of values Any nonzero value
cluster_database_instances=4
CLUSTER_INTERCONNECTS

Default: NULL
Range of values One or more IP addresses, separated by colons. Provides information about additional cluster interconnects available for use in a RAC environments.

This parameter can be used to override the default interconnect with a preferred cluster traffic network. This parameter is useful in Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands. You can also use CLUSTER_INTERCONNECTS to override the default interconnect chosen by Oracle.

For example, if you are running two instances of Oracle for two databases on the same machine, then you can load balance the interconnect traffic to different physical interconnects. This does not reduce Oracle availability.
cluster_interconnects=10.2.1.1:10.2.1.2:10.2.1.3:10.2.1.4
GCS_SERVER_PROCESSES

Default depends on CPUs and whether stand-alone, ASM, or RAC
The number of background GCS server processes (LMS0, ... LMS9 and LMSa, ... LMSz) to serve inter-instance traffic among Oracle RAC instances. GCS server processes are only seen in an Oracle RAC environment.
Range of values: 0 - 36
gcs_server_processes=16
INSTANCE_GROUPS Deprecated
INSTANCE_NAME

Default: The instance's SID
In a Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance.
instance_name=orabase
INSTANCE_NUMBER Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for Real Application Clusters, then 0.

INSTANCE_NUMBER is a Real Application Clusters parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.

The INSTANCE parameter of the ALTER TABLE ... ALLOCATE EXTENT statement assigns an extent to a particular free list group. If you set INSTANCE_NUMBER to the value specified for the INSTANCE parameter, the instance uses that extent for inserts and for updates that expand rows.

The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement. The absolute maximum is operating system-dependent.
instance_number=0
INSTANCE_TYPE

Default: 0
An Oracle RAC parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.
instance_type=RDBMS
PARALLEL_ADAPTIVE_MULTI_USER

Default: TRUE
When set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
Range of values: {TRUE | FALSE}
parallel_adaptive_multi_user=TRUE
PARALLEL_DEGREE_LIMIT

Default: CPU
With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. The limit is enforced by PARALLEL_DEGREE_LIMIT.
Range of values: {CPU | IO | <integer>}
parallel_degree_limit=CPU
PARALLEL_DEGREE_POLICY

new in 11.2

DEFAULT: MANUAL
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution but that cannot obtain the necessary parallel servers processes due to a lack of system resources. Once the necessary resources become available the SQL statement will be dequeued and allowed to execute. The queue is a simple First In First Out queue based on the time a SQL statement was executed.

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED, Oracle will also decide if an object that will be accessed using parallel execution would benefit from being cached in the SGA (buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In a RAC environment Oracle will affinitize or map piece of the object into each of the buffer caches on the active instances by fileNumber and extentNumber unless the object is hash partitioned and the number of hash partitions is larger than the number of nodes. By creating this mapping, Oracle will automatically know which buffer cache to access to find different parts or partitions of the object. Using this information Oracle will prevent multiple instances from reading the same information from disk over and over again. If the size of the object is larger than the size of the buffer cache or the size of the buffer cache multiplied by the number of active instances in a RAC cluster than it will be read using direct path reads.
Syntax PARALLEL_DEGREE_POLICY = {MANUAL | LIMITED | AUTO}
parallel_degree_limit=AUTO
PARALLEL_EXECUTION_MESSAGE_SIZE

Default: Operating System and Compatible Parameter value dependent
The size of messages used for parallel execution (formerly parallel query, PDML, Parallel Recovery, replication).
Range of values : 2148 to 32768
parallel_execution_message_size=16384
PARALLEL_FORCE_LOCAL

Default: FALSE
On a RAC cluster forces parallel query slaves to stay on the local instance and not parallelize across nodes increasing interconnect traffic.
Range of values: {TRUE | FALSE}
parallel_force_local=TRUE
PARALLEL_INSTANCE_GROUP

No Default
PARALLEL_INSTANCE_GROUP = group_name

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. Parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
parallel_instance_group='SYS$USERS'
PARALLEL_IO_CAP_ENABLED Deprecated
PARALLEL_MAX_SERVERS

Default: See explanation at right
Default is (PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5)
Range of values: 0 to 3600
parallel_max_servers=10
PARALLEL_MIN_PERCENT

Default: 0
Range of values: 0 to 109
parallel_min_percent=0
PARALLEL_MIN_SERVERS
Default: See explanation at right
Default is (CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2)
Range of values Default value to the value of PARALLEL_MAX_SERVERS
parallel_min_servers=0
PARALLEL_MIN_TIME_THRESHOLD
Default: AUTO
Range of values: {AUTO | integer}
parallel_min_time_threshold=AUTO
PARALLEL_SERVER Deprecated
PARALLEL_SERVER_INSTANCES Deprecated
PARALLEL_SERVERS_TARGET

Default: See explanation at right
Default is (PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2)
Range of values: 0 to PARALLEL_MAX_SERVERS
parallel_servers_target=4
PARALLEL_THREADS_PER_CPU

Default: O/S dependent but usually 2
Any non-zero integer.
parallel_threads_per_cpu=4
THREAD

Default:0
THREAD has been superseded by the INSTANCE_NAME and INSTANCE_NUMBER parameters, and will be made obsolete in a future release of the Oracle Database.
thread=0
 
Result Cache
CLIENT_RESULT_CACHE_LAG

Default: 3000
Specifies the maximum time (in milliseconds) since the last round trip to the server, before which the OCI client query execute makes a round trip to get any database changes related to the queries cached on the client.
client_result_cache_lag=3000
CLIENT_RESULT_CACHE_SIZE

Default: 0
Specifies the maximum size of the client per-process result set cache (in bytes). All OCI client processes inherit this maximum size. Setting a nonzero value enables the client query cache feature. This can be overridden by the client configuration parameter OCI_RESULT_CACHE_MAX_SIZE.
Range of values: {0 to operating system dependent}
client_result_cache_size=0
RESULT_CACHE_MAX_RESULT

Default: 5%
Specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use.
result_cache_max_result=7
RESULT_CACHE_MAX_SIZE

Default: Derived
Specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled.
result_cache_max_size=2129920
RESULT_CACHE_MODE

Default: MANUAL
Specifies when a ResultCache operator is spliced into a query's execution plan.
Range of Values: {MANUAL | FORCE}
result_cache_mode=MANUAL
RESULT_CACHE_REMOTE_EXPIRATION

Default: 0
Specifies the number of minutes that a result using a remote object is allowed to remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers (for example, if the remote table used by a result is modified at the remote database).
result_cache_remote_expiration=0
 
Security
O7_DICTIONARY_ACCESSIBILITY

Default: FALSE
Version 7 Dictionary Accessibility support. Range of values TRUE or FALSE
07_dictionary_accessibility=FALSE
DB_ULTRA_SAFE

Default: OFF
Sets the default values for other parameters that control protection levels.
Range of values: {OFF | DATA_ONLY | DATA_AND_INDEX}
db_ultra_safe=OFF
LDAP_DIRECTORY_ACCESS

Default: NONE
Specifies whether Oracle refers to Oracle Internet Directory for user authentication information. If directory access is turned on, then this parameter also specifies how users are authenticated.
Range of values: {NONE | PASSWORD | SSL}
ldap_directory_access=NONE
LDAP_DIRECTORY_SYSAUTH
Default: NO
Enables or disables directory-based authorization for SYSDBA and SYSOPER.
ldap_directory_sysauth=NO
OS_ROLES

Default: FALSE
Determines whether Oracle or the operating system identifies and manages the roles of each username.
os_rules=FALSE
REMOTE_LOGIN_PASSWORD_FILE

Default: EXCLUSIVE
Specifies whether Oracle checks for a password file.
Range of Values: {SHARED | EXCLUSIVE | NONE}
remote_login_passwordfile='EXCLUSIVE'
REMOTE_OS_AUTHENT Deprecated
REMOTE_OS_ROLES

Default:FALSE
Specifies whether operating system roles are allowed for remote clients. The default value, false, causes Oracle to identify and manage roles for remote clients.
remote_os_rules=FALSE
SEC_CASE_SENSITIVE_LOGON Deprecated
SEC_MAX_FAILED_LOGIN_ATTEMPTS

Default: 10
Specifies the number of authentication attempts that can be made by a client on a connection to the server process. After the specified number of failure attempts, the connection will be automatically dropped by the server process.
sec_max_failed_login_attempts=3
SEC_PROTOCOL_ERROR_FURTHER_ACTION

Default: CONTINUE
Specifies the further execution of a server process when receiving bad packets from a possibly malicious client.
Range of Values: {CONTINUE | DELAY <integer> | DROP <integer>}
sec_protocol_error_further_action=CONTINUE
SEC_PROTOCOL_ERROR_TRACE_ACTION

Default: TRACE
Specifies the action that the database should take when bad packets are received from a possibly malicious client.
Range of Values: {NONE | TRACE | LOG | ALERT}
sec_protocol_eror_trace_action=TRACE
SEC_RETURN_SERVER_RELEASE_BANNER

Default: FALSE
Specifies whether or not the server returns complete database software information to clients.
sec_return_server_release_banner=FALSE
SQL92_SECURITY

Default: FALSE
The SQL standard specifies that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.
sql92_security=FALSE
 
Transactions
COMMIT_WAIT

No Default
An advanced parameter used to control when the redo for a commit is flushed to the redo logs.
Range of values: {NOWAIT | WAIT | FORCE_WAIT}
commit_wait=NOWAIT
COMMIT_WRITE Deprecated
DML_LOCKS Default value Derived: 4 * TRANSACTIONS. Specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
Range of values 20 to unlimited; a setting of 0 disables enqueues
dml_locks=748
FAST_START_PARALLEL_ROLLBACK Determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running
Range of values {HI | LO | FALSE}
fast_start_parallel_rollback=LOW
GLOBAL_TXN_PROCESSES

Default: 1
Specifies the initial number of GTXn background processes (GTX0, ... GTX9 and GTXa, ... GTXj) per instance to support global (XA) transactions in an Oracle RAC environment.
Range of values:{0 to 20}
global_txn_processes=3
TRANSACTIONS

Default: Derived: (1.1 * SESSIONS)
Specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.
Range of values: {4 to 2^32}
transactions=187
TRANSACTIONS_PER_ROLLBACK _SEGMENT

Default 5
Specifies the number of concurrent transactions you expect each rollback segment to have to handle. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.
transactions_per_rollback_segment=6
 
Undo Tablespace
UNDO_MANAGEMENT

Default: AUTO
Specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.
Range of values: {MANUAL | AUTO}
undo_management=AUTO
UNDO_RETENTION

Default: 900
Specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
Range of values: 0 to 2^31-1
undo_retention=1440
UNDO_TABLESPAC E

No Default
Name of the undo tablespace to be used during startup.
undo_tablespace='UNDOTBS1'
 
Undocumented
Query for undocumented initialization parameters set pagesize 35
set linesize 150
col NAME format a40
col VALUE format a20
col DESCRIPTION format a60
set pause on
set pause 'Hit enter to continue'

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
ORDER BY 1;
_arch_comp_dbg_scan

Default: 0
archive compression scan debug
 

Related Topics
DBMS_WORKLOAD_REPOSITORY
Init.ora & SPFILE
Synonyms

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2016 Daniel A. Morgan All Rights Reserved