In this article I will describe the steps used to identify which shared memory and semaphore segments are owned by a particular instance in Oracle V7, V8.0, V8i, and V9i. This can be helpful in recovery situations where the database instance has not released its shared memory and semaphores on database shutdown.
Consider the situation where you have several instances running on one database server and one crashes leaving the “sgadef
First, run “ipcs -b” on the database server. You will get out similar to table 1.
QMSDEV on appdev: ipcs -b
IPC status from
Message Queue facility not in system.
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 0 0x0fe2b384 –rw-r—– oracle dba63733760
m 1 0x0feaab41 –rw-r—– oracle dba63733760
m 353 0x0fc80ce5 –rw-r—– oracle dba55721984
m 154 0x0fe3a366 –rw-r—– oracle dba11059200
m 111 0x0fc78b7c –rw-r—– oracle dba57868288
T ID KEY MODE OWNER GROUP NSEMS
Semaphores:
s 0 00000000 –ra-r—– oracle dba 25
s 1 00000000 –ra-r—– oracle dba 25
s 2 00000000 –ra-r—– oracle dba 25
s 3 00000000 –ra-r—– oracle dba 25
s 458758 00000000 –ra-r—– oracle dba 25
s 458759 00000000 –ra-r—– oracle dba 25
s 196616 00000000 –ra-r—– oracle dba 25
s 196617 00000000 –ra-r—– oracle dba 25
s 196618 00000000 –ra-r—– oracle dba 25
s 196619 00000000 –ra-r—– oracle dba 25
s 19 0x00000078 –ra-ra-ra- root dba 1
s 131092 00000000 –ra-r—– oracle dba 25
s 131093 00000000 –ra-r—– oracle dba 25
s 131094 00000000 –ra-r—– oracle dba 25
s 131095 00000000 –ra-r—– oracle dba 25
s 131096 00000000 –ra-r—– oracle dba 25
s 131097 00000000 –ra-r—– oracle dba 25
s 131098 00000000 –ra-r—– oracle dba 25
s 131099 00000000 –ra-r—– oracle dba 25
Running “oradebug” in Oracle9i
In Oracle9i, is you were to attempt to simply use “oradebug ipc” at the SQL prompt, you will get the following:
SQL> oradebug ipc
ORA-00074: no process has been specified
In Oracle9i, this change was made to support dumping IPC information for Oracle Parallel Server (OPS) or RAC. Unlike semaphores and shared memory IPC information is different for every process when you run in an OPS environment rather than in a single mode environment.
Here is the workaround I was able to use in obtaining shared memory and semaphore information in Oracle9i. In this example, you will need to have two telnet sessions open to the database server: One to login to SQL*Plus and (2) another to determine the background process (server processes) of the SQL*Plus session:
% sqlplus “/ as sysdba”
Now in another shell session, look for the background (sever) process of the SQL*Plus session:
% ps -ef | grep TARGDB | grep LOCAL
oracle 4744 1 0 12:54:55 ? 0:00 oracleTARGDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Now go back to your SQL*Plus session and issue the following:
SQL> oradebug setospid 4744
Statement processed.
SQL> oradebug ipc
Information written to trace file.
You can now exit your SQL*Plus and navigate to the user_dump_dest directory and locate the trace file just created:
% cd /u01/app/oracle/admin/TARGDB/udump
% ls -lt
total 8
-rw-r–r– 1 oracle dba 4025 Feb 4 13:00 targdb_ora_4744.trc
% cat targdb_ora_4744.trc
/u01/app/oracle/admin/TARGDB/udump/targdb_ora_4744.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name: SunOS
Node name: alex
Release: 5.8
Version: Generic_108528-19
Machine: sun4u
Instance name: TARGDB
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 4744, image: oracle@alex (TNS V1-V3)
*** 2004-02-04 13:00:48.767
*** SESSION ID:(11.46) 2004-02-04 13:00:48.766
Dump of unix-generic skgm context
areaflags 00000037
realmflags 0000000f
mapsize 00002000
protectsize 00002000
lcmsize 00002000
seglen 00400000
largestsize 00000000f8000000
smallestsize 0000000001000000
stacklimit ff46ec6f
stackdir -1
mode 640
magic acc01ade
Handle: 2ef40f8 `/u01/app/oracle/product/9.2.0TARGDB’
Dump of unix-generic realm handle `/u01/app/oracle/product/9.2.0TARGDB’, flags = 00000000
Area #0 `Fixed Size’ containing Subareas 0-0
Total size 000000000006f1e0 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 8903 0000000020000000 0000000020000000
Subarea size Segment size
0000000000070000 0000000020400000
Area #1 `Variable Size’ containing Subareas 1-1
Total size 000000001f000000 Minimum Subarea size 01000000
Area Subarea Shmid Stable Addr Actual Addr
1 1 8903 0000000020070000 0000000020070000
Subarea size Segment size
000000001ff90000 0000000020400000
Area #2 `Redo Buffers’ containing Subareas 2-2
Total size 00000000000a6000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
2 2 8903 0000000040000000 0000000040000000
Subarea size Segment size
00000000000a6000 0000000020400000
Area #3 `skgm overhead’ containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
3 3 8903 00000000400a6000 00000000400a6000
Subarea size Segment size
0000000000002000 0000000020400000
Dump of Solaris-specific skgm context
sharedmmu 00000001
shareddec 0
used region 0: start 0000000020000000 length 0000000021000000
Maximum processes: = 250
Number of semaphores per set: = 127
Semaphores key overhead per set: = 4
User Semaphores per set: = 123
Number of semaphore sets: = 3
Semaphore identifiers: = 3
Semaphore List=
11730950
————– system semaphore information ————-
IPC status from
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 458752 0xb748224 –ra-r—– oracle dba oracle dba 127 13:00:32 17:55:39
s 65537 0xb748225 –ra-r—– oracle dba oracle dba 127 no-entry 17:55:39
s 65538 0xb748226 –ra-r—– oracle dba oracle dba 127 17:55:40 17:55:39
s 458755 0xe150224 –ra-r—– oracle dba oracle dba 127 13:00:45 17:55:50
s 65540 0xe150225 –ra-r—– oracle dba oracle dba 127 no-entry 17:55:50
s 65541 0xe150226 –ra-r—– oracle dba oracle dba 127 17:55:51 17:55:50
s 11730950 0x9c5e0880 –ra-r—– oracle dba oracle dba 127 13:00:39 12:31:34
s 2818055 0x9c5e0881 –ra-r—– oracle dba oracle dba 127 no-entry 12:31:34
s 2818056 0x9c5e0882 –ra-r—– oracle dba oracle dba 127 12:31:36 12:31:34
The above output shows that the particular database instance I was logged into, owned shared memory segment 353 while its semaphore identifiers where 458758 and 458759. You can verify that these are correct by looking them up in Table 1 above.
NOTE: The above “oradebug ipc” output is from a Version 7 database. See the notes at the bottom of this document for issues on running “oradebug” on Oracle8 and Oracle8i.
After logging into each database instance and running “oradebug ipc”, you will know which segments are valid on the running databases. Using this process of elimination you can identify the idle segments from a crashed instance. You can then kill them using “ipcrm -m” and “ipcrm -s” respectfully.
The command syntax to remove the shared memory segments or semaphores is as follows:
% ipcrm -m
% ipcrm -s