Solaris + Oracle & multiple db's

[Igaduma]

Hello All,

I have a problem that is doing my head in.
On a V880 server with 8Gb ram there are about 3 running databases.
Each time I want to startup a fourth one, I get an error.

Making the other db's smaller in SGA doesn't seem to have any impact so it must be coming from the /etc/system file, although that seems correct.
The machine reports 3 db's up, top shows me 7.3Gb still free and 700Mb swap in use, with additional 5Gb swap free
Each db is configured with a very small SGA of 128Mb

This is the error I get when I try to start up the fourth db:

ORA-27302: failure occurred at: skgpwreset1
ORA-27303: additional information: invalid shared ctx
ORA-27146: post/wait initialization failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

Setting the actual db to 128Mb of SGA in the ini.ora file doesn't seem to change anything.

This is the /etc/system part for oracle:

*Oracle required params
* forceload: misc/ipc
forceload: sys/shmsys
forceload: sys/semsys
* forceload: sys/msgsys
set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967295

I tried upping the shmmax to 7Gb but to no avail, it seems to be another parameter.

Any help here is greatly appriciated!
Iga

 

[SamBones]

You didn't mention what version of Solaris or Oracle you're running, so some of these may not apply, but try this...

# Oracle
# Shared Memory
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmax=0xffffffff
set shmsys:share_page_table=1
set shmsys:shminfo_shmmni=900
set shmsys:shminfo_shmseg=900

# Semaphores
set semsys:seminfo_semmap=900
set semsys:seminfo_semmns=10000
set semsys:seminfo_semmnu=1500
set semsys:seminfo_semmni=4010
set semsys:seminfo_semmsl=4010
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767

You'll need to reboot for these to take effect. Also, you should actually tune them for your needs (assuming they work).

 

[Igaduma]

Hello Sambones,

I did reboot the server, but it still doesn't work.
Shouldn't I be able to add as many db's as I like ?

Thanks!
Iga

 

[Igaduma]

After adding your SGA parameters to the /etc/system file (and removing the initial ones) I get following error after a reboot:


ORA-27123: unable to attach to shared memory segment
SVR4 Error: 22: Invalid argument
Additional information: 1
Additional information: 2703

Strangely enough I can increase a running db up to 1.5Gb ?

It's running solaris 9, latest patch cluster & oracle9i

 

[Igaduma]

Hi all,

These setting in /etc/system made it happy.
But, how can I know *which* setting has done the trick ?

set shmsys:shminfo_shmmax=7294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=900
set shmsys:shminfo_shmseg=900
set semsys:seminfo_semmap=900
set semsys:seminfo_semmns=10000
set semsys:seminfo_semmnu=1500
set semsys:seminfo_semmni=4010
set semsys:seminfo_semmsl=4010
set semsys:seminfo_semopm=900
set semsys:seminfo_semvmx=32767

You mention tuning them, but is a "large" setting not applicable to most things ?
And, how exactly would I tune these settings ?

Thanks!
Iga

 

[kHz]

Most likely this:
set shmsys:shminfo_shmmax=7294967295

 

[SamBones]

Some of these don't matter if they're set large. For example "shminfo_shmmax" is setting the maximum amount of shared memory a single process can request. This is how Oracle creates it's SGA. It doesn't matter if this value is too large since Solaris will only give the process what it can. In other words, you can't set the SGA to more than your physical memory no matter how this parameter is set.

Some of these do matter if they are set too big. Some of these are defining how many of something the kernel can handle so it's actually building structures in the kernel to handle them. The larger some of these numbers are, the more memory taken up by the kernel and processes.

I used to have a link that explained each of these parameters and how to figure out what you need. I'll post it if I can find it.

Also, you can use the "[tt]ipcs[/tt]" command to see your shared memory and semaphore use. See the [tt]man[/tt] page for usage.

 

[SamBones]

Here's a link just from a quick Google...

http://www.lifeaftercoffee.com/2005/12/12/semaphore-settings-and-shared-memory-for-oracle-database/

 

[SamBones]

Here's another link with good info...

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:454420165038

You might also check the FAQs under the Oracle Forums on Tek-Tips.