ipcs and identifying which oracle dbs responsible

[Scott_130]

We are having some significant memory issues on our server. We currently have 9 (some large some not) database running and when I run an ipcs command the results look like this:
(I applied an attachement of the same as below just easier to read). The question is, how can I tell which Oracle database goes with which shared memory segment other than matching up the SGA's for every line?

m 1176585219 0x5a456e8c H--rw-r----- oracle dba186646528 12740 5216

m 304219140 0x52bda504 H--rw-r----- oracle dba146800640 15619 5222

m 2053 0x7e9c7798 H--rw-r----- oracle dba 88080384 15732 5141

m 26174472 0xc475e1d4 H--rw-r----- oracle dba687865856 15770 5224

m 1034 0x00000000 ---rw-r----- oracle dba 29360128 3272 5100

m 11 0x00000000 ---rw-r----- oracle dba 6291456 3272 5100

m 12 0x00000000 ---rw-r----- oracle dba 6291456 3272 5100

m 13 0x00000000 ---rw-r----- oracle dba264241152 3272 5100

m 14 0x00000000 ---rw-r----- oracle dba264241152 3272 5100

m 15 0x4fd07138 ---rw-r----- oracle dba 23068672 3272 5100

m 14522384 0x18740bb0 H--rw-r----- oracle dba570425344 4640 5091

m 1644196885 0x38c91a74 H--rw-r----- oracle dba167772160 23711 5097

m 251903001 0xb3f21168 H--rw-r----- oracle dba117440512 1197 5103

m 94675994 0xae6c87a8 H--rw-r----- oracle dba165675008 27757 5092


Thanks

Scott

[Tom Danzig]

As far as I know, the only way to match up the instances to the appropriate shared memory segment is to refernce the CPID or LPID to the various Oracle processes.

Someone else surely has a better way though I'm sure.

[Bill Hassell]

Use ipcs -bmop to find the PID's although this is not 100% as shared memory can be orphaned due to programming errors. Now the Oracle instances should be able to report on their SGA usage individually, but while it is good programming practice to have one process own and manage shared memory, it certainly is not a requirement. You may wan to get a copy of shminfo from: ftp://contrib:9unsupp8@hprc.external.hp.com/sysadmin/programs/shminfo/

[R. Allan Hicks]

You might want to look at this metalink doc
www.metalink.com

Note:68281.1
Subject: DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS

This might give you some idea of the source of your mem problem.

Note:153961.1
Subject: Semaphores and Shared Memory - An Overview

Also, seems like you have more memory segments than seem appropriate. Seems like I read about memory problems occuring from fragmented SGA. You might want to verify your kernel parameters for shmmax as outlined in one of the above notes.

Good Luck

[Graham Cameron_1]

The suggestion above re metalink 68281.1 doesn't seem to work with oracle 9i.

In any case, the crude way I have always done this is to match the ctime of the shared memory segment with the creation time of the LK file.

ipcs -ma|grep oracle gives, in the last col, the creation time of the shared memory.

ls -lrt $ORACLE_HOME/dbs/lk* gives, oldest first, the creation time of each lk file (created at db startup), and where the lk file name also contains the oracle sid.

Match the times. I do this by eye but it could be scripted.

Not scientific but I've never rm'd the wrong one yet.

Of course if > 1 database starts up within the same minute, this is useless.

-- Graham

[Duncan Edmonstone · Accepted solution]

...or you could just use the tool that oracle provide for this very purpose... 'sysresv' - you need to run it as oracle with your ORACLE_HOME set and $ORACLE_HOME/lib in your SHLIB_PATH - e.g.:

# su - oracle
$ echo $ORACLE_HOME
/u01/app/oracle/product/9.2.0
$ echo $SHLIB_PATH
/u01/app/oracle/product/9.2.0/lib
$ ${ORACLE_HOME}/bin/sysresv -?
/u01/app/oracle/product/9.2.0/bin/sysresv: illegal option -- ?
usage : sysresv [-if] [-d ] [-l sid1 ...]
-i : Prompt before removing ipc resources for each sid
-f : Remove ipc resources silently, oevrrides -i option
-d : List ipc resources for each sid if on
-l sid1 .. : apply sysresv to each sid
Default : sysresv -d on -l $ORACLE_SID
Note : ipc resources will be attempted to be deleted for a
sid only if there is no currently running instance
with that sid.
$ ${ORACLE_HOME}/bin/sysresv -l hpdb rcat

IPC Resources for ORACLE_SID "hpdb" :
Shared Memory:
ID KEY
13811 0x0f1c4f54
Semaphores:
ID KEY
327 0x22e38130
Oracle Instance alive for sid "hpdb"

IPC Resources for ORACLE_SID "rcat" :
Shared Memory:
ID KEY
12 0xafb9fc9c
Semaphores:
ID KEY
428 0xc178d65c
Oracle Instance alive for sid "rcat"

HTH

Duncan

[Hein van den Heuvel]

Scott,
Good question.


Bill,
ipcs -CPID will be gone, and LPID may or might not be there. No programming bug needed. The last attacher can simply have left, with others still attached but un-identified. Also, shminfo sounds cool, but did not work directly on my Itanium box.

R.Allan,
Good articles!
One suggested technique did not work directly on 9.2:
SQL> oradebug ipc
ORA-00074: no process has been specified

Minor nit: www.metalink.com leads to some french page with bonus pop-up :-)
This should be: www.metalink.oracle.com


Graham,
Ditto. I use the CTIME lign up.

Duncan,
Thanks to remind us about sysresv. It is also mentioned in the metalink articles. On my Itanium my Oracle instance happend to have 40 segments (yeah I know how to fix that. It was a test). sysresv only listed 4.

Cheers all,
Hein.