Oracle-Scripts.net

Connect Hanged Oracle DB

-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/10/27
-- # Name: Connect to Hanged DB
-- ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # How to connect to the sga when DB is hanging
-- # and connection is not permitted
-- #
-- ################################################

-- There is an option that is helpful when the databse is hanging and we can't connect 
-- to database but we need to execute commands like oradebug

-- With sqlplus -prelim 
-- we can connect to the sga but not to the database, in this case no session is created.



1) sqlplus -prelim / as sysdba

2) sqlplus /nolog
set _prelim on
conn / as sysdba



Example:

$sqlplus -prelim /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 26 19:35:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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