Tag Archives: oradebug

delete unused shared memory segments from an Oracle instance

Once upon a time, a dba issues some kill -9 to clean up dying database processes. Or the database instance crashes. This will left some shared memory segments. Note 68281.1 describe how to remove them on a server with multiple databases.

First, list the ipc process


$ ipcs
IPC status from /dev/mem as of Mon Nov 14 11:28:58 CET 2011
T        ID     KEY        MODE       OWNER    GROUP
Message Queues:
q         0 0x4107001c -Rrw-rw----     root   printq

Shared Memory:
m         0 0x7800006f --rw-rw-rw- itmuser1 itmusers
m         1 0x78000070 --rw-rw-rw- itmuser1 itmusers
m   5242882 0x41d2ba80 --rw-r-----   oracle      dba
m  99614723 0xb0d4d164 --rw-rw----   oracle      dba
m  12582917 0xb84cbc28 --rw-rw----   oracle      dba
m  79691782 0x1058873f --rw-------   oracle      dba
m 638582792 0x78000382 --rw-rw-rw-     root   system
m 218103817 0x780003b7 --rw-rw-rw-     root   system
Semaphores:
s         1 0x6202c477 --ra-r--r--     root   system
s   6291461 0x0102c2d8 --ra-------     root   system
s         6 0xa100004b --ra-ra-ra-     root   system

Get a list of the running databases


$ ps -ef | grep pmon | grep -v grep
  oracle  483334       1   1   Aug 16      -  6:46 ora_pmon_db03
  oracle 1253476       1   0   Oct 31      -  2:00 ora_pmon_db01
  oracle 2298042       1   0   Sep 05      - 11:07 ora_pmon_db02

Then, for each database, get the ipc information
$ export ORACLE_SID=db01
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/admin/db01/udump/db01_ora_2625574.trc
5242882         0x41d2ba80
$ export ORACLE_SID=db02
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db02a/db02/trace/db02_ora_2441408.trc
99614723        0xb0d4d164
$ export ORACLE_SID=db03
$ sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
$ awk '/Shared Memory:/{getline;getline;print}' /u01/app/oracle/diag/rdbms/db03b/db03/trace/db03_ora_2617416.trc
12582917        0xb84cbc28

Compare it with the first list, and if you are absolutely sure to do what you are doing, remove the oracle segments that are not assigned to any database instance with ipcrm. If possible try first to figure out where they come from and do a shutdown abort of the not-correctly-stopped database.

Ok, with ipcrm


$ ipcrm -m 79691782

I removed the segment that apparently does not relate to any running instance

This could help you if you are really forced to remove some shared memory segments and you cannot afford shutting down other databases.

List events in session, process or system

There is a new command in 11g to display the current events, which is oradebug eventdump.

For instance :

SQL> alter session set events '10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3';

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace level=12
942 trace name ERRORSTACK level 3

Read metalink note 436036.1

In 10g and before, the command was oradebug dump events 1 and the list was dumped in a trace file, 11g directly outputs to the console.

Note there is no backward compatibility with unsupported tools like oradebug.
In 11g you will get an ORA-76 with dump events

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 1
ORA-00076: dump EVENTS not found
$ oerr ora 76
00076, 00000, "dump %s not found"
// *Cause:  An attempt was made to invoke a dump that does not exist.
// *Action: Type DUMPLIST to see the list of available dumps.