Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
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. –  FreshPhilOfSO Aug 22 '12 at 4:20
    
    
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
show 2 more comments

1 Answer

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..

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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