This discussion is archived
14 Replies Latest reply: Jan 30, 2013 11:56 PM by Pinela RSS

ORA-00020: maximum number of processes ... exceeded

rsar001 Newbie
Currently Being Moderated
Hi All,
RDBMS Version 11.1.0.7 64x; Windows 2008 server 64x

I'm having a weird problem.. I replicated a database (copied from one server to another); I copied all datafiles, redo logs, init.ora ..etc. I recreated teh controlfiles and got the DB up and running..

I have 2 people connecting to the database and as soon as they start running anything, they'll start getting teh max number pf processes exceeded. THe production database is using 800 processes.. I changed this to 1000 and the same story again.. after few short mins the 2 users who are testing teh DB are getting the same message again (max number of processes exceeded)..

I noticed in teh laert log that I'm getting a lot of job errors (as we havn't set any of the other scripts on the new environment yet) and then right after we started getting the errors with the processes; here is the alert log:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file c:\oracle\admin\diag\rdbms\mydb01\mydb01\trace\mydb01_cjq0_5716.trc:

Fri Jan 08 16:01:28 2010
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file c:\oracle\admin\diag\rdbms\mydb01\mydb01\trace\mydb01_cjq0_5716.trc:

Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file c:\oracle\admin\diag\rdbms\mydb01\mydb01\trace\mydb01_cjq0_5716.trc:

Fri Jan 08 16:02:58 2010
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process m000 submission failed with error = 20
Fri Jan 08 16:03:58 2010
Process m000 submission failed with error = 20
Fri Jan 08 16:04:59 2010
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process m000 submission failed with error = 20
Fri Jan 08 16:06:59 2010
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process m000 submission failed with error = 20
So, is this due to the fact that the jobs are failing?
Any ideas what can I do/check to resolve this?

Thanks

Edited by: user5545873 on Jan 11, 2010 9:38 AM

Edited by: user5545873 on Jan 11, 2010 9:39 AM
  • 1. Re: ORA-00020: maximum number of processes ... exceeded
    sb92075 Guru
    Currently Being Moderated
    SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;

    post results from SQL above.
  • 2. Re: ORA-00020: maximum number of processes ... exceeded
    rsar001 Newbie
    Currently Being Moderated
    SQL> SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM
    
    MACHINE                                  PROGRAM
    ---------------------------------------- --------------------------------------------------
    db_srv_1                                 ORACLE.EXE (SMON)
    db_srv_1                                 ORACLE.EXE (CKPT)
    db_srv_1                                 ORACLE.EXE (DBRM)
    db_srv_1                                 ORACLE.EXE (SMCO)
    db_srv_1                                 ORACLE.EXE (VKTM)
    db_srv_1                                 ORACLE.EXE (PMON)
    db_srv_1                                 ORACLE.EXE (q001)
    db_srv_1                                 ORACLE.EXE (RECO)
    db_srv_1                                 ORACLE.EXE (LGWR)
    db_srv_1                                 ORACLE.EXE (DIA0)
    db_srv_1                                 ORACLE.EXE (MMON)
    db_srv_1                                 OMS
    db_srv_1                                 ORACLE.EXE (W000)
    db_srv_1                                 ORACLE.EXE (q000)
    db_srv_1                                 ORACLE.EXE (PSP0)
    EBCVIC04\db_srv_1                        sqlplus.exe
    db_srv_1                                 ORACLE.EXE (QMNC)
    db_srv_1                                 ORACLE.EXE (CJQ0)
    db_srv_1                                 ORACLE.EXE (DBW0)
    db_srv_1                                 ORACLE.EXE (MMNL)
    db_srv_1                                 ORACLE.EXE (MMAN)
    db_srv_1                                 ORACLE.EXE (DIAG)
    
    22 rows selected.
    
    SQL>
    I actually noticed that the OracleJobSchedulerMYDB01 was not started, could this be a reason for not freeing up job processes?

    Thanks

    Edited by: user5545873 on Jan 11, 2010 9:54 AM
  • 3. Re: ORA-00020: maximum number of processes ... exceeded
    729338 Expert
    Currently Being Moderated
    Hi,

    What is the value of job_queue_processes in your database. Also what does the trace file contains?

    Regards

    Edited by: skvaish1 on Jan 11, 2010 12:55 PM
  • 4. Re: ORA-00020: maximum number of processes ... exceeded
    rsar001 Newbie
    Currently Being Moderated
    SQL> show parameter job_queue_processes
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------------
    job_queue_processes                  integer     10
    SQL>
  • 5. Re: ORA-00020: maximum number of processes ... exceeded
    sb92075 Guru
    Currently Being Moderated
    THe production database is using 800 processes.. I changed this to 1000 and the same story again.
    please use CUT & PASTE to provide proof above is true.
  • 6. Re: ORA-00020: maximum number of processes ... exceeded
    rsar001 Newbie
    Currently Being Moderated
    I just noted the following:

    SQL> r
      1* SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM
    
    MACHINE                        PROGRAM                          COUNT(*)
    ------------------------------ ------------------------------ ----------
    EIS08DB1                       ORACLE.EXE (SMON)                       1
    EIS08DB1                       ORACLE.EXE (CKPT)                       1
    EIS08DB1                       ORACLE.EXE (DBRM)                       1
    EIS08DB1                       ORACLE.EXE (SMCO)                       1
    EIS08DB1                       ORACLE.EXE (VKTM)                       1
    EIS08DB1                       ORACLE.EXE (PMON)                       1
    EIS08DB1                       ORACLE.EXE (J000)                       1
    EIS08DB1                       ORACLE.EXE (q001)                       1
    EIS08DB1                       ORACLE.EXE (RECO)                       1
    EIS08DB1                       ORACLE.EXE (LGWR)                       1
    EIS08DB1                       ORACLE.EXE (DIA0)                       1
    EIS08DB1                       ORACLE.EXE (MMON)                       1
    EIS08DB1                       ORACLE.EXE (J001)                       1
    EIS08DB1                       OMS                                   632
    EIS08DB1                       ORACLE.EXE (W000)                       1
    EIS08DB1                       ORACLE.EXE (q000)                       1
    EIS08DB1                       ORACLE.EXE (PSP0)                       1
    EBCVIC04\EIS08DB1              sqlplus.exe                             1
    EIS08DB1                       ORACLE.EXE (QMNC)                       1
    EIS08DB1                       ORACLE.EXE (CJQ0)                       1
    EIS08DB1                       ORACLE.EXE (DBW0)                       1
    EIS08DB1                       ORACLE.EXE (MMNL)                       1
    EIS08DB1                       ORACLE.EXE (MMAN)                       1
    EIS08DB1                       ORACLE.EXE (DIAG)                       1
    
    24 rows selected.
    what is OMS?? and why is it 632??

    Thanks
  • 7. Re: ORA-00020: maximum number of processes ... exceeded
    sb92075 Guru
    Currently Being Moderated
    what is OMS??
    Oracle Management Server
    http://download.oracle.com/docs/html/A88769_01/ch6.htm

    and why is it 632??
    Many, many sessions

    now you need to determine how/why.
  • 8. Re: ORA-00020: maximum number of processes ... exceeded
    rsar001 Newbie
    Currently Being Moderated
    >
    Many, many sessions
    >

    What should be an ideal value for this?

    >
    now you need to determine how/why.
    >

    Any pointers of where should I start looking? or what's usually the cause for such a high number?

    Also, I looked up the OMS in production and it's 4!! so how come its 632 in the clone environment?? any specific parameter that I should be setting (or un-setting).

    Thanks

    Edited by: user5545873 on Jan 11, 2010 12:14 PM
  • 9. Re: ORA-00020: maximum number of processes ... exceeded
    sb92075 Guru
    Currently Being Moderated
    I looked up the OMS in production and it's 4!!
    I don't have any. It is not required at all.
    so how come its 632 in the clone environment??
    Something is different.
    What are they trying or doing?
    It is YOUR system & we have no idea what has been done on it or to it.
    any specific parameter that I should be setting (or un-setting).
    To be determined.
  • 10. Re: ORA-00020: maximum number of processes ... exceeded
    Pinela Journeyer
    Currently Being Moderated
    OMS is Oracle Managed Server.
    These appear if you have Grid Control (for example).
    I also have encountered such a situation. Too many OMS connections that are blocking new connections.

    DB is 11.2.0.3, GC agente is 11.1 and GC is 11.1

    How did you fix this?
    Anybody got any idea?

    thanks.
    br,
    jpinela.
  • 11. Re: ORA-00020: maximum number of processes ... exceeded
    884645 Newbie
    Currently Being Moderated
    Was there a solution for this ? Can you please post the solution or workaround ?

    Thanks!
  • 12. Re: ORA-00020: maximum number of processes ... exceeded
    sb92075 Guru
    Currently Being Moderated
    user4590420 wrote:
    Was there a solution for this ? Can you please post the solution or workaround ?

    Thanks!
    shutdown, disable, turnoff OMS
  • 13. Re: ORA-00020: maximum number of processes ... exceeded
    baskar.l Pro
    Currently Being Moderated
    Hi,

    Lot of oc4j connections from OMS? There are few bugs of unclosed oc4j connections . Please check the same in MOS.

    Baskar.l
  • 14. Re: ORA-00020: maximum number of processes ... exceeded
    Pinela Journeyer
    Currently Being Moderated
    Hi,

    well, my problem was that someone started EM on a machine managed by GC.
    The connections that we were seeing where from EM.

    Just stopped dbconsole and everything was fine.

    BR,
    jpinela.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points