Oracle DataBase tuning on Linux

Verify that the kernel parameters shown in the following table are set to values greater than or equal to the minimum value shown. If the current value for any parameter is higher than the value listed in this table, then do not change the value of that parameter. The procedure following the table describes how to verify and set the values.
Execute command
/sbin/sysctl -a | egrep "kernel.shmall|kernel.shmmax|kernel.shmmn|kernel.sem"
and compare the values with the values in the table below
Note:
The kernel parameter and shell limit values shown in the following section are minimum values only. For production database systems, Oracle recommends that you tune these values to optimize the performance of the system. Refer to the operating system documentation for more information about tuning kernel parameters.

Parameter	Minimum Value	Recommended value	File
semmsl semmns semopm semmni	250 32000 100 128	Set only if those that are set by OS or other applications are lower	/proc/sys/kernel/sem
shmall	2097152	shmmax/page_size usually page_size=4	/proc/sys/kernel/shmall
shmmax	Minimum of the following values: ·       Half the size of the memory ·       4GB - 1 byte Note: The minimum value required for shmmax is 0.5 GB. However, Oracle recommends that you set the value of shmmax to 2.0 GB for optimum performance of the system.	Half of RAM or if swap file is less then half of RAM the size of swap file	/proc/sys/kernel/shmmax
shmmni	4096		/proc/sys/kernel/shmmni
file-max	512 * PROCESSES		/proc/sys/fs/file-max
ip_local_port_range	Minimum:9000 Maximum: 65000		/proc/sys/net/ipv4/ip_local_port_range
rmem_default	262144		/proc/sys/net/core/rmem_default
rmem_max	4194304		/proc/sys/net/core/rmem_max
wmem_default	262144		/proc/sys/net/core/wmem_default
wmem_max	1048576		/proc/sys/net/core/wmem_max
tcp_wmem	262144		/proc/sys/net/ipv4/tcp_wmem
tcp_rmem	4194304		/proc/sys/net/ipv4/tcp_rmem

To display the current value specified for these kernel parameters, and to change them if necessary, use the following steps:
Enter the commands shown in the following table to display the current values of the kernel parameters, make a note of these values and identify any values that you must change:

For semmsl, semmns, semopm, and semmni

# /sbin/sysctl -a | grep sem 

This command displays the value of the semaphore parameters in the order listed.

shmall, shmmax, and shmmni

# /sbin/sysctl -a | grep shm 

This command displays the details of the shared memory segment sizes.file-max

# /sbin/sysctl -a | grep file-max  

This command displays the maximum number of file handles.

ip_local_port_range

# /sbin/sysctl -a | grep ip_local_port_range

This command displays a range of port numbers.

rmem_default

# /sbin/sysctl -a | grep rmem_default

rmem_max

# /sbin/sysctl -a | grep rmem_max

wmem_default

# /sbin/sysctl -a | grep wmem_default

wmem_max

# /sbin/sysctl -a | grep wmem_max

tcp_wmem

# /sbin/sysctl -a | grep tcp_wmem

tcp_rmem

# /sbin/sysctl -a | grep tcp_rmem

If the value of any kernel parameter is different from the minimum value, then complete the following procedure:

Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:
Note:
Include lines only for the kernel parameter values that you want to change. For the semaphore parameters (kernel.sem), you must specify all four values. However, if any of the current values are larger than the minimum value, then specify the larger value.
fs.file-max = 512 * PROCESSES
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
or
kernel.sem = 250 256000 100 1024
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304
Note:
The minimum value required for shmmax is 0.5 GB. However, Oracle recommends that you set the value of shmmax to 2.0 GB for optimum performance of the system.
By specifying the values in the /etc/sysctl.conf file, they persist when you restart the system. However, on SUSE Linux Enterprise Server systems, enter the following command to ensure that the system reads the /etc/sysctl.conf file when it restarts:
# /sbin/chkconfig boot.sysctl on
Enter the following command to change the current values of the kernel parameters:
# /sbin/sysctl -p
Review the output from this command to verify that the values are correct. If the values are incorrect, edit the /etc/sysctl.conf file, then enter this command again.
Enter the command /sbin/sysctl -a to confirm that the values are set correctly.
On SUSE systems only, enter the following command to cause the system to read the /etc/sysctl.conf file when it restarts:
# /sbin/chkconfig boot.sysctl on
On SUSE systems only, you must enter the GID of the oinstall group as the value for the parameter /proc/sys/vm/hugetlb_shm_group. Doing this grants members of oinstall a group permission to create shared memory segments.
For example, where the oinstall group GID is 501:
# echo 501 > /proc/sys/vm/hugetlb_shm_group
After running this command, use vi to add the following text to /etc/sysctl.conf, and enable the boot.sysctl script to run on system restart:
vm.hugetlb_shm_group=501
Note:
Only one group can be defined as the vm.hugetlb_shm_group.
After updating the values of kernel parameters in the /etc/sysctl.conf file, either restart the computer, or run the command sysctl -p to make the changes in the /etc/sysctl.conf file available in the active kernel memory.

Set Shell Limits for the oracle User

To improve the performance of the software, you must increase the following shell limits for the oracle user:

Shell Limit	Item in limits.conf	Hard Limit
Maximum number of open file descriptors	nofile	65536
Maximum number of processes available to a single user	nproc	16384

To increase the shell limits:
Add the following lines to the /etc/security/limits.conf file:
oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session    required     pam_limits.so
Depending on the oracle user's default shell, make the following changes to the default shell start-up file:
·       For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE Linux Enterprise Server systems /etc/profile.local):
if [ $USER = "oracle" ]; then
        if [ $SHELL = "/bin/ksh" ]; then
              ulimit -p 16384
              ulimit -n 65536
        else
              ulimit -u 16384 -n 65536
        fi
fi