8 Replies Latest reply on Nov 21, 2011 5:28 PM by JohnJomon

    ORA-00020: maximum number of processes (%s) exceeded

    Merlin128
      nobody was able to log into the database. when I tried 'sqlplus / as sysdba' I got : ORA-00020: maximum number of processes 1000 exceeded

      database is 11.2.0.2 with patch 12

      I've bounced the database and increased the processes..

      my question is.. is there a way to tell what the processes were? what machine they were coming from? in a log somewhere..
      Since I was unable to log in, I couldn't tell what had happened.. who was causing it..

      if it happens again.. is there a way to log it?

      also, this machine has DataGuard to a standby system.. is it possible for something to go wrong with dataguard and cause this?
        • 1. Re: ORA-00020: maximum number of processes (%s) exceeded
          sb92075
          Merlin128 wrote:
          nobody was able to log into the database. when I tried 'sqlplus / as sysdba' I got : ORA-00020: maximum number of processes 1000 exceeded

          login before limit has been reached
          • 2. Re: ORA-00020: maximum number of processes (%s) exceeded
            901089
            Number of processes tells how many os(operating system) processes created for session. Keeping 2000 should be better.
            At the os level we can see this using ps -ef command.
            • 3. Re: ORA-00020: maximum number of processes (%s) exceeded
              Mich Talebzadeh
              As stated at OS level you can do a ps -ef|grep oracle<SID>|wc -l

              Something like below. It is case SID is mydb
               ps -ef | grep oraclemydb|grep -v grep
              oracle   10902     1  0 10:55 ?        00:00:00 oraclemydb (LOCAL=NO)
              oracle   10963 10962  0 10:57 ?        00:00:00 oraclemydb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
              To find out the number of connections (server side processes) do
              ps -ef | grep oraclemydb|grep -v grep|wc -l
              2
              In general if you are running oracle on UNIX, you should have a cron that runs once every 2 minutes etc and does the monitoring of Oracle processes at OS level piping the output to a file with a timestamp. A simple korn shell will do it.

              Hope this helps,

              Mich
              • 4. Re: ORA-00020: maximum number of processes (%s) exceeded
                user8886876
                I've bounced the database and increased the processes...
                Instead of bouncing database , we could kill one or two "LOCAL=NO" process from OS then login as sysdba to troubleshoot further.

                Thanks.
                • 5. Re: ORA-00020: maximum number of processes (%s) exceeded
                  VenkatB
                  >
                  my question is.. is there a way to tell what the processes were? what machine they were coming from? in a log somewhere..
                  Since I was unable to log in, I couldn't tell what had happened.. who was causing it..

                  if it happens again.. is there a way to log it?

                  also, this machine has DataGuard to a standby system.. is it possible for something to go wrong with dataguard and cause this?
                  >

                  You have only treated the symptom and not fixed the cause. As fellow members said, you could kill some "non-critical" processes and log in to the database.

                  But you will have to find out why there were so many processes in place. I am sure you might have set the definite number of processes after your planning before you went live. If you suddenly see too many processes, you will have to find out why and where they are coming from. Also check if there are any rogue processes.

                  To find out if there are any processes which have no sessions attached

                  select spid from v$process where addr not in (select paddr from v$session);

                  To answer your question, data guard shouldn't be affected by this. Nor would dataguard cause this issue in primary.

                  Fix this processes issue before it's too late. Otherwise, there will be a situation when you will have to keep increasing the processes until you hit the OS limit

                  Regards
                  Venkat
                  • 6. Re: ORA-00020: maximum number of processes (%s) exceeded
                    Merlin128
                    fyi.. the database is on Windows 2008 x64..
                    so the unix process commands dont work..

                    I've been watching procmon and procexp but not sure what I am looking for.. there is only 2 oracle.exe processes..
                    • 7. Re: ORA-00020: maximum number of processes (%s) exceeded
                      sb92075
                      SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;

                      One, tedious, way to determine culprit would be to parse & count records within listener.log file
                      to determine from where the greatest number of connections originate.
                      • 8. Re: ORA-00020: maximum number of processes (%s) exceeded
                        JohnJomon
                        Most of the time this is caused by some applications which handles the connection pool in a poor manner.

                        Find out the number of sessions by same username and machine. rather than bouncing the database, ask the application person to stop the apps and check teh sessions again. And kill them off, and you need to make sure there are no java processes sitting idle on the application server.

                        It's not a one line answer as you need to do the troubleshooting to pinpoint the exact reason!