Troubleshoot Fix Resolve Ora-00020: Maximum Number Of Processes (num) Exceeded

Overview
This is a database resource error. The error is because of the number of oracle processes reached its maximum limit. The maximum limit is mentioned in your database parameter file (spfile/pfile). Corresponding init parameter is PROCESSES. 
How you can check the value of this parameter
  1. Open init.ora file and check the value of the parameter proceses
  2. Connect sqlplus sys as sysdaba
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------processes                            integer     400
  1. Connect sqlplus sys as sysdaba
SQL> select name , value from v$parameter where name ='processes';

NAME                 VALUE
-------------------- ------------------------------
processes            400

Permanent fix for this error
The permanent fix for this issue is increase the value of the process parameter.
  1. If your database is opened with spfile then,Alter system set sessions=600 scope=spfile;
    shutdown immediate;
    startup;
  2. If Your database is opened with pfile then,
    Open your parameter file(init.ora) with an editor and modify the processes parameter value to higher value. Restart the database after making the changes to the parameter file.
Quick fix/workaround
If you are not at all able to login to the database, or you cannot bounce the database without maintenance window you can go ahead with following workaround.
For Unix: Find out some old idle oracle session process and kill them using OS kill command
$  ps -ef|grep LOCAL=NO|grep oracleproddb
  oracle  1994     1   0 16:34:27 ?           0:01 oracleproddb (LOCAL=NO)
  oracle  2202     1   0 16:38:05 ?           0:00 oracleproddb (LOCAL=NO)
  oracle  2400     1   0 16:39:06 ?           2:14 oracleproddb (LOCAL=NO)
$ kill -9 1994
For Windows:
kill some old sessions using ORAKILL utility.
Using SQLPLUS with sys as sysdba privilege:
If someone already logged in with sqlplus with sysdba privilege, find out some old idle connections from v$session dictionary view using login time and kill those sessions 
alter system kill session ‘sid, serial#’;
 
Recommendation
                If your database was quite stable for long time and one fine day it is giving problem, in that case it is recommended to check the application also whether it is spawning unwanted connections or process.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google