3

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:

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

7
  • 3
    The SHAD processes are created when you login to Oracle - they're just normal background processes. Look in v$session to see if you can find what is responsible. From that you should be able to establish the machine(s) that the logins are coming from along with the usernames. – Philᵀᴹ Aug 22 '12 at 4:20
  • 1
    in the first thread you mention the article "DB : High Number of Oracle Processes (Doc ID 660985.1) " is mentionted. This is the bug BKW is talking about. The content of the article is reproduced in the link supplied by @phil. so it woul be nice if you try this workaround and report to us the results – miracle173 Aug 22 '12 at 4:48
  • @miracle173 what kind of article is it, and any idea how to get my hands on the original document? – Louis Rhys Aug 22 '12 at 7:14
  • @Phil when I join with v$session, it seems that the responsible session is owned by OMS and running under SYSMAN user – Louis Rhys Aug 22 '12 at 7:39
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..

1
  • You can post the metalink bub/document ID without violating any support/license agreement – a_horse_with_no_name Aug 22 '12 at 6:15
1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.