Problem with Processes increasing uncontrollably
453869Dec 18 2008 — edited Jul 29 2010
First the Environment: Development server running Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production with the Partitioning, OLAP, Data Mining and Real Application Testing options on a Dell PowerEdge 2950 Server.
I apologize up front for the long post but I thought that providing the data below would be useful. I created a database about a month ago and it has been working without problems until yesterday. This is a development/test database. Last week I started experimenting with the DataPump utilites because I need to move the meta-data from 4 schemas on another server to this one. Everything was fine until yesterday when I did a full import (previously I had been using the Schema= parameter). Shortly after that import I was unable to connect to the instance using either SQL*Plus or Toad. I figured there was a small glitch and so I rebooted the server and I could connect. 30 minutes later and the problem happened again. This time I just restarted the instance services and I could connect again. After another 30 min (approx) period I couldn't connect. Based on the error ("Error - No Connection and Oracle error 20) I did some research and it appeared to be related to having too many processes running (basically exceeding the number set in the "processes" parameter found in the v$parameter view.
What is happening is after a restart of the instance I have about XX processes and then over time (about 30 minutes) more and more processes are started automatically until the limit is reached. The multiplying processes appear to be primarily SHAD processes.
I ran the following queries:
{color:#3366ff}select name, value* from v$parameter where name = 'processes';{color}
*NAME VALUE*
processes 150
{color:#3366ff}select * from v$process{color}
It returned 35 rows
*PID SPID USERNAME SERIAL# TERMINAL PROGRAM*
1 NULL NULL 0 NULL PSEUDO
2 2144 SYSTEM 1 VISTA-DB1 ORACLE.EXE (PMON)
3 2484 SYSTEM 1 VISTA-DB1 ORACLE.EXE (VKTM)
4 1132 SYSTEM 1 VISTA-DB1 ORACLE.EXE (DIAG)
5 2312 SYSTEM 1 VISTA-DB1 ORACLE.EXE (DBRM)
6 3000 SYSTEM 1 VISTA-DB1 ORACLE.EXE (PSP0)
7 2148 SYSTEM 2 VISTA-DB1 ORACLE.EXE (MMAN)
8 3832 SYSTEM 1 VISTA-DB1 ORACLE.EXE (DIA0)
9 3504 SYSTEM 1 VISTA-DB1 ORACLE.EXE (DBW0)
10 2448 SYSTEM 1 VISTA-DB1 ORACLE.EXE (LGWR)
11 1996 SYSTEM 1 VISTA-DB1 ORACLE.EXE (CKPT)
12 2996 SYSTEM 1 VISTA-DB1 ORACLE.EXE (SMON)
13 3980 SYSTEM 1 VISTA-DB1 ORACLE.EXE (RECO)
14 1976 SYSTEM 1 VISTA-DB1 ORACLE.EXE (MMON)
15 3144 SYSTEM 1 VISTA-DB1 ORACLE.EXE (MMNL)
16 3860 SYSTEM 1 VISTA-DB1 ORACLE.EXE (D000)
17 2104 SYSTEM 1 VISTA-DB1 ORACLE.EXE (S000)
18 3436 SYSTEM 2 VISTA-DB1 ORACLE.EXE (q000)
19 2292 SYSTEM 2 VISTA-DB1 ORACLE.EXE (P000)
20 1868 SYSTEM 2 VISTA-DB1 ORACLE.EXE (P001)
21 3404 SYSTEM 1 VISTA-DB1 ORACLE.EXE (P002)
22 3116 SYSTEM 1 VISTA-DB1 ORACLE.EXE (P003)
23 2200 SYSTEM 1 VISTA-DB1 ORACLE.EXE (P004)
24 2620 SYSTEM 1 VISTA-DB1 ORACLE.EXE (P005)
25 1712 SYSTEM 1 VISTA-DB1 ORACLE.EXE (P006)
26 2404 SYSTEM 1 VISTA-DB1 ORACLE.EXE (FBDA)
27 4020 SYSTEM 1 VISTA-DB1 ORACLE.EXE (SMCO)
28 3088 SYSTEM 1 VISTA-DB1 ORACLE.EXE (QMNC)
29 524 SYSTEM 1 VISTA-DB1 ORACLE.EXE (W000)
30 3620 SYSTEM 2 VISTA-DB1 ORACLE.EXE (SHAD)
31 3856 SYSTEM 2 VISTA-DB1 ORACLE.EXE (SHAD)
32 3636 SYSTEM 3 VISTA-DB1 ORACLE.EXE (SHAD)
33 3212 SYSTEM 2 VISTA-DB1 ORACLE.EXE (SHAD)
34 2964 SYSTEM 2 VISTA-DB1 ORACLE.EXE (SHAD)
35 3028 SYSTEM 2 VISTA-DB1 ORACLE.EXE (SHAD)
{color:#3366ff}select count(*), program{color}
{color:#3366ff}from v$process{color}
{color:#3366ff}group by program{color}
You will notice in the 30 seconds or so it took me to run this query the number of SHAD processes has jumped to 9 from 6
*COUNT(*) PROGRAM*
1 ORACLE.EXE (VKTM)
1 ORACLE.EXE (CKPT)
1 ORACLE.EXE (P001)
1 ORACLE.EXE (P003)
1 ORACLE.EXE (FBDA)
1 ORACLE.EXE (PMON)
1 PSEUDO
1 ORACLE.EXE (LGWR)
1 ORACLE.EXE (P005)
1 ORACLE.EXE (W000)
1 ORACLE.EXE (DBRM)
1 ORACLE.EXE (DBW0)
1 ORACLE.EXE (DIAG)
1 ORACLE.EXE (MMAN)
1 ORACLE.EXE (DIA0)
1 ORACLE.EXE (SMON)
1 ORACLE.EXE (RECO)
1 ORACLE.EXE (SMCO)
1 ORACLE.EXE (q001)
1 ORACLE.EXE (PSP0)
1 ORACLE.EXE (MMNL)
1 ORACLE.EXE (D000)
1 ORACLE.EXE (S000)
1 ORACLE.EXE (q000)
1 ORACLE.EXE (P000)
1 ORACLE.EXE (MMON)
1 ORACLE.EXE (P004)
9 ORACLE.EXE (SHAD)
1 ORACLE.EXE (P002)
1 ORACLE.EXE (P006)
1 ORACLE.EXE (QMNC)
{color:#3366ff}select count(*), username{color}
{color:#3366ff}from v$session{color}
{color:#3366ff}group by username{color}
*COUNT(*) USERNAME*
20 NULL
8 SYSMAN
3 DBSNMP
2 SYS
{color:#3366ff}select s.sid, p.pid, s.program "Client", p.program "Server"{color}
{color:#3366ff}from v$session s full outer join v$process p{color}
{color:#3366ff} on (p.addr = s.paddr){color}
{color:#3366ff}order by p.pid{color}
*SID PID Client Server*
NULL 1 NULL PSEUDO
169 2 ORACLE.EXE (PMON) ORACLE.EXE (PMON)
168 3 ORACLE.EXE (VKTM) ORACLE.EXE (VKTM)
167 4 ORACLE.EXE (DIAG) ORACLE.EXE (DIAG)
166 5 ORACLE.EXE (DBRM) ORACLE.EXE (DBRM)
165 6 ORACLE.EXE (PSP0) ORACLE.EXE (PSP0)
163 7 ORACLE.EXE (MMAN) ORACLE.EXE (MMAN)
164 8 ORACLE.EXE (DIA0) ORACLE.EXE (DIA0)
162 9 ORACLE.EXE (DBW0) ORACLE.EXE (DBW0)
161 10 ORACLE.EXE (LGWR) ORACLE.EXE (LGWR)
160 11 ORACLE.EXE (CKPT) ORACLE.EXE (CKPT)
159 12 ORACLE.EXE (SMON) ORACLE.EXE (SMON)
158 13 ORACLE.EXE (RECO) ORACLE.EXE (RECO)
157 14 ORACLE.EXE (MMON) ORACLE.EXE (MMON)
156 15 ORACLE.EXE (MMNL) ORACLE.EXE (MMNL)
NULL 16 NULL ORACLE.EXE (D000)
NULL 17 NULL ORACLE.EXE (S000)
155 18 ORACLE.EXE (q000) ORACLE.EXE (q000)
NULL 19 NULL ORACLE.EXE (P000)
NULL 20 NULL ORACLE.EXE (P001)
NULL 21 NULL ORACLE.EXE (P002)
NULL 22 NULL ORACLE.EXE (P003)
NULL 23 NULL ORACLE.EXE (P004)
NULL 24 NULL ORACLE.EXE (P005)
NULL 25 NULL ORACLE.EXE (P006)
151 26 ORACLE.EXE (FBDA) ORACLE.EXE (FBDA)
154 27 ORACLE.EXE (SMCO) ORACLE.EXE (SMCO)
149 28 ORACLE.EXE (QMNC) ORACLE.EXE (QMNC)
150 29 ORACLE.EXE (W000) ORACLE.EXE (W000)
141 30 OMS ORACLE.EXE (SHAD)
140 31 OMS ORACLE.EXE (SHAD)
139 32 toad.exe ORACLE.EXE (SHAD)
138 33 emagent.exe ORACLE.EXE (SHAD)
142 34 OMS ORACLE.EXE (SHAD)
137 35 emagent.exe ORACLE.EXE (SHAD)
143 36 emagent.exe ORACLE.EXE (SHAD)
136 37 OMS ORACLE.EXE (SHAD)
145 38 ORACLE.EXE (q001) ORACLE.EXE (q001)
135 39 OMS ORACLE.EXE (SHAD)
134 40 OMS ORACLE.EXE (SHAD)
133 41 OMS ORACLE.EXE (SHAD)
132 42 OMS ORACLE.EXE (SHAD)
131 43 toad.exe ORACLE.EXE (SHAD)
I started running these queries at 6:41 pm I then re-ran the select * from v$process at 6:54 pm and had 115 rows ( I will spare you the listing of all the rows). It appears that all the new processes are OMS ORACLE.EXE (SHAD) processes.
*Now for the question* - What would cause this continuous spawning of OMS processes? Is there something that happened because I did a full import?
Thank you very much
Rick Anderson