Applies to:
Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Information in this document applies to any platform.
What are the recommended steps to diagnose an ORA-20 - maximum number of processes (%s) exceeded?
Connections to the database have caused the current number of operating system processes associated with the Oracle Instance to exceeed the PROCESSES database parameter
The typical remedy to an ORA-20 - maximum number of processes (%s) exceeded ... is to increase the PROCESSES parameter ... This is usually only a temporary solution
Here are the steps to diagnose the cause of the ORA-20
1) The first step is to gather information about the processes that exist when the ORA-20 occurs
connect ... as sysdba
set markup html on
set pagesize 30
spool processes_sessions.html
select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program,
s.sid,
s.serial#,
p.spid
FROM v$session s,v$process p
WHERE p.addr=s.paddr
order by p.background desc;
spool off
exit;NOTE .. depending on version ... S.PORT may need to be removed from the query
2) Look for patterns in the output generated in step #1 (processes_sessions.html)SAMPLE OUTPUT
Here are the steps to diagnose the cause of the ORA-20
1) The first step is to gather information about the processes that exist when the ORA-20 occurs
connect ... as sysdba
set markup html on
set pagesize 30
spool processes_sessions.html
select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program,
s.sid,
s.serial#,
p.spid
FROM v$session s,v$process p
WHERE p.addr=s.paddr
order by p.background desc;
spool off
exit;NOTE .. depending on version ... S.PORT may need to be removed from the query
2) Look for patterns in the output generated in step #1 (processes_sessions.html)SAMPLE OUTPUT
V$PROCESS - OS USERNAME
|
TERMINAL
|
PROGRAM
|
V$SESSION - USERNAME
|
COMMAND
|
STATUS
|
SERVER
|
PROCESS
|
MACHINE
|
PORT
|
TERMINAL
|
PROGRAM
|
oracle
|
UNKNOWN
|
oracle@filnx10 (TNS V1-V3)
|
SYS
|
3
|
ACTIVE
|
DEDICATED
|
27140
|
filnx10
|
0
|
pts/2
|
sqlplus@filnx10 (TNS V1-V3)
|
oracle
|
UNKNOWN
|
oracle@filnx10 (TNS V1-V3)
| |||||||||
oracle
|
UNKNOWN
|
oracle@filnx10 (Q001)
|
0
|
ACTIVE
|
DEDICATED
|
24739
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (Q001)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (VKTM)
|
0
|
ACTIVE
|
DEDICATED
|
24534
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (VKTM)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (GEN0)
|
0
|
ACTIVE
|
DEDICATED
|
24538
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (GEN0)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (DIAG)
|
0
|
ACTIVE
|
DEDICATED
|
24540
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (DIAG)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (DBRM)
|
0
|
ACTIVE
|
DEDICATED
|
24542
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (DBRM)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (DIA0)
|
0
|
ACTIVE
|
DEDICATED
|
24544
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (DIA0)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (MMAN)
|
0
|
ACTIVE
|
DEDICATED
|
24546
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (MMAN)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (DBW0)
|
0
|
ACTIVE
|
DEDICATED
|
24548
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (DBW0)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (LGWR)
|
0
|
ACTIVE
|
DEDICATED
|
24550
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (LGWR)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (CKPT)
|
0
|
ACTIVE
|
DEDICATED
|
24552
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (CKPT)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (SMON)
|
0
|
ACTIVE
|
DEDICATED
|
24554
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (SMON)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (RECO)
|
0
|
ACTIVE
|
DEDICATED
|
24556
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (RECO)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (MMON)
|
0
|
ACTIVE
|
DEDICATED
|
24558
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (MMON)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (MMNL)
|
0
|
ACTIVE
|
DEDICATED
|
24560
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (MMNL)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (QMNC)
|
0
|
ACTIVE
|
DEDICATED
|
24576
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (QMNC)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (SMCO)
|
0
|
ACTIVE
|
DEDICATED
|
26415
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (SMCO)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (W000)
|
0
|
ACTIVE
|
DEDICATED
|
26417
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (W000)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (CJQ0)
|
0
|
ACTIVE
|
DEDICATED
|
24617
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (CJQ0)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (Q000)
|
0
|
ACTIVE
|
DEDICATED
|
24737
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (Q000)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (PMON)
|
0
|
ACTIVE
|
DEDICATED
|
24530
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (PMON)
| |
oracle
|
UNKNOWN
|
oracle@filnx10 (PSP0)
|
0
|
ACTIVE
|
DEDICATED
|
24532
|
filnx10
|
0
|
UNKNOWN
|
oracle@filnx10 (PSP0)
|
* If there are many V$SESSION.STATUS = INACTIVE ...
This means that there are many user sessions that have connected but are not doing anything
POSSIBLE SOLUTION : Enable dead connection detection (DCD) and user resource limits
A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes (Doc ID 601605.1)
* If there are many V$SESSION.PROGRAM (and possibly many V$SESSION.STATUS = INACTIVE) entries from the same program ...
POSSIBLE SOLUTIONS :
1) Examine the program (often a web server) to see if the program has a setting to abandon a connection after X number of seconds and reconnect (a respawn event)
If the program has such a setting ... a slowdown in either network or database performance should to be investigated ... as this would cause a timeout in the application ..
and thus a reconnect ... setting a longer 'timeout' in the application often will resolve future occurrences of ORA-20
2) Like the solution above ... setting DCD and Resource Limits ... often resolves these as well
* If there are many rows with data for V$PROCESS but no data for V$SESSION ...
This is a problem on the operating system side and such sessions will need to be killed manually with KILL -# (UNIX / LINUX) or ORAKILL (Windows)
This condition needs to be examined by an operating system expert (System Administrator or OS Vendor)
WHY? - When an Oracle session is terminated ... whether by the user logging out ... a kill session by a privileged user ... or even the user reaching a resource limit set in their profile
Oracle SMON (or PMON) will first clean up the database resources (rollback transactions ... release locks etc)
Oracle then will remove the entry in V$SESSION as the session is now 'cleaned up'
Oracle will then request that the operating system terminate any OS processes associated with the former Oracle Session
The Oracle Kernel cannot force the OS to terminate the processes .. this occurs at the OS level only ... all it can do is request that they be terminatedNOTE: This is a living document ... Oracle encourages comments as to other solutions / scenarios ... so please feel free to add a remark
NOTE:1050281.1 - Getting ORA-00020/ ORA-00018 With A High Number Of CPUs Regardless Of How High The Related DB Parameters Are Set.
NOTE:169706.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
NOTE:458527.1 - ORA-00020 on an ASM instance
NOTE:601605.1 - A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
NOTE:169706.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
NOTE:458527.1 - ORA-00020 on an ASM instance
NOTE:601605.1 - A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
No comments:
Post a Comment