ASHDUMP for Instance Crash/Hang ‘Post Mortem’ Analysis

Hi guys!
In the last weeks I talked about ASHDUMP in the post HANGANALYZE Part 1. Let’s think about it now…

Imagine the situation: The database is hanging, you cannot find what is going on and decided to restart the database OR your leader/boss yelled to you do it so, OR you know the database is going do get down, anyway…
Everyone has passed by this kind of situation at least once. After restart everything become OK and the ‘problem’ was solved. But now you are being asked about RCA (what caused this situation?). The database was hanging, so no snap was closed and you lost the ASH info…

For this cases I think is very useful to take 1 minute before database get down to generate an ASHDUMP. It’s very simple:

sqlplus / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump ashdumpseconds 30
oradebug tracefile_name

An exemple of execution:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump ashdumpseconds 30
Statement processed.
SQL> oradebug tracefile_name
/db/oracle/diag/rdbms/grepora/GREPORA/trace/GREPORA_ora_22024.trc

The command below will generate an ASH dump from the last 30 seconds to trace file. You can also generate an ASHDUMP for minutes by changing the line with ashdumpseconds by:

SQL> oradebug dump ashdump 5

Other way to do it is:

SQL> alter session set events 'immediate ashdump(5)';

Or the equivalent for ASHDUMPSECONDS:

SQL> alter session set events 'immediate ashdumpseconds(300)';

If you cannot create a connection in database with SQLPlus (even as SYSDBA), regarding it’s a hang situation, you can use an preliminar connection, like shown in the post HANGANALYZE Part 2.

The trace file is generated with instructions to import data with SQLLDR. This way you can realize your ‘Post Mortem’ analysis. 🙂

An example of ASHDUMP file:

ASHDUMPSECONDS
=====================================================
Processing Oradebug command 'dump ashdumpseconds 30'
ASH dump
<>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table 
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file  as below
----------------------------------------------------------------
load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID  CONSTANT 0           ,
DBID                          ,
INSTANCE_NUMBER               ,
SAMPLE_ID                     ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   ,
SESSION_ID                    ,
SESSION_SERIAL#               ,
SESSION_TYPE                  ,
USER_ID                       ,
SQL_ID                        ,
SQL_CHILD_NUMBER              ,
SQL_OPCODE                    ,
FORCE_MATCHING_SIGNATURE      ,
TOP_LEVEL_SQL_ID              ,
TOP_LEVEL_SQL_OPCODE          ,
SQL_PLAN_HASH_VALUE           ,
SQL_PLAN_LINE_ID              ,

SQL_PLAN_OPERATION#           ,
SQL_PLAN_OPTIONS#             ,
SQL_EXEC_ID                   ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   ,
PLSQL_ENTRY_OBJECT_ID         ,
PLSQL_ENTRY_SUBPROGRAM_ID     ,
PLSQL_OBJECT_ID               ,
PLSQL_SUBPROGRAM_ID           ,
QC_INSTANCE_ID                ,
QC_SESSION_ID                 ,
QC_SESSION_SERIAL#            ,
EVENT_ID                      ,
SEQ#                          ,
P1                            ,
P2                            ,
P3                            ,
WAIT_TIME                     ,
TIME_WAITED                   ,
BLOCKING_SESSION              ,
BLOCKING_SESSION_SERIAL#      ,
BLOCKING_INST_ID              ,
CURRENT_OBJ#                  ,
CURRENT_FILE#                 ,
CURRENT_BLOCK#                ,
CURRENT_ROW#                  ,
TOP_LEVEL_CALL#               ,
CONSUMER_GROUP_ID             ,
XID                           ,
REMOTE_INSTANCE#              ,
TIME_MODEL                    ,
SERVICE_HASH                  ,
PROGRAM                       ,
MODULE                        ,
ACTION                        ,
CLIENT_ID                     ,
MACHINE                       ,
PORT                          ,
ECID
)
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data= errors=1000000
---------------------------------------------------
<>
<>
####
4092499541,1,93736863,"06-15-2016 16:58:00.581442000",118,13423,1,152,"a3dj32s553jwz",0,3,16794496187212003770,"",0,3121342805,1,20,0,27310348,"06/15/2016 16:57:59",0,0,0,0,0,0,0,310662678,642,1415053318,9371681,422864,0,511985,590,62515,1,289642,7,1595,0,94,12553,,0,1024,3427055676,"","","","","devapp16",35734,""
####
4092499541,1,93736863,"06-15-2016 16:58:00.581442000",309,869,1,0,"",65535,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,112941199,13,0,0,0,0,499675,4294967295,0,1,4294967295,0,0,0,86,12553,,0,0,3427055676,"sqlplus@devdb09 (TNS V1-V3)","sqlplus@devdb09 (TNS V1-V3)","","","devdb09",0,""
####
<>
*** 2016-06-15 16:58:13.931
Oradebug command 'dump ashdumpseconds 30' console output:

Very nice, right? 🙂

Matheus.