Question:
:
: I had a hung database last week, and after three minutes and
hundreds of hung users, I killed the Oracle background processes and
re-started the database. I got no trace or dump file. How can I
get diagnostic information from a hung instance before I re-start
the database?
I cannot inconvenience my end-user community for capturing
diagnostics for more than a few seconds. Does Oracle have a way to
dump an SGA and diagnostics prior to bouncing a hung database?
Answer: : The SQL*Plus utility has an undocumented argument invoked as
(-prelim) to allow you to connect to a hung Oracle database that is
not accepting new connections. The sqlplus prelim option allows
you to access a hung database to quickly run an oradebug trace
report.
Also see
onnecting to a hung instance using -prelim option.
This prelim utility is not the same as the hang manager (hangman)
utility, a tool introduced in Oracle 11g to detect database
bottlenecks. An extension of the dba_waiters and
dba_blockers views, the hangman tables have a "hang chain" that
allow the DBA to find the source of hangs, such as the "deadly
embrace" where mutually blocking locks or latches hang a process.
Note: Using the sqlplus -prelim should be done very quickly
since you will want to quickly re-start your instance so as to
minimize the downtime of your user community. You never want to
keep hundreds of users waiting, and a 2 minute bounce (nuke and
re-start) is always faster than trying to resurrect connectivity to
a hung instance.
Here is how to quickly generate a hang analysis immediately prior to
re-starting a hung production instance:
/u01/app/oracle/bin>sqlplus system/mypass -prelim /nolog
SQL*Plus: Release 10.2.0.2.0 - Production
> oradebug
setmypid
Statement processed.
> oradebug hanganalyze
12
Hang Analysis in
/u01/app/oracle/admin/mysid/udump/mysid_ora_1234.trc
