oradebug gather all impotant information for troubleshooting

If a database is hung, then we cannot even login to sqlplus using "/ as sysdba", right?

Not entirely true, atleast for 10g and above.

We can login as follows and collect vital information for analyzing the reason for the hang before bouncing the db

The prelim option allows us to connect to the SGA but does not establish a session
and hence we can perform hanganalyze commands to dump information for further review
of hang conditions and reasons. This is a sessionless SQL Plus connection to the SGA.
Same thing can be done from EM as well by having a Direct SGA connection


$sqlplus -prelim
SQL>

At this point, it will not say anything like "Connected to Database"...etc.
Now you can perform oradebug hanganalyze commands

SQL> oradebug setmypid
SQL> oradebug hanganalyze 12


===>>>>>>>>>>>>>>>>>>>>>>>


- Get the sid from the pid or vice versa:

SELECT s.sid, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND ... < p.spid = or perhaps
s.sid = >




If certain processes get PROCESSSTATE dump.
- Trace the process from the os level (see step 4).

Get processstate dumps. Do 3 times. This generates a trace file in
your user_dump_dest (from sqlplus: show parameter user_dump_dest).

$ sqlplus "/as sysdba"
oradebug setospid
oradebug unlimit
oradebug dump processstate 10



Get errorstacks from the process. Do 3 times. This generates a
trace file in your user_dump_dest (from sqlplus: show parameter user_dump_dest).

$ sqlplus "/as sysdba"
oradebug setospid
oradebug unlimit
oradebug dump errorstack 3




====>>>>>>>>




oradebug dump treedump

Dumps the structure of an index tree. The dump file contains one line for each block in the tree,
indented to show its level, together with a count of the number of index entries in the block.



Syntax Parameter:

oradebug dump treedump object_id


Example:

select object_id from sys.dba_objects
where owner = upper('&Owner') and
object_name = upper('&IndexName');


SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump treedump 40
Statement processed.

Instead of oradebug dump treedump command you can also use,
alter session set events 'immediate trace name treedump level n';

That will give you a trace file with the one line for each block in the B*-tree and a row
count for each block.If there are large numbers of empty or nearly empty blocks, then the
index is a good candidate for being rebuilt.



=============>>>>>>>>>>>==========

oradebug for oracle Background process :


oradebug procstat
Dump process Statistics.


Syntax Parameter :
oradebug procstat OS PID


Example
Dump statistics for the DBWR background process:


select pid,name
from v$process p, v$bgprocess b
where b.paddr = p.addr
SQL> /
PID NAME
---------- -----
2 PMON
3 DBW0
4 LGWR
5 CKPT
6 SMON
7 RECO

6 rows selected.

SQL> oradebug setorapid 3

Unix process pid: 15668, image: oracle@apollo (DBW0)

SQL> oradebug procstat
Statement processed.

To find out where is the trace file located run:

SQL> oradebug tracefile_name
/ora-main/app/oracle/admin/test/bdump/test_dbw0_15668.trc