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

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.