Oracle.exe (SHAD) process keeps increasing

Question

I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener does not currently know of service requested in connect descriptor error when the limit is reached. I tried increasing the processes limit to 300. But after a while the database will still reach this limit and have the same error. When I tried querying

select program, count(*) from v$process group by program order by count(*) desc;

I found that almost all of the processes are owned by Oracle.exe (SHAD)

PROGRAM                                                            COUNT(*)
---------------------------------------------------------------- ----------
ORACLE.EXE (SHAD)                                                       135
ORACLE.EXE (J001)                                                         1
ORACLE.EXE (Q001)                                                         1
ORACLE.EXE (PMON)                                                         1
ORACLE.EXE (GEN0)                                                         1
PSEUDO                                                                    1
ORACLE.EXE (LGWR)                                                         1
ORACLE.EXE (Q000)                                                         1
ORACLE.EXE (W000)                                                         1
ORACLE.EXE (DBRM)                                                         1
ORACLE.EXE (DBW0)                                                         1
ORACLE.EXE (DIAG)                                                         1
ORACLE.EXE (DIA0)                                                         1
ORACLE.EXE (MMAN)                                                         1
ORACLE.EXE (SMON)                                                         1
ORACLE.EXE (RECO)                                                         1
ORACLE.EXE (SMCO)                                                         1
ORACLE.EXE (W001)                                                         1
ORACLE.EXE (J000)                                                         1
ORACLE.EXE (PSP0)                                                         1
ORACLE.EXE (MMNL)                                                         1
ORACLE.EXE (D000)                                                         1
ORACLE.EXE (S000)                                                         1
ORACLE.EXE (CJQ0)                                                         1
ORACLE.EXE (MMON)                                                         1
ORACLE.EXE (QMNC)                                                         1
ORACLE.EXE (VKTM)                                                         1
ORACLE.EXE (CKPT)                                                         1

Any idea what's causing this process spam and how to fix it? Note, I found many users reporting the issue in the internet, see for example:

• https://forums.oracle.com/forums/thread.jspa?threadID=839381
• https://forums.oracle.com/forums/thread.jspa?threadID=969909
• https://forums.oracle.com/forums/thread.jspa?threadID=2409737

But so far I haven't found any solution or even a sensible theory

Answer 1

If you're on Windows and have DCD (dead connection detection) enabled then this could be relevant to your case. There is a note on Oracle support that talks about poor implementation of DCD on windows leaving behind a bunch of shadow processes. Without going into details as I don't think I can republish that info here, the solution was to disable DCD. So keep a monitor on the number of shad processes and disable DCD in sqlnet.ora. Fairly quick and painless to test if that is the cause..

Answer 2

By default, Oracle will expire password after 180 days, so a highly probable cause of the high number of SHAD process is due to some connections using an expired password. For me, that connections was made from a web service constantly trying to make a connection.

To fix the problem, you will need to update the password. If you want to stop password from expiring, you can follow the instruction from this blog http://www.dbas-oracle.com/2011/06/ora-28001-password-has-expired.html

Answer 3

When you configure your Oracle Database instance to use the Dedicated connections, then you will have an ORACLE.EXE (SHAD) entry in the v$process view for each process that opens up a session with your instance.

So long as new programs connect to your Oracle instance in the Dedicated Server configuration, then the number of ORACLE.EXE (SHAD) processes will keep on increasing.

Analysis

You can run the following statement to retrieve an overview of all the non-background processes that are running on an Oracle instance:

COLUMN "Session Program" format a20
Column "Process Program" format a20
SET pages 50
SET lines 230
SELECT COUNT(a.SID),    
    a.server, 
    a.program "Session Program", 
    a."TYPE",
    b.PROGRAM "Process Program"
FROM v$session a JOIN v$process b 
    ON a.paddr = b.addr
WHERE a."TYPE" != 'BACKGROUND'
GROUP BY a.server, 
         a.program, 
         a."TYPE", 
         b.program;

This will return something like this:

COUNT(A.SID) SERVER    Session Program      TYPE       Process Program     
------------ --------- -------------------- ---------- --------------------
         199 DEDICATED someprog.exe         USER       ORACLE.EXE (SHAD)   
           3 NONE      someprog.exe         USER       ORACLE.EXE (D000)   
           1 DEDICATED Toad.exe             USER       ORACLE.EXE (SHAD)   

3 rows selected.

Shared vs. Dedicated Server

All dedicated connections will have a corresponding ORACLE.EXE (SHAD) process on the server side.

If you have a client that is using the SHARED server connection setting, then these will be listed as NONE (session side) and as (Dxxx) (process side). The Dxxx is the number of the dispatcher, that is waiting for client requests on server side.

^{Reference: About Oracle Database Background Processes (Oracle | Docs)}

So even though it looks like these shadow processes aren't there for a good reason, they actually are there and exist for a very good reason, as they are responsible for the dedicated client connection from the someprog.exe to the Oracle server's ORACLE.EXE (SHAD) process. They keep track of client requests.

Shared Server Connections

If you want to get rid of these processes, because you are running out of resources, then you start thinking about configuring your Oracle database instance and clients to use the Shared Server features.

^{Reference: Configuring Oracle Database for Shared Server (Oracle | Docs)}

Enabling this feature will link all client connection processes to an Oracle Dispatcher (Dxxx) and the ORACLE.EXE (SHAD) processes should eventually vanish.

In a Shared Server setting the dispatcher becomes responsible for a number of client connections (pool) and this will result in less server resources being used (up) by client processes requiring a corresponding Oracle process/session.