Some time ago a customer had problems with a application that generates many blocking sessions. In the end all the blocking session grow up and exceeds the avaible processes on the database so that the ORA-00020 maximum number of processes exceeded error occur. The customer also ask me, to analyze the problem to know which statement is the root cause of the problem.
The problem now is that the database preventing new connections to that instance (normal users and for sysdba connection). Sure you can now kill some session at OS level to get a process for your connection or restart the database, but if you kill the wrong connection you may not find the root cause of the problem.
[oracle@server1 prelim]$ sqlplus scott/tiger SQL*Plus: Release 188.8.131.52.0 Production on Sun Jun 15 16:08:35 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-00020: maximum number of processes (300) exceeded
The same issue occur if we try to login as sysdba user.
[oracle@server1 DB12EE]$ sqlplus / as sysdba SQL*Plus: Release 184.108.40.206.0 Production on Sun Jun 15 16:11:40 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-00020: maximum number of processes (300) exceeded
What we can do here is to use the Preliminary Parameter to start a sqlplus session. Using a sqlplus preliminary connection you will be able to connect to the database since no session is actually created, but you will have limited access to the SGA. This will help in capturing diagnostic information like a systemstate dump to aid in problem resolution.
[oracle@server1 DB12EE]$ sqlplus -prelim / as sysdba SQL*Plus: Release 220.127.116.11.0 Production on Sun Jun 15 16:20:09 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> oradebug setmypid Statement processed. SQL> oradebug hanganalyze 3 Statement processed. SQL> oradebug TRACEFILE_NAME /u01/app/oracle/diag/rdbms/db12ee/DB12EE_1/trace/DB12EE_1_ora_24689.trc
With 18.104.22.168 onwards oradebug hanganalyze will not produce output under a sqlplus „preliminary connection“. If you do this as shown in this example oradebug seems to run successfull but when we look into the trace file generated we only see this message.
Processing Oradebug command 'hanganalyze 3' =============================================================================== HANG ANALYSIS: ERROR: Can not perform hang analysis dump without a process state object and a session state object. ( process=(nil), sess=(nil) ) ===============================================================================
Now we can make a systemstate dump of the database. The trace files generated here are also helpfull to analyze a problem like here described. Also read the documentation which level is suitable for your problem.
SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug dump systemstate 267 Statement processed. SQL> oradebug TRACEFILE_NAME /u01/app/oracle/diag/rdbms/db12ee/DB12EE_1/trace/DB12EE_1_ora_24689.trc
After you have created your trace files you can try to kill some of the database session so a normal login is possible. To kill a database session you can use „kill -9“ on Unix or „orakill“ on windows. Once you have a connection to the database you can do further analyse of the problem.
Hope this helps and as always comments are welcome