Oracle Database Initialization Parameters

 Initialization parameters

      
             Click on the below link to know about the Oracle Database Initialization parameters   

Database Initialization Parameters

Some of the important parameters and their definitions:

Parameter Name	Purpose
audit_file_dest	AUDIT_FILE_DEST specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.
audit_trail	Enables or disables database auditing. Values: •none or false: Disables database auditing. •os: Enables database auditing and directs all audit records to the operating system's audit trail. •db or true: Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). •db_extended: Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table. The SQL AUDIT statements can set auditing options regardless of the setting of this parameter.
compatible	COMPATIBLE allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release. This parameter specifies the release with which the Oracle server must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the current release may be restricted. When using a standby database, this parameter must have the same value on the primary and standby databases.
control_files	CONTROL_FILES specifies one or more names of control files, separated by commas.
db_block_size	DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192. The value of this parameter must be a multiple of the physical block size at the device level. The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks. The value must remain set to its initial value. In Oracle Database 10g, the default value of DB_BLOCK_SIZE is operating system specific, but is typically 8 KB (8192 bytes). In previous Oracle Database releases, the default value was 2 KB (2048 bytes). If DB_BLOCK_SIZE is not specified in the parameter file when upgrading to the new Oracle Database 10g release, then you will receive an error when attempting to start up your Oracle Database. Add the following to your parameter file: DB_BLOCK_SIZE = 2048If DB_BLOCK_SIZE is specified in the parameter file, then the Oracle Database will use this value instead of the default value of 8 KB.
db_domain	specifies the logical location of the database within the network structure
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. The following characters are valid in a database name: alphanumeric characters, underscore (_), number sign (#), and dollar sign ($). No other characters are valid.
diagnostic_dest	As of Oracle Database 11g Release 1, the diagnostics for each database instance are located in a dedicated directory, which can be specified through the DIAGNOSTIC_DEST initialization parameter. The structure of the directory specified by DIAGNOSTIC_DEST is as follows: <diagnostic_dest>/diag/rdbms/<dbname>/<instname>This location is known as the Automatic Diagnostic Repository (ADR) Home. For example, if the database name is proddb and the instance name is proddb1, the ADR home directory would be <diagnostic_dest>/diag/rdbms/proddb/proddb1. The following files are located under the ADR home directory: •Trace files - located in subdirectory <adr-home>/trace   •Alert logs - located in subdirectory <adr-home>/alert. In addition, the alert.log file is now in XML format, which conforms to the Oracle ARB logging standard.   •Core files - located in the subdirectory <adr-home>/cdumd   •Incident files - the occurrence of each serious error (for example, ORA-600, ORA-1578, ORA-7445) causes an incident to be created. Each incident is assigned an ID and dumping for each incident (error stack, call stack, block dumps, and so on) is stored in its own file, separated from process trace files. Incident dump files are located in <adr-home>/incident/<incdir#>. You can find the incident dump file location inside the process trace file.
memory_target	MEMORY_TARGET 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. In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
open_cursors	OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.
processes	PROCESSES 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.
remote_login_passwordfile	Specifies whether Oracle checks for a password file and how many databases can use the password file. Setting the parameter to NONE signifies that Oracle should ignore any password file (and therefore privileged users must be authenticated by the operating system). Setting the parameter to EXCLUSIVE signifies that the password file can only be used by one database and the password file can contain names other than SYS and INTERNAL. Setting the parameter to SHARED allows more than one database to use a password file; however, the only users recognized by the password file are SYS and INTERNAL.
service_names	SERVICE_NAMES specifies the service names supported by the instance.  SERVICE_NAMES is one or more strings which represent the names of the database on the network. It is possible to provide multiple services names so that different usages of a single database can be identified separately. Service names can also be used to identify a single service that is available from two different databases through the use of replication
undo_tablespace	UNDO_TABLESPACE specifies the undo tablespace to be used when an instance starts up. If this parameter is specified when the instance is in manual undo management mode, an error will occur and startup will fail.  If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in the database is chosen. If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal circumstances.  You can replace an undo tablespace with another undo tablespace while the instance is running.
O7_DICTIONARY_ACCESSIBILITY	O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in the SYS schema. For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema. If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects: •SELECT_CATALOG_ROLE •EXECUTE_CATALOG_ROLE •DELETE_CATALOG_ROLE
SGA_TARGET	SGA_TARGET 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) If these automatically tuned memory pools are set to non-zero 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 •Streams pool •Fixed SGA and other internal allocations The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
SPFILE	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.  When the default server parameter file is used by the server, the value of SPFILE is internally set by the server.  The SPFILE resides in the ORACLE_HOMEdbs directory; however, users can place it anywhere on their machine as long as it is specified in a PFILE.
STATISTICS_LEVEL	STATISTICS_LEVELspecifies 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. The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments. When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting.  The additional statistics are timed OS statistics and plan execution statistics. Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including: •Automatic Workload Repository (AWR) Snapshots •Automatic Database Diagnostic Monitor (ADDM) •All server-generated alerts •Automatic SGA Memory Management •Automatic optimizer statistics collection •Object level statistics •End to End Application Tracing (V$CLIENT_STATS) •Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL) •Service level statistics •Buffer cache advisory •MTTR advisory •Shared pool sizing advisory •Segment level statistics •PGA Target advisory •Timed statistics •Monitoring of statistics Note: Oracle strongly recommends that you do not disable these important features and functionality. When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL.
UNDO_MANAGEMENT	UNDO_MANAGEMENT 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.
UNDO_RETENTION	UNDO_RETENTION specifies (in seconds) the amount of committed undo information to retain in the database. You can use UNDO_RETENTION to satisfy queries that require old undo information to rollback changes to produce older images of data blocks. You can set the value at instance startup.  The UNDO_RETENTION parameter works best if the current undo tablespace has enough space for the active transactions. If an active transaction needs undo space and the undo tablespace does not have any free space, then the system will start reusing undo space that would have been retained. This may cause long queries to fail. Be sure to allocate enough space in the undo tablespace to satisfy the space requirement for the current setting of this parameter.
UNDO_TABLESPACE	UNDO_TABLESPACE specifies the undo tablespace to be used when an instance starts up. If this parameter is specified when the instance is in manual undo management mode, an error will occur and startup will fail.  If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in the database is chosen. If no undo tablespace is available, the instance will start without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal circumstances.  You can replace an undo tablespace with another undo tablespace while the instance is running.
UTL_FILE_DIR	Used by the UTL_FILE package. Defines the path to where files are located. If set to "*" (asterisk), then any directory that Oracle has access to may be used.