Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Some useful Unix Commands Linux/Unix
  • Another export with Query Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • Free conference number from http://www.freeconference.com Oracle
  • Very clear article about oracle dataguard Oracle
  • Oracle Standby Database Library Index from Metalink Oracle
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • Gathering statistics with DBMS_STATS Oracle
  • _B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade Oracle
  • note id 373303.1 Linux/Unix
  • Process Map for CPU and Memory for OS processes Linux/Unix
  • Changing the Global Database Name Oracle
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ Oracle
  • PLSQL Table Syntax 2 Oracle
  • rm_backup_arch_file.ksh Linux/Unix

oradebug ipcrm ipcs

Posted on 21-Nov-2006 By Admin No Comments on oradebug ipcrm ipcs

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.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 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

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 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

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

Oracle, SQL scripts

Post navigation

Previous Post: How to sort list of files on basis of their sizes.
Next Post: xargs use

Related Posts

  • currwait.sql Oracle
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Oracle Statspack survival Guide Oracle
  • configUOCIOTTO.ora Oracle
  • To see how much time or progress of long transaction Oracle
  • Distributed Transaction Troubleshooting. Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • oracle Dba site Oracle
  • initUOCIOTTO.ora Oracle
  • proc.sql Oracle
  • find_longsql.sql Oracle
  • How To Limit The Access To The Database So That Only One User Per Schema Are Connected (One Concurrent User Per Schema) Oracle
  • Reading parameter file and printing Linux/Unix
  • Good Doc 28-JUN-2006 Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme