Oracle HangAnalyze


There comes a time in your life (more often then not and when you least expect) you will find that your sql session is hanging or some client application is hung or the database itself is hanging. While utilities AWR and ADDM are helpful, these don't tell the internal status of the instance, in particular if you are stuck with a hung instance. In such cases running AWR/ADDM is out of the equation. Oracle provides a additional tool as part of "oradebug" called "hanganalyze". This tool can be used to either diagnose a known hung session or the database in general.

This blog contains steps to generate hang analyze trace file

Run the following query to obtain the SPID of the hung session

Obtain session SPID
select a.inst_id, 
       a.sid, 
       a.serial#,  
       b.spid ospid, 
       to_char(logon_time,'dd-Mon-rr hh24:mi') LogonTime, 
       lower(status) status
from   gv$session a, gv$process b 
 where a.inst_id = b.inst_id   
   and a.paddr = b.addr 
   and status = 'ACTIVE';

Invoking oradebug
1. Connect to the database as sys user
2. If spid of the hung session is know then execute the below command
SQL> oradebug setospid [spid]
3. If the spid is not know or if you want to generate a dump for the entire database choose this
SQL> oradebug setmypid;
4. SQL> oradebug unlimit;
5. SQL> oradebug hanganalyze 3 << This will return the name of the trace file that will contain the dump results.
6. SQL> !sleep 60  << This is optional and is used for second snapshot to be taken for comparison.
7. SQL> oradebug hanganalyze 3 << This will append to the same trace file generated in the above step. No need to run this if comparison is not required.

The trace file usually written to $ORACLE_BASE/diag/rdbms/[dbname]/[instance_name]/trace/ folder. Make sure there is enough diskspace to store this file.

In the trace file look for the section caleld "Chains most likely to have caused the hang:" (located towards the top of the report). This will give you general idea of what you are dealing with. Should you need additional information, for each of the chains listed in this section a breakdown is provided with details of the waiting and blocking sessions in the following section.

Note that this trace file when uploaded to Oracle Support along with a SR usually leads to speedier resolution

Popular posts from this blog

Recover Standby database from ORA-00332: archived log is too small

When a standby database crashes and upon instance recovery gets stuck with error ORA-00332: archived log is too small - may be incompletely archived, then following set of actions must be carried out to restore the service. 1. Check that the instance is in MOUNTED. (select status from v$instance) 2. If not mounted, then start the database in mounted mode SQL> startup mount; 3. Search the alert log to identify the archive log that is corrupt,  Sample lines from the alert log Errors in file /u01/app/oracle/diag/rdbms/fxfgsby/FXFGSBY1/trace/FXFGSBY1_ora_19463.trc: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '+FLASH/fxfgsby/archivelog/2013_08_19/thread_1_seq_173831.5998.823855747' Shutting down recovery slaves due to error 332 4. In the above example, the corrupt log file belongs to Thread#=1 and Sequence#=173831 5. Log on to the Primary database cluster and take a copy of the file from ASM onto the local file system. Not...
Read more

Check Active Dataguard Replication Status

In order to check if ADG is up-to-date, one has to tail the alert log on both the Primary and Physical Standby databases, followed by a manual check to look for the log status of (in-transit) against each of the threads. While this method is perfect it can be bit tedious to perform. You can now do the same using one of the attached SQL from the Primary database. Deployment and usage steps - Open a vi editor on the Primary database. - Copy of the below SQL into the editor - Complete the  database link creation command based on the database    you are checking. - Save the text file as check_adg_status.sql - execute the sql file as sys user. e.g. sqlplus -s  / as sysdba @check_adg_status.sql  Note : These commands cannot be run from the Physical standby as the query relies on using a temporary database link that cannot be created on a read-only database. Troubleshooting 1. Some database clusters may report the following error when invoked   ...
Read more

Top CPU consuming oracle sessions

Use the shell script from  here  to obtain the details of the Oracle sessions using high CPU.  Usage: Log in as oracle user and navigate to the folder where the script has been copied into. Do not forget to grant execute permissions to the script prior to invoking it. $ ./top_oracle_sessions.sh [cpu_threshold]  e.g.1  $ ./top_oracle_sessions.sh 90       This will gather information if overall CPU usage is above 90% e.g.1  $ ./top_oracle_sessions.sh        This will gather information if overall CPU usage is above 50%. This is the default setting. Notes: Use the attached script to obtain the details of the Oracle sessions using high CPU. The script works by follows Takes 2 snapshots using Linux "top" command with a interval of 2 seconds between snaps Uses the data from the second snapshot to determine if the overall CPU usage is above a set threshold. The threshold is a conf...
Read more