Determining Semaphore usage

[Tony Williams]

After a backup in whichour Oracle database is shitdown then started, the start fails. Our DBA says:

On Feb. 22nd, we did reduce the number of processes (connections ) in Oracle due to the following problem. The operating system was not able to start the Oracle database after the COLD backup, but the database could be started manually. It was diagnosed to be related to semaphores. Since DBA group could not change the number of semaphores (Brian was out of the office ), we reduced the number of processes (connections) from 100 to 90. The number of semaphores on that box was 100. The number of semaphores should be at least 10% more than the number of processes.

Our semaphore kernel paramaters are:

semaem 16384
semmap 3202
semmni 3200
semmns 6400
semmnu 1600
semume 64
semvmx 32768

To me its sees like we have more than enough semaphore sets and semaphores. While running there are 90 semaphores in use by the Oracle SGA.

Is ther a way to determine how many semaphores are in use other than ipcs -sob, which does not seem to be accurate?

Also does anyone know what may be causing this problem?

[Marcelo De Florio]

For monitoring the request/seg for semaphores, with the following command:

sar -m

eg, sar -m 1 10.

MDF

[James A. Donovan]

You semaphore parameters look fine to me as well. What was ther error message given when Oracle failed to restart? Any relevant messages given in the Oracle alert.log? Does the init.ora file specify "processes = 90"? That is the parameter which determines how many semaphores are allocated for Oracle.

[Tony Williams]

Yes Jim,

the init.ora processes=90 was changed from processes=100 so that Oracle could start, I do not have the info from the alert.log on this date.

[ajax13]

I know this
to calculate the # for SEMMNS, use this formula
sum the # of processes for each oracle DB except the largest one. then add (2 * the # of processes to the largest processes) then add
(10 * the number of databases).

This is only for oracle8i, if you have other programs which use shared memory and semaphore you need to adjust the value.

Your backup program/tool is using shared memory and semaphores and that is why the database could not start. You may wanna consider increasing SEMMNS parameter.

[Rita C Workman · Accepted solution]

Well, different versions of Oracle make more demands (like Oracle 8i..) but the following is the basic formula I use to set up semaphores in our kernels:

1. For each db instance you wish to run list out the processes parm from the init.ora file.
2. For MIPS based machines only: add 1 to each of these figures. Kepp this list of gures for use as the 'oraproc' parm in step 4.
3. Sum these fugres.
The figure you have is the # of semaphores required by Oracle to start ALL db's. Add to this any other system requirements and sensure SEMMNS is AT LEAST this value.
i.e. SEMMS >= SUM of 'processes' for all db's
+ 1 per database (MIPS only)
+ other system requirements
4. Semaphores are allocated by Unix in 'sets' of up to SEMMSL semaphores per set. You can have a MAXIMUM of SEMMNI sets on the system at any one time. SEMMSL is an arbitrary figure which is best set to a round figure no smaller that the smallest 'processes' figure for any db on the system. This is not a requirement though.

Note that SEMMSL is not used on all Unix platfors. eg: hpux does not have a SEMMSL limit on the # of semaphores in any one set.

To determine Oracle requirements for SEMMNI:
Take each figure from step 2 & substitue it for ORAPROC below:
Sets requred for Instance = (ORAPROC / SEMMSL ) rounded UP.

Sum these fugres for all instances. This gives you Oracle's SEMMNI requirement. Add to this any other system requirements.

System requirements are generally 10% above what Oracle requires. however you need to take into account any other programs that require semaphores.

5. On MIPS systems SEMMNU should be set at least equal to SEMMS.
6. Oracle 8.0.x and 89.1.x try to allocate twice as many semaphores as are in the "init.ora" file on startup. For ex: if processes = 200, ORacle will need 400 to startup the SGA. This needs to be part of your calculations.
Ex: If you have 3 db's and the init.ora files have 100, 150 and 200 processes allocated for ea. db then you add these up = 450 + 30 = 450 and an extra 10 = 480..I would then round this up to 500 . Now you would need to set SEMMS to at twice this number (500 * 2 = 1000 semms).
And don't forget that semmap you add 2 more to so SEMMAP would be 1000 + 2 = 10002.

If all you using is 90 processes and only running 1 db instance...then I'd relook at those numbers..

Just my 2cents,
/rcw

[Rita C Workman]

hmmm...seems my keyboard is sticking...where you see fugres...it should be f i g u r e s..

sorry
/rcw

[Tony Williams]

I would like to thank everyone for the info however, after doing all the math it seems like I need 100 semaphores and at more 200 and my kernel is set to 6400, which should be more than enough, so as of this minute Its a mystery as to why Oracle will not start. Sar -m is about 2 sema/s and ipcs -sob shows 100 semaphores in use. I will create a cron job to look at ipcs and sar while Oracle is starting so I can get a better picture.

[Rita C Workman]

Tony, please take a look at this thread and check the parms it mentions.....

http://us-support.external.hp.com/cki/bin/doc.pl/sid=ace24a220a8ea2c2d8/screen=ckiDisplayDocument?docId=200000038532536

Hope this helps,
/rcw