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
No comments:
Post a Comment