As a Oracle DBA, when we install Oracle binaries; we have to take care of a number of prerequisites. One of such prerequisite is to set Kernel Parameters to the Oracle recommended values.
However, most of the time we are not bothered about; what actually these kernel parameters do and why is the requirement to reset it to the Oracle recommended values.
In today’s post, I would try to provide a brief overview of the Kernel parameters related to the Oracle installation and the role they play with respect to the Oracle database.
Kernel Parameters related to Oracle installation:
Based on functionality of the parameters, I have segreggated the parameters into the following list
1. Semaphore Parameters:
2. Shared Memory Parameters:
3. File Handling Parameters
4. Port Range Parameters
5. Socket Size Parameters
To understand the functioning of the parameters, we have to first understand two important aspects of an Operating System called Shared Memory and Semaphore.
Shared Memory: A shared memory is a region or part of the physical memory (RAM), that can be accessed or shared by multiple processes on the Operating System (server).
Oracle uses Shared Memory for SGA (Shared Global Area) allocation.
Semaphore: A Semaphore is a controlling signal or flag that can be either turned On or Off by a OS process. Semaphore controlls the access to Shared OS resources. If a signal is already turned On , process which tries to turned On the same signal has to wait and sleep until it is turned Off. On awakening, the process can re-attempt to turn On the signal and can eventually succeed or wait depending on the state of the signal. Each OS process is associated with a Semaphore.
Oracle uses Semaphores to control concurrent access to SGA since it can be shared (writable) by the attached database processes.
Explanation and Impact on Oracle Database:
SHMMAX: Specifies the maximum allowable size of a single shared memory segment (in bytes)
SHMMIN: Specifies the minimum size of a single shared memory segment (in bytes)
SHMMNI: Specifies the maximum number of shared memory segments on system (in numbers)
SHMALL: Specifies the sum of all shared memory segments in the system (in pages)
SHMSEG: Specifies the maximum number of shared memory segments that can be attached (i.e. used) by a single process.
SHMMNS: Specifies the amount of shared memory that can be allocated system-wide.
SHMMAX and SHMALL are the two important kernel parameters which affects a Oracle Database instance.
When a Oracle instance starts up, Shared Memory (SGA) is allocated to the instance from the available System Shared Memory segments. Oracle can use any of the forth mentioned methods for the Shared Memory allocation to the Instance.
By default, Oracle uses one-segment allocation method and checks if a single Shared Memory segment can fit the SGA requirement by checking the size of SHMMAX kernel parameter and comparing it with SGA_TARGET. If “SHMMAX> SGA_TARGET”, Oracle uses the one-segment method for SGA allocation.
If one-segment allocation is not feasible, Oracle goes for the contiguous multi-segment allocation, where it tries to allocate contiguous Shared Memory segments to fulfill SGA requirement.
If Oracle can’t find a contiguous set of segments, it finally uses the non contiguous multi-segment allocation, where tries to allocate scattered Shared Memory segments to fulfill SGA requirement.
If sufficient Shared Memory segment (s) is not available, Oracle will throw “Out Of Memory” errors like below
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
While using oracle, it is recommended to allocate half of the size of RAM (.5 * RAM Size) to SHMMAX i.e.
SHMMAX= Size of Physical Memory (RAM) in bytes/ 2
If the available RAM on the server is 30 GB, it is recommended to set shmmax to 15 GB i.e 16106127360 bytes
SHMALL should be set as the Sum of SGA from all the Oracle Instances on the system devided by OS page size i.e.
SHMALL = SUM(all SGA) in bytes / OS page_size
If the size of the SGA from all of the Oracle Instances on a particular server 20 GB, then shmall should be configured as follows
a. Find the OS PAGE_SIZE
[oracle@labserver ~]$ getconf PAGE_SIZE
b. Determine shmall using above mentioned formula
shmall = (20*1024*1024*1024)/4096=5242880 (pages)
kernel.shmall = 5242880
We can query the details of Shared Memory segments as follows
[oracle@labserver ~]$ ipcs -m -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1
The status of allocated shared memory segments can be queried as follows
[oracle@labserver ~]$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 3506176 root 644 80 2
0x00000000 3538945 root 644 16384 2
0x00000000 3571714 root 644 280 2
0x54917120 8126468 oracle 640 4096 0
0x00000000 8159237 oracle 640 4096 0
0x5f600208 15106054 oracle 640 14680064 100
0x00000000 15138823 oracle 640 511705088 50
Where bytes: Size of the allocated Shared Memory segment and
nattache: Number of processes attached to the respective Shared Memory segment
Note: In any case the Shared Memory Parameters should not exceed the physical memory (RAM) size. Setting the values larger than available RAM may lead to errors.
SEMMSL : Specifies the maximum number of semaphores in a semaphore set
SEMMNS : Specifies the maximum number of semaphores available on the system.
SEMOPMN : Specifies the maximum number of operations in a single semaphore call
SEMMNI : Specifies the maximum number of semaphore sets available in the system
SEMVMX : specifies the maximum value a semaphore set can have
SEMMSL and SEMMNS are the two important Semaphore parameter that affect the Oracle database.
As we know, Semaphores are OS signals that control shared access; each database process must be associated with a Semaphore.
When a Oracle instance starts up Oracle allocates semaphores to all the PROCESSES (parameter file values) associated with that instance. Semaphore gets allocated in sets rather than as an individual one.
We can allocate all the semaphores to the database processes in single or multiple sets. If SEMMSL=PROCESSES, then Semaphores can be allocated in a single set to that particular database instance.
If the server is hosting multiple Oracle Instances, it is recommended to set the value of SEMMSL to the lowest value of PROCESSES from all the Instances. In this way, we can prevent the Semaphore being in the unused status.
If the server has 3 databases namely LABPA, LABPABR and LABCP; each having number of processes as 100 , 200 and 300 respectively.
If we set the value of SEMMSL=300, then each of the Database Instance would be allocated 300 Semaphores (as Semaphores get allocated in sets).
However, LABPA and LABPABR would be utilizing only 100 and 200 Semaphores respectively.
Therefore, we will have 200 and 100 unused Semaphores from LABPA and LABPABR instances respectively. These unused Semaphores can not be allocated to other processes.
This is why, it is recommended to set the value of SEMMSL to the lowest value of PROCESSES among all the Oracle Instances on a particular server.
In our example, we must set SEMMSL=100. In this case, it will allocate 1 (100 Semaphores), 2(200 Semaphores) and 3(300 Semaphores) sets of Semaphores to LABPA, LABPABR and LABCP Oracle Instances respectively.
The maximum number of semaphores that can be allocated will be the lesser than (SEMMSL*SEMMNI) or SEMMNS.
Current Semaphore configuration can be checked as follows
[oracle@labserver ~]$ ipcs -s -l
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767
Current allocation of Semaphores can be queried as
[oracle@labserver ~]$ ipcs -s
------ Semaphore Arrays --------
key semid owner perms nsems
0x57e20798 131073 oracle 640 124
0x51e15128 262146 oracle 640 152
0x51e15129 294915 oracle 640 152
0x51e1512a 327684 oracle 640 152
Where nsems : Number of Semaphores allocated in the respective set
Other Kernel Parameters:
file-max : Specifies the maximum of number of file handles available on the system. This number indicates the number of files that can be opened on the server at any given time
ip_local_port_range : Specifies the range of local ports on the system that can be used by TCP and UDP outgoing connections.
While specifying the IP range, you need skip the range of the ports that would be used as a various Listener ports like HTTP, FTP, Database Listener.
rmem_default: Specifies default OS receive buffer size for all types of connections. This is the default size of a network data packet that the system will receive.
rmem_max: Specifies max OS receive buffer size for all types of connections. This is the maximum size of a network data packet that the system can receive
wmem_default: Specifies default OS send buffer size for all types of connections. This is the default size of a network data packet that the system will send.
wmem_max: Specifies max OS send buffer size for all types of connections. This is the maximum size of a network data packet that the system can send.
The rmem and wmem parameters can be tuned and increased to handle large file transfer between servers.
Note: To make kernel paraameter changes permanent, system reboot is required