DBA Tips Archive for Oracle

  


Determining which instance owns which shared memory & semaphore segments

by Jeff Hunter, Sr. Database Administrator

Introduction

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<SID>.dbf" file, shared memory and semaphore segments running. Given that you have many instances running, it becomes unclear which shared memory and semaphore sets to kill. The steps below will allow you to determine which shared memory and semaphore segments NOT to kill.

First, run "ipcs -b" on the database server. You will get out similar to table 1.

QMSDEV on appdev: ipcs -b
IPC status from <running system> as of Tue Sep 26 16:22:17 2000
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
Table 1 - Example output from "ipcs -b"

Now log into each individual instance you have up and running by setting your "ORACLE_HOME" and "ORACLE_SID". Log into each instance using "svrmgrl" and use the following command:

    svrmgr> connect internal
    Connected.
    SVRMGR> oradebug ipc
    -------------- Shared memory --------------
    Seg Id     Address   Size
    353        80000000  55721984
    Total: # of segments = 1, size = 55721984
    -------------- Semaphores ----------------
    Total number of semaphores = 50
    Number of semaphores per set = 25
    Number of semaphore sets = 2
    Semaphore identifiers:
     458758 458759

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 <shared memory id>
   % ipcrm -s <semaphore id>


Running "oradebug" in Oracle8
When running "oradebug" in an Oracle8 environment, Oracle will write all shared memory information to a trace file in your "user_dump_dest" directory while writting semaphore informtion to your screen. Output from "oradebug ipc" in an Oracle8 environment would like the following:
    SVRMGR> connect internal
    Connected.
    svrmgr> oradebug ipc
    Shared memory information written to trace file.
    -------------------- Semaphores --------------------
    Total number of semaphores = 250
    Number of semaphores per set = 25
    Number of semaphore sets = 10
    Semaphore identifiers:
     0 65537 2 3 4 5 6 7 8 9
To gather information about the shared memory in the trace file, navigate to your "user_dump_dest" directory and type in "ls -lt". This will order the files by modification date. Your trace file will be at the top of the file listing. Do a view on this file. Notice that the shared memory information is broken up into several areas. In the case of this example we have 5 areas. In each Area look up the "Shmid". In most cases, your SGA will all fit within one segment and therefore one Shmid.

...snipped...

Dump of unix-generic realm handle `/u01/app/oracle/product/8.0.5DBADB', flags = 00000000
 Area #0 `Fixed Size' containing Subareas 0-0
  Total size 000000000000be10 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      0        0        0 0000000080000000 0000000080000000
                              Subarea size     Segment size
                          000000000000c000 00000000043d9000
  00000000043d9000
 Area #1 `Variable Size' containing Subareas 1-1
  Total size 000000000330a000 Minimum Subarea size 00100000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      1        1        0 000000008000c000 000000008000c000
                              Subarea size     Segment size
                          0000000003400000 00000000043d9000
  00000000043d9000
 Area #2 `Database Buffers 1 of 1' containing Subareas 2-2
  Total size 0000000000fa0000 Minimum Subarea size 00002000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      2        2        0 000000008340c000 000000008340c000
                              Subarea size     Segment size
                          0000000000fa0000 00000000043d9000
  00000000043d9000
 Area #3 `Redo Buffers' containing Subareas 3-3
  Total size 000000000002a000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      3        3        0 00000000843ac000 00000000843ac000
                              Subarea size     Segment size
                          000000000002a000 00000000043d9000
  00000000043d9000
 Area #4 `Lock Manager' containing Subareas 4-4
  Total size 0000000000002000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      4        4        0 00000000843d6000 00000000843d6000
                              Subarea size     Segment size
                          0000000000002000 00000000043d9000
  00000000043d9000
 Area #5 `skgm overhead' containing Subareas 5-5
  Total size 0000000000001000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      5        5        0 00000000843d8000 00000000843d8000
                              Subarea size     Segment size
                          0000000000001000 00000000043d9000
  00000000043d9000
Dump of Solaris-specific skgm context

...snipped...
Table 2 - Example snip from oradebug trace file

Running "oradebug" in Oracle8i
Running "oradebug ipc" in an Oracle8i environment is similar to Oracle8 with the exception that both the shared memory and semaphore information is written to the "user_dump_dest" trace file. See 'Running "oradebug" in Oracle8' above. Table 2 - Example snip from oradebug trace file
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 <running system> as of Wed Feb  4 13:00:48 EST 2004
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


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 14-Apr-1998 00:00:00 EDT
Page Count: 99549