Linux in Oracle DBA Life
============================

######################################
2/t        SEMAPHORES
######################################

Semaphores can best be described as counters which are used to provide synchronization between processes or between threads within a process for shared resources like shared memories.
System V semaphores support semaphore sets where each one is a counting semaphore. So when an application requests semaphores, the kernel releases them in sets. The number of semaphores per set can be defined through the kernel parameter SEMMSL.

[root@ace2oracledb ~]# ipcs -ls

------ 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

[root@ace2oracledb ~]#

--->>> 8.1. The SEMMSL Parameter (Part 1 of semaphore set)

This parameter defines the maximum number of semaphores per semaphore set.

Current setting as per 19c-prerequistes log

==> Trying to remove instances of  - setting for kernel.sem is
==> Adding kernel.sem = 250 32000 100 128

Oracle recommends SEMMSL to be at least 250 for 9i R2 and 10g R1/R2 databases

--->>>  8.2. The SEMMNI Parameter (Part 2 of semaphore set)

This parameter defines the maximum number of semaphore sets for the entire Linux system.

Oracle recommends SEMMNI to be at least 128 for 9i R2 and 10g R1/R2 databases

--->>> 8.3. The SEMMNS Parameter (Part 3 of semaphore)
 
 This parameter defines the total number of semaphores (not semaphore sets) for the entire Linux system.
 A semaphore set can have more than one semaphore, and as the semget(2) man page explains, values greater than SEMMSL * SEMMNI makes it irrelevant.
 The maximum number of semaphores that can be allocated on a Linux system will be the lesser of: SEMMNS or (SEMMSL * SEMMNI).
Oracle recommends SEMMSL to be at least 32000 for 9i R2 and 10g R1/R2 databases

--->>>  8.4. The SEMOPM Parameter

This parameter defines the maximum number of semaphore operations that can be performed per semop(2) system call (semaphore call). The semop(2) function provides the ability to do operations for multiple semaphores with one semop(2) system call. Since a semaphore set can have the maximum number of SEMMSL semaphores per semaphore set, it is often recommended to set SEMOPM equal to SEMMSL.
Oracle recommends to set SEMOPM to a minimum value of 100 for 9i R2 and 10g R1/R2 databases on x86 and x86-64 platforms.

 

--->>>   8.5. Setting Semaphore Parameters

[root@ace2oracledb ~]# cat /proc/sys/kernel/sem
250    32000    100    128
[root@ace2oracledb ~]#

These values represent SEMMSL, SEMMNS, SEMOPM, and SEMMNI.

 Alternatively, you can run:

# ipcs -ls

All four described semaphore parameters can be changed in the proc file system without reboot:

# echo 250 32000 100 128 > /proc/sys/kernel/sem

Alternatively, you can use sysctl(8) to change it:

sysctl -w kernel.sem="250 32000 100 128"

To make the change permanent, add or change the following line in the file /etc/sysctl.conf. This file is used during the boot process.

echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf

 

 

*********** 8.6. An Example of Semaphore Settings *********
On systems where the ora.init parameter PROCESSES is very large, the semaphore settings need to be adjusted accordingly.

The SEMMSL setting should be 10 plus the largest PROCESSES parameter of any Oracle database on the system. So if you have one database instance running on a system where PROCESSES is set to 5000, then SEMMSL should be set to 5010.

The maximum number of semaphores that can be allocated on a Linux system will be the lesser of: SEMMNS or (SEMMSL * SEMMNI). Since SEMMNI can stay at 128, we need to increase SEMMNS to 641280 (5010*128).

A semaphore set can have the maximum number of SEMMSL semaphores per semaphore set and it is recommended to set SEMOPM equal to SEMMSL. Since SEMMSL is set to 5010 the SEMOPM parameter should be set to 5010 as well.

Hence, if the ora.init parameter PROCESSES is set to 5000, then the semaphore settings should be as follows:

sysctl -w kernel.sem="5010 641280 5010 128"

************************************************************

######################################
2/t        Setting File handlers
######################################

=> File handler

Trying to remove instances of  - setting for fs.file-max is
Adding fs.file-max = 6815744
Trying to remove instances of  - setting for fs.aio-max-nr is
Adding fs.aio-max-nr = 1048576

--->>>   Chapter 9. Setting File Handles

The maximum number of file handles denotes the maximum number of open files on a Linux system.

Oracle recommends that the file handles for the entire system is set to at least 65536 for 9i R2 and 10g R1 and R2 for x86 and x86-64 platforms.

To determine the maximum number of file handles for the entire system, run:

[root@ace2oracledb ~]# cat /proc/sys/fs/file-max
6815744
[root@ace2oracledb ~]#

 To determine the current usage of file handles, run:
 
[root@ace2oracledb ~]# cat /proc/sys/fs/file-nr
20352    0    6815744
[root@ace2oracledb ~]#
[root@ace2oracledb ~]#

 The file-nr file displays three parameters:

    => the total allocated file handles.
    => the number of currently used file handles (with the 2.4 kernel); or the number of currently unused file handles (with the 2.6 kernel).
    => the maximum file handles that can be allocated (also found in /proc/sys/fs/file-max).
    
The maximum number of file handles can be changed in the proc file system without reboot:

# echo 65536 > /proc/sys/fs/file-max

 To make the change permanent, add or change the following line in the file /etc/sysctl.conf. This file is used during the boot process.

echo "fs.file-max=65536" >> /etc/sysctl.conf

===>>> fs.aio-max-nr

aio-nr is the running total of the number of events specified on the
io_setup system call for all currently active aio contexts.  If aio-nr
reaches aio-max-nr then io_setup will fail with EAGAIN.  Note that
raising aio-max-nr does not result in the pre-allocation or re-sizing
of any kernel data structures.

[root@ace2oracledb ~]# sysctl -a | grep aio
fs.aio-max-nr = 1048576
fs.aio-nr = 14693

fs.aio-max-nr = 1048576
Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem I/O subsystem failures.

The kernel parameter and shell limit values in this 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 your operating system documentation for more information about tuning kernel parameters.

 

######################################
2/t        Changing Network Kernel Settings
######################################
--->>>    10.2. Changing Network Kernel Settings

Oracle now uses User Datagram Protocol (UDP) as the default protocol on Linux for interprocess communication, such as cache fusion buffer transfers between the instances.

=> net related

Trying to remove instances of  - setting for net.core.rmem_default is
Adding net.core.rmem_default = 262144
Trying to remove instances of  - setting for net.core.rmem_max is
Adding net.core.rmem_max = 4194304
Trying to remove instances of  - setting for net.core.wmem_default is
Adding net.core.wmem_default = 262144
Trying to remove instances of  - setting for net.core.wmem_max is
Adding net.core.wmem_max = 1048576
Trying to remove instances of  - setting for net.ipv4.ip_local_port_range is
Adding net.ipv4.ip_local_port_range = 9000 65500
Trying to remove instances of  - setting for net.ipv4.conf.all.rp_filter is
Adding net.ipv4.conf.all.rp_filter = 2
Trying to remove instances of  - setting for net.ipv4.conf.default.rp_filter is
Adding net.ipv4.conf.default.rp_filter = 2

Oracle recommends the default and maximum send buffer size (SO_SNDBUF socket option) and receive buffer size (SO_RCVBUF socket option) to be set to 256 KB.
The receive buffers are used by TCP and UDP to hold received data until it is read by the application. The receive buffer cannot overflow because the peer is not allowed to send data beyond the buffer size window.
This means that datagrams will be discarded if they do not fit in the socket receive buffer.
This could cause the sender to overwhelm the receiver.

wmem -> write Buffer
rmem -> Read Buffer

The default and maximum window size can be changed in the proc file system without reboot:
The default setting in bytes of the socket receive buffer:

# sysctl -w net.core.rmem_default=262144

The default setting in bytes of the socket send buffer:

# sysctl -w net.core.wmem_default=262144

The maximum socket receive buffer size which may be set by using the SO_RCVBUF socket option:

# sysctl -w net.core.rmem_max=262144

The maximum socket send buffer size which may be set by using the SO_SNDBUF socket option:

# sysctl -w net.core.wmem_max=262144

To make the change permanent, add the following lines to the /etc/sysctl.conf file, which is used during the boot process:

net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144

 

 On Red Hat Enterprise Linux systems the default range of IP port numbers that are allowed for TCP and UDP traffic on the server is too low for 9i and 10g systems.
 Oracle recommends the following port range:

# sysctl -w net.ipv4.ip_local_port_range="1024 65000"

To make the change permanent, add the following line to the /etc/sysctl.conf file, which is used during the boot process:

net.ipv4.ip_local_port_range=1024 65000

The first number is the first local port allowed for TCP and UDP traffic, and the second number is the last port number.

 

===>>
The IPv4 setting for rp_filter or Reverse Path filtering is a method used by the Linux Kernel to help prevent attacks used by Spoofing IP Addresses.
Reverse path filtering is a Kernel feature that, when enabled, is designed to ensure packets that are not routable to be dropped. The easiest example of this would be and IP Address of the range 10.0.0.0/8, a private IP Address, being received on the Internet facing interface of the router. These packets can’t be routed to the Internet and should not be there, if they are something bad is happening.

[root@ace2oracledb ~]# sysctl -ar '.rp_filter'
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
net.ipv4.conf.enp0s3.rp_filter = 2
net.ipv4.conf.enp0s8.rp_filter = 2
net.ipv4.conf.lo.rp_filter = 0
net.ipv4.conf.virbr0.rp_filter = 2
net.ipv4.conf.virbr0-nic.rp_filter = 2
[root@ace2oracledb ~]#

 

 

The 3 values that can be set for the key rp_filter are:
0 No source address validation is performed and any packet is forwarded to the destination network
1 Strict Mode as defined in RFC 3704. Each incoming packet to a router is tested against the routing table and if the interface that the packet is received on is not the best return path for the packet then the packet is dropped.
2 Loose mode as defines in RFC 3704 Loose Reverse Path. Each incoming packet is tested against the route table and the packet is dropped if the source address is not routable through any interface. The allows for asymmetric routing where the return path may not be the same as the source path

The default value we can see is set to 1 in all cases except the loop-back interface: lo.

==>>

######################################
2/t        Chapter 7. Setting Shared Memory
######################################

Shared memory allows processes to access common structures and data by placing them in shared memory segments.
It is the fastest form of inter-process communication available since no kernel involvement occurs when data is passed between the processes.
In fact, data does not need to be copied between the processes.

Oracle uses shared memory segments for the Shared Global Area (SGA) which is an area of memory that is shared by Oracle processes.
The size of the SGA has a significant impact to Oracle's performance since it holds database buffer cache and much more.

SGA could be allocate ion 3 modes
-> single memory
-> contiguous
-> non-contiguous

to see all shared  segment setting

[root@ace2oracledb ~]# ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4294967296
max total shared memory (kbytes) = 4294967296
min seg size (bytes) = 1

********
Trying to remove instances of  - setting for kernel.shmmni is
Adding kernel.shmmni = 4096
Trying to remove instances of  - setting for kernel.shmall is
Adding kernel.shmall = 1073741824
Trying to remove instances of  - setting for kernel.shmmax is
Adding kernel.shmmax = 4398046511104
********
[root@ace2oracledb ~]#

===>>> 7.1. Setting SHMMAX Parameter( in BYTES)

This parameter defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space.

For example, if you use the Red Hat Enterprise Linux 3 smp kernel on a 32 bit platform (x86), then the virtual address space for a user process is 3 GB. If you use the Red Hat Enterprise Linux 3 hugemem kernel on a 32 bit platform (x86), then the virtual address space for a user process is almost 4GB. Hence, setting SHMMAX to 4GB - 1 byte (4294967295 bytes) on a smp kernel on a 32 bit architecture will not increase the maximum size of a shared memory segment to 4 GB -1. Even setting SHMMAX to 4 GB - 1 byte using the hugemem kernel on a 32 bit architecture will not enable a process to get such a large shared memory segment.

In fact, the upper limit for a shared memory segment for an Oracle 10g R1 SGA using the hugemem kernel is roughly 3.42 GB (~3.67 billion bytes) since virtual address space is also needed for other things like shared libraries. This means if you have three 2 GB shared memory segments on a 32 bit system, no process can attach to more than one shared memory segment at a time. Also note if you set SHMMAX to 4294967296 bytes (4*1024*1024*1024=4GB) on a 32 bit system, then SHMMAX will essentially bet set to 0 bytes since it wraps around the 4GB value. This means that SHMMAX should not exceed 4294967295 on a 32 bit system. On x86-64 platforms, SHMMAX can be much larger than 4GB since the virtual address space is not limited by 32 bits.

 -> Since the SGA is comprised of shared memory, SHMMAX can potentially limit the size of the SGA. SHMMAX should be slightly larger than the SGA size. If SHMMAX is too small, you can get error messages similar to this one:

ORA-27123: unable to attach to shared memory segment

 To determine the maximum size of a shared memory segment---in BYTES, run:
 
[root@ace2oracledb ~]# cat /proc/sys/kernel/shmmax
4398046511104

 The default shared memory limit for SHMMAX can be changed in the proc file system without reboot:

# echo 2147483648 > /proc/sys/kernel/shmmax

Alternatively, you can use sysctl(8) to change it:

# sysctl -w kernel.shmmax=2147483648

To make a change permanent, add the following line to the file /etc/sysctl.conf (your setting may vary). This file is used during the boot process.

# echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf

 

===>>>  7.2. Setting SHMMNI Parameter (in NUMBERS)

This parameter sets the system wide maximum number of shared memory segments.(in case of NON-single memory allocation)
Oracle recommends SHMMNI to be at least 4096 for Oracle 10g. For Oracle 9i on x86 the recommended minimum setting is lower. Since these recommendations are minimum settings, it is best to set it always to at least 4096 for 9i and 10g databases on x86 and x86-64 platforms.

o determine the system wide maximum number of shared memory segments, run:

# cat /proc/sys/kernel/shmmni
4096

The default shared memory limit for SHMMNI can be changed in the proc file system without reboot:

# echo 4096 > /proc/sys/kernel/shmmni

Alternatively, you can use sysctl(8) to change it:

# sysctl -w kernel.shmmni=4096

To make a change permanent, add the following line to the file /etc/sysctl.conf. This file is used during the boot process.

# echo "kernel.shmmni=4096" >> /etc/sysctl.conf

 

===>>>  7.3. Setting SHMALL Parameter(Pages in NUMBERS)

This parameter sets the total amount of shared memory pages that can be used system wide (per Linux Machine). Hence, SHMALL should always be at least ceil(shmmax/PAGE_SIZE).

The default size for SHMALL in Red Hat Enterprise Linux 2.1, 3, 4 and 5 is 2097152 which is also Oracle's recommended minimum setting for 9i and 10g on x86 and x86-64 platforms.

PAGE_SIZE is usually 4096 bytes

 If you are not sure what the default PAGE_SIZE is on your Linux system, you can run the following command and get value in BYTES

$ getconf PAGE_SIZE
4096

SO it is 4KB

 To determine the system wide maximum number of shared memory pages, run:

[root@ace2oracledb ~]# cat /proc/sys/kernel/shmall
1073741824

The default shared memory limit for SHMALL can be changed in the proc file system without reboot:

# echo 2097152 > /proc/sys/kernel/shmall

Alternatively, you can use sysctl(8) to change it:

# sysctl -w kernel.shmall=2097152

To make the change permanent, add the following line to the file /etc/sysctl.conf. This file is used during the boot process.

# echo "kernel.shmall=2097152" >> /etc/sysctl.conf

NOTE -> SHMALL displays value in PAGES so to get in BYTES we need to multiply with PAGE_SIZE.