The following is a short guide on how to calculate the required kernel parameters for running Oracle 11g Release 2 database on Linux.
In addition to that i add some parameters recommended by myself.
Note that for running Oracle Grid Infrastrucure (aka “Clusterware”) some additional parameters might be required.
Values required by Oracle according to the documentation
The following tables list the required MINIMUM values according to the oracle documentation.
.
Parameter name in /etc/sysctl.conf Description MINIMUM value
kernel.sem "semmsl semmns semopm semmni"
kernel.sem:semmsl total semaphores for each set 250
kernel.sem:semmns total number of semaphores systemwide 32000
kernel.sem:semopm Maximum number of operations for semop call 100
kernel.sem:semmni total semaphore sets 128
kernel.shmall total number of shared memory pages that can be used system wide 2097152
kernel.shmmax the maximum size of a single shared memory segment in bytes that a linux process can allocate Either 4 GB - 1 byte, or half the size of physical memory (in bytes), whichever is lower.
Default: 536870912
kernel.shmmni system wide number of shared memory segments 4096
fs.file-max maximum number of open files system-wide 6815744
fs.aio-max-nr concurrent outstanding i/o requests 1048576
net.ip_local_port_range minimum and maximum ports for use Minimum: 9000
Maximum: 65500
net.rmem_default the default size in bytes of the receive buffer 262144
net.rmem_max the maximum size in bytes of the receive buffer 4194304
net.wmem_default the default size in bytes of the send buffer 262144
net.wmem_max the maximum size in bytes of the send buffer 1048576
Shell Limits for Oracle User
in /etc/security/limit.confDescription Hard Limit
nofile number of open files 65536
nproc number of processes 16384
stack stack size in kb 10240
/dev/shm file system largest value of MEMORY_MAX_TARGET or MEMORY_TARGET of all instances
Calculate the required values
The following table shows calculation formulas for setting the required kernel parameters for running oracle 11g release 2 on Linux.
Parameter name in /etc/sysctl.conf Description MINIMUM value Calculation
kernel.sem "semmsl semmns semopm semmni"
kernel.sem:semmsl total semaphores for each set 256 set fixed to 256
kernel.sem:semmns total number of semaphores systemwide 32000 2 * sum (process parameters of all database instances on the system)
+ overhead for background processes
+ system and other application requirements
kernel.sem:semopm Maximum number of operations for semop call 100 set fixed to 100
kernel.sem:semmni total semaphore sets 128 semmns divided by semmsl, rounded UP to nearest multiple to 1024
kernel.shmall total number of shared memory pages that can be used system wide 2097152 ceil(shmmax/PAGE_SIZE).
(PAGE_SIZE is usually 4096 bytes unless Big/Huge Pages used)
get PAGESIZE by doing:
"getconf PAGESIZE"
kernel.shmmax the maximum size of a single shared memory segment in bytes that a linux process can allocate Either 4 GB - 1 byte, or half the size of physical memory (in bytes), whichever is lower.
Default: 536870912largest value of SGA_MAX_SIZE, SGA_TARGET,MEMORY_MAX_SIZE or MEMORY_TARGET of all instances
kernel.shmmni system wide number of shared memory segments 4096 set fixed to 4096...did not find any formula
fs.file-max maximum number of open files system-wide 6815744 >= 6815744 (dont know why this large....but should be enough for most databases)
fs.aio-max-nr concurrent outstanding i/o requests 1048576 no adjustment; set fix to 1048576
net.ip_local_port_range minimum and maximum ports for use Minimum: 9000
Maximum: 65500no adjustment, fixed
net.rmem_default the default size in bytes of the receive buffer 262144 >= 262144
net.rmem_max the maximum size in bytes of the receive buffer 4194304 >= 4194304
net.wmem_default the default size in bytes of the send buffer 262144 >= 262144
net.wmem_max the maximum size in bytes of the send buffer 1048576 >= 1048576
The default page size needed for some calculations can be obtained by doing:
getconf PAGESIZE
The output states the default (not huge page size) in bytes.
The shell limits shown above should be enough initially. However if you have many data files (> 1000) i would increase the number of open files by doubling the value. All other parameters are sized big enough for almost every environment.
For using the new Automatic Memory Management Feature which automatically sized SGA and PGA Oracle uses a pseudo file system /dev/shm. If activating this feature by setting MEMORY_MAX_SIZE or MEMORY_TARGET in your init.ora you have to size /dev/shm appropriately. The following table outlines how to do it:
/dev/shm file system largest value of MEMORY_MAX_TARGET or MEMORY_TARGET of all instances
You can make this change permanent by editing your /etc/fstab. For instance the following change will set /dev/shm to a size of 13 GB:
... shmfs /dev/shm tmpfs size=13g 0 ...
Addition values recommended by Ronny Egner
Note: The values mentioned there are values set from experience. Set them at your own risk!
Value Description Recommended Value
vm.swappiness ( in /etc/sysctl.conf) adjusts the swap affinity. Range from 0 to 100. Lower values mean pages remain longer in memory before swapping them out to disk. Larger values swap out infrequently used memory pages faster. The default value is “60″. 5
Setting the kernel.shmmax value greater then 4GB -1 has the following disadvantage: If Oracle segfaults it will not be able to write a coredump file. This value represents the max size of a single shared memory segment, if your SGA(s) is larger than this value than multiple shared memory segments will be used. From metalink Doc ID:567506.1
Pingback: maximum connections semaphore check « Talipkorkmaz's Blog
This is Usefull post and easy to understand.Great job.
Regards sarathi
Pretty neat and useful