sqlplus preliminary connection


pflaster_Fotor

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 12.1.0.1.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 12.1.0.1.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 12.1.0.1.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 11.2.0.2 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

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s