14 Replies Latest reply: Jan 31, 2013 1:56 AM by Pinela RSS

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

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