Connect Hanged Oracle DB

There is an option that is helpful for the Oracle DBA when the database is hanging and we can’t connect to the database. We can execute commands like oradebug and sqlplus -prelim
We can connect to the sga but not to the database, in this case no session is created.

We wants to connect to the sga but it’s impossible to create a sql*plus session:

 sqlplus -prelim / as sysdba
(or)
sqlplus /nolog
set _prelim on
conn / as sysdba

Example:

$sqlplus -prelim /nolog

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in C:\oracle\admin\orcl\udump\orcl_ora_5564.trc
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
C:\oracle\admin\orcl\udump\orcl_ora_5598.trc


In a RAC environment:

SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 12

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all dump systemstate 266

We can generate the hanganalyze and systemstate dumps in a normal SQL*Plus session too:

 For the HangAnalyze
SQL>alter session set events 'immediate trace name hanganalyze level 12';
To get the SystemState:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Author: Vincent Fenoll (DBA Oracle)
Compatible: Oracle 10g 11g 12c

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.