Beginners Guide to Oracle Database Initialization Parameters

About Initialization Parameters

Managing an Oracle instance includes configuring parameters that affect the basic operation of the Oracle instance; these parameters are called initialization parameters. The Oracle instance reads initialization parameters from a file at startup. During installation, when you select a preconfigured database workload available in DBCA, the initialization parameters are optimized for typical use in the environment that you specified. After being read from a file, initialization parameters are retained in memory, where the values for many of them can be changed dynamically. There are two types of parameter files. The type of file used to start the instance determines if dynamic initialization parameter changes persist across database shutdown and startup. The parameter file types are:

Server parameter file: which is the preferred form of the initialization parameter file, and is a binary file that can be written to and read by the database. It must not be edited manually.
Text initialization parameter file: which is a text file that can be read by the Oracle instance, but it is not written to by the instance. You can change a text initialization parameter file with a text editor, but changes do not take effect until you restart the Oracle instance.

Types of Initialization Parameters

The Oracle database server has the following types of initialization parameters:

Derived Parameters: Meaning that their values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value. For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
Operating System-Dependent Parameters: The valid values or value ranges of some initialization parameters depend upon the host operating system. For example, the parameter DB_BLOCK_BUFFERS indicates the number of data buffers in the main memory, and its maximum value depends on the operating system. The size of those buffers, set by DB_BLOCK_SIZE, has an operating system-dependent default value.
Variable Parameters: These offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance and some affect performance but do not impose absolute limits.
Dynamic Parameters: These can be changed for the current Oracle Database instance. The changes take effect immediately.
Static Parameters: These cannot be changed for the current instance. You must change these parameters in the text initialization file or server parameter file and then restart the database before changes take effect.

Basic Initialization Parameters

The following is a list of the basic database initialization parameters. Most databases should only need to have basic parameters set to run properly and efficiently. These differ from Advanced Parameters and those Underscore Parameters of which the latter is not documented in the Oracle documentation set.

CLUSTER_DATABASE: is a Real Application Clusters parameter that specifies whether or not Real Application Clusters is enabled. Parameter Type: Boolean. Default Value is True.
COMPATIBLE: allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release. Parameter Type: String. Default Value is 10.0.0 (For 10g and so on)
CONTROL_FILES: This file contains entries that describe the structure of the database (such as it’s name, the timestamp of its creation, and the names and locations of it’s datafiles and redo files). Parameter Type: String. Default Value: Operating system-dependent.
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.
DB_CREATE_FILE_DEST: specifies the default location for Oracle-managed datafiles. Parameter Type: String. There is no default value.
DB_CREATE_ONLINE_LOG_DEST_n: where n = 1, 2, 3, … 5) specifies the default location for Oracle-managed control files and online redo logs. Parameter Type: String. There is no default value.
DB_DOMAIN: specifies the logical location of the database within the network structure. Parameter Type: String. There is no default value.
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. Parameter Type: String. There is no default value.
DB_RECOVERY_FILE_DEST: specifies the default location for the flash recovery area. Parameter Type: String. There is no default value.
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 flash recovery area. Parameter Type: Big integer. There is no default value.
DB_UNIQUE_NAME: specifies a globally unique name for the database. Parameter Type: String.
INSTANCE_NUMBER: is a RAC parameter that specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS. Parameter type: Integer. Default Value: Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for RAC, then 0.
JOB_QUEUE_PROCESSES:* specifies the maximum number of processes that can be created for the execution of jobs. Parameter type: Integer. Default Value: 0.
LOG_ARCHIVE_DEST_n: defines up to ten (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. Parameter Type: String. There is no default value.
LOG_ARCHIVE_DEST_STATE_n (where n = 1, 2, 3, … 10): specifies the availability state of the corresponding destination. Parameter Type: String. Default value: enable.
NLS_LANGUAGE: specifies the default language of the database. Parameter Type: String. Default Value: Operating system-dependent, derived from the NLS_LANG environment variable.
NLS_TERRITORY: specifies the name of the territory whose conventions are to be followed for day and week numbering. Parameter Type: String. Default Value: Operating system-dependent.
OPEN_CURSORS: specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. Parameter Type: Integer. Default Value: 50.
PGA_AGGREGATE_TARGET: specifies the target aggregate PGA memory available to all server processes attached to the instance. Parameter type: Big integer. Default value: 10 MB or 20% of the size of the SGA, whichever is greater.
PROCESSES: specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Parameter type: Integer. Default value: 40 to operating system-dependent.
REMOTE_LISTENER: 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). Parameter type: String. Default value: There is no default value.
REMOTE_LOGIN_PASSWORDFILE: specifies whether Oracle checks for a password file. Parameter type String. Default value: SHARED.
SESSIONS: specifies the maximum number of sessions that can be created in the system. Parameter type: Integer. Default value Derived: (1.1 * PROCESSES) + 5.
SGA_TARGET: specifies the total size of all SGA components (Buffer cache, Shared pool, Large pool, Java pool, Streams pool). Parameter type: Big integer. Default value: 0 (SGA autotuning is disabled).
SHARED_SERVERS: specifies the number of server processes that you want to create when an instance is started. Parameter type: Integer. Default value: 0, meaning that shared server is not on. If you are using shared server architecture or if the DISPATCHERS parameter is set such that the total number of dispatchers is more than 0, then the default value is 1.
STAR_TRANSFORMATION_ENABLED: determines whether a cost-based query transformation will be applied to star queries. Parameter type: String. Default value: FALSE.
UNDO_MANAGEMENT: Specifies which undo space management mode the system should use. Parameter type: String. Default value: MANUAL.
UNDO_TABLESPACE: specifies the undo tablespace to be used when an instance starts up. Parameter type: String. Default value: The first available undo tablespace in the database.

Querying Basic Initialization Parameters

The simplest way to query a parameter is to use the “show parameter” syntax. For example:

SQL> show parameter processes

This returns all parameter values that include the partial name provided or you can specify the full name of the parameter to get a single value.

Changing Values of Basic Initialization Parameters

You can change the Basic Parameters using either the alter session command or the alter system command:

For Dynamic parameters

SQL> alter system set [parameter]=[new_value];

For Static parameters

SQL> alter system set [parameter]=[new_value] scope=spfile;

- OR -

SQL> alter session set [parameter]=[new_value];

Static Parameters only take effect after the instance is shutdown and restarted.

12c Initialization parameter when CDB PDB (multitenant) database architecture used

A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values. A CDB uses an inheritance model for initialization parameters in which PDBs inherit initialization parameter values from the root. In this case, inheritance means that the value of a particular parameter in the root applies to a particular PDB.

A PDB can override the root’s setting for some parameters, which means that a PDB has an inheritance property for each initialization parameter that is either true or false. The inheritance property is true for a parameter when the PDB inherits the root’s value for the parameter. The inheritance property is false for a parameter when the PDB does not inherit the root’s value for the parameter.

The inheritance property for some parameters must be true. For other parameters, you can change the inheritance property by running theALTER SYSTEM SET statement to set the parameter when the current container is the PDB. If ISPDB_MODIFIABLE is TRUE for an initialization parameter in the V$SYSTEM_PARAMETER view, then the inheritance property can be false for the parameter. When the current container is the root, the CONTAINER clause of the ALTER SYSTEM SET statement controls which PDBs inherit the parameter value being set.

The ALTER SYSTEM SET initialization_parameter statement can modify only some initialization parameters for PDBs. All initialization parameters can be set for the root. For any initialization parameter that is not set explicitly for a PDB, the PDB inherits the root’s parameter value.

You can modify an initialization parameter for a PDB when the ISPDB_MODIFIABLE column is TRUE for the parameter in the V$SYSTEM_PARAMETERview. The following query lists all of the initialization parameters that are modifiable for a PDB:

SQL> SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME;

When the current container is a PDB, run the ALTER SYSTEM SET initialization_parameter statement to modify the PDB. The statement does not affect the root or other PDBs. When a PDB is unplugged from a CDB, the values of the initialization parameters that were specified for the PDB with SCOPE=BOTH or SCOPE=SPFILE are added to the PDB’s XML metadata file. These values are restored for the PDB when it is plugged in to a CDB.

To make an underscore parameter persistent inside a 12c PDB, the same underscore parameter needs to be specified explicitly(with its default value) in cdb$root. Until they are specified in root, they are not available to be modified in PDBs since this is a dictionary operation for PDBs.