On Windows based Oracle installations, if you hit an issue whereby you see that processes/sessions keep increasing unabated, and you keep receiving “ORA-00020: maximum number of processes exceeded” error while connecting to the database and increasing the value of parameter “processes” remains insufficient always,
it means there is some application/program/module which has some bug or some issue with it which is making it to create more and more sessions with the database without closing the previous sessions. If you query v$process view, it would result something as follows
it means there is some application/program/module which has some bug or some issue with it which is making it to create more and more sessions with the database without closing the previous sessions. If you query v$process view, it would result something as follows
SQL> select count(*),program from v$process group by program order by 1 desc;
COUNT(*) PROGRAM
---------- ----------------------------------------------------------------
800 ORACLE.EXE (SHAD)
1 ORACLE.EXE (QMNC)
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 (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 (PSP0)
1 ORACLE.EXE (MMNL)
1 ORACLE.EXE (D000)
1 ORACLE.EXE (S000)
1 ORACLE.EXE (CJQ0)
1 ORACLE.EXE (MMON)
1 ORACLE.EXE (VKTM)
1 ORACLE.EXE (CKPT)
|
If you query v$session, you may easily find which application/user is having this many sessions.
SQL> select a.username,a.program from v$session a, v$process b where a.paddr=b.addr and b.program='ORACLE.EXE (SHAD)' order by username;
USERNAME PROGRAM
------------------------------ ----------------------------------------------------------------
SYSMAN OMS
SYSMAN OMS
SYSMAN OMS
SYSMAN OMS
SYSMAN OMS
…
…
…
|
As you can see above (a real time scenario which I faced recently), there are zillions of sessions coming from the OEM Database Control with user SYSMAN. It actually meant that there is something wrong with the OEM configuration as OEM is not supposed to launch this many sessions with the database.
For this particular scenario, I actually locked the user SYSMAN (alternatively the OEM Database Control can also be de-configured) to stop these unwanted sessions. I did this because OEM Database Control was not required for this database. And problem resolved.
If you face similar issue where sessions are coming from OEM, but you can’t lock SYSMAN or permanently de-configure the OEM, you may think of de-configuring the OEM, dropping the repository, and then re-configuring the OEM. If problem persist, you might be hitting some bug and should raise a case with Oracle support.
No comments:
Post a Comment