Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • find_du.ksh to find # of files, their sizes in current folder and its subdolder Linux/Unix
  • Removing Ctrl-M from end of line using vi Linux/Unix
  • myfile Oracle
  • scripts to take listener.log backup Linux/Unix
  • Generating XML from SQLPLUS Oracle
  • Shuffle an array PHP/MYSQL/Wordpress
  • Rownum with Order by Oracle
  • Proc code Oracle
  • online_bkup.sql Oracle
  • column level grant syntax Oracle
  • In Addition to previous note, following grants needed on PERFSTAT user. Oracle
  • Alter procedure auditing Oracle
  • Looping for remote servers and find its database from oratab file. Linux/Unix
  • restarting network in linux Linux/Unix
  • Test Case for Inserting Multiple (2.3 Million rows in 26 Seconds) Oracle

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

  • plan10g.sql Oracle
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ Oracle
  • How to start CRS manually Oracle
  • Query to Generate aggregate on every 30 mins. Oracle
  • column level grant syntax Oracle
  • fkwoind.sql fkwoindex.sql 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 (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Follwoing korn shell is used to move files from source folder to destination folder and gzip it in destination folder. mv_iotto.ksh Linux/Unix
  • v$event_name Oracle
  • Difference between SYNC and AFFIRM Oracle
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • First Entry in RAC Oracle
  • New OFA for 11g Oracle
  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • How to Make Trace Files Created by Oracle Readable by All Users ? Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme