Trace Connect Hang at startup

 http://www.oracleangels.com/2011/05/useful-tracing-commands-oradebug-oracle.html
-- 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



sqlplus '/ as sysdba'
oraclebug unlimit
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266

oraclebug tracefile_name




-----------------------------------------Identify the process id consuming 100% CPU and take errorstack as below

connect / as sysdba
oradebug setospid 1234 << replace 1234 with actual process id
oradebug unlimit
oradebug dump errorstack 3
wait 1 min
oradebug dump errorstack 3
wait 1 min
oradebug dump errorstack 3
oradebug tracefile_name

Killed Session Hung Consuming 100% CPU
Doc ID: Note:458881.1
215858.1