What was in the core file or trace files you uploaded for the SR. They may
tell you why oracle is recommending increasing the values.
This could be a bug with rman that requires more semaphores than normal and
this is their work around until they create a patch.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On
Behalf Of Mladen Gogala
Sent: Friday, February 12, 2021 7:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: linux kernel parameters
Hi Jeff,
The valies of kernel.sem represent SEMMSL, SEMMNS, SEMOPM, and SEMMNI.
SEMMSL is maximum number of semaphores in a semaphore set. SEMMNI is the
maximum number of semaphore sets. SEMMNS is the maximum number of semaphores
on the system. SEMOPM is the maximum number for semaphore operations per
semop call. SEMMSL*SEMMNI should be equal to SEMMNS. Oracle's default
settings do not make too much sense:
# oracle-database-preinstall-19c setting for kernel.sem is '250 32000 100
128'
kernel.sem = 250 32000 100 128
With the default settings, Oracle will consume 2 semaphore sets:
-bash-4.2$ ipcs -s
------ Semaphore Arrays --------
key semid owner perms nsems
0x8d2a3534 6 oracle 600 250
0x8d2a3535 7 oracle 600 250
That is for 70 oracle processes:
-bash-4.2$ ps -ef|grep ora_|grep -v grep|wc -l
70
However, I usually change the number of semaphores like this:
kernel.sem = 64 65536 1024 128
That means that I have 65536 semaphores distributed into 1024 semaphore
sets, each set containing 64 semaphores. When I check my semaphore usage
now, it looks like this:
------ Semaphore Arrays --------
key semid owner perms nsems
0x8d2a3534 11 oracle 600 64
0x8d2a3535 12 oracle 600 64
0x8d2a3536 13 oracle 600 64
0x8d2a3537 14 oracle 600 64
0x8d2a3538 15 oracle 600 64
0x8d2a3539 16 oracle 600 64
Those are 6 sets of 64 semaphores. There are 68 oracle processes on the
system
-bash-4.2$ ps -ef|grep ora_|grep -v grep|wc -l
68
That means that we have approximately 6 semaphores per process:
scale=2
6*64/68
5.64
Now, let's make few connections and see what happens:
The number of consumed semaphores remains the same:
-bash-4.2$ ipcs -s
------ Semaphore Arrays --------
key semid owner perms nsems
0x8d2a3534 11 oracle 600 64
0x8d2a3535 12 oracle 600 64
0x8d2a3536 13 oracle 600 64
0x8d2a3537 14 oracle 600 64
0x8d2a3538 15 oracle 600 64
0x8d2a3539 16 oracle 600 64
That means that the semaphores are allocated by the instance, based on the
processes parameter and not based on the number of running processes. The
processes parameter is:
SQL> show parameter processes
NAME TYPE VALUE
------------------------- ------- -----
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 80
log_archive_max_processes integer 4
processes integer 320
The maximum number of processes is 320. On the other hand, 6 sets of 64
semaphores each means that we have 384 semaphores allocated for 320
processes. That means that the old rule of "1 semaphore per process + 50 to
spare" still applies. However, with the default setting you allow 32000
semaphores on the system but you only allow 100 sets of 250 semaphores each.
That means that you cannot utilize more than 25000 semaphores. Modifying the
parameter according to the recommendation makes no sense whatsoever. BTW, I
prefer more smaller sets, to have better granularity. My version is almost
the same as yours
[mgogala@umajor ~]$ sql scott/tiger@ora19c
SQLcl: Release 20.4 Production on Fri Feb 12 21:48:37 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
And yes, I did renew the password and alter password_life_time to unlimited.
BTW, I was lazy as well. With 64 semaphores in the set, it doesn't make
sense to allow 128 simultaneous operations per semop call. It is only
possible to do a semop call on a single semaphore set, with one operation
per semaphore. So, my recommended sessting is:
kernel.sem = 64 65536 1024 64
On 2/12/21 8:20 AM, Beckstrom, Jeffrey wrote:
We have been pursuing a RMAN segmentation fauit issue with Oracle when doing
a "recover plluggable database" command in 19.7. Oracle is suggesting the
following change:
According to the sysctl.conf that you uploaded, the following is the current
KERNEL.SEM setting, as shown here.
kernel.sem = 250 32000 100 128
To avoid the segmentation fault, modify the /etc/sysctl.conf with the
following:
kernel.sem=3000 32000 3000 128
Have the kernel parameters changed with 19.7? Everything I Googled implies
our values should be sufficient.
Jeffrey Beckstrom
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
--
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com