HANGANALYZE Part 2

Hi!
See the first part of this post here: HANGANALIZE Part 1.

This post is just complement with a little kludge I liked…
First, let’s remmember that the hanganalyze is used when you are if some hanging in your environment, of course.

But what if you are having difficult to access the database, even with ‘/ as sysdba’?

You can create a ‘preliminary connection’ without create a session, like this:

sqlplus -prelim / as sysdba

This ‘feature’ is available since Oracle 10g, and it basically skips a session creation part (which could block) when logging on as SYSDBA.

When you log on normally (even as SYSDBA), this is what happens:
1) A new Oracle process is started
2) The new process attaches to SGA shared memory segments
3) The new process allocates process and session state objects and initializes new session structures in SGA

The step 3 obviously can create some ‘lock’ once it’s allocating (locking) memory (usually latches/KGX mutexes).
So, the preliminar connection consists in not execute step 3. And this is the reason it solves ‘memory hangs’ situations… 🙂

But, there is another observation: With -prelim you are able to get a systemstate or an ashdump, but since 11.2.0.2 you cannot get a hanganalize. The statements are proccessed:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug hanganalyze 3
Statement processed.

Uuups, and what if I get this error in the trace file?

ERROR: Can not perform hang analysis dump without a process state object and a session state object.

No problems, McGayver can be applied again, there is a kludge for the kludge: You can use another ospid to generate the hanganalyse. It’s not recommended to use a vital process (just to mention).
I listed some sessions connected on database and used one of them to generate the hanganalyze:

[oracle@devdb09]$ ps -ef |grep  greporadb |grep LOCAL=NO |head
oracle    2418     1  0 13:54 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2420     1  0 13:54 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2422     1  0 13:54 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2565     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2567     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2569     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2571     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2573     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2575     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
oracle    2577     1  0 13:55 ?        00:00:00 oraclegreporadb (LOCAL=NO)
[oracle@devdb09 trace]$ sqlplus -prelim / as sysdba
SQL>  oradebug setospid 2577
Oracle pid: 133, Unix process pid: 2577, image: oracle@devdb09
SQL> oradebug dump hanganalyze 3
Statement processed.
SQL> exit
Disconnected from ORACLE

Ok, now the hanganalyze was generated on spid tracefile. Let’s see:

[oracle@devdb09 userdumpdest]$ ls -lrt |grep 2577
-rw-rw---- 1 oracle oracle      125 Jun 16 14:02 greporadb_ora_2577.trm
-rw-rw---- 1 oracle oracle     2772 Jun 16 14:02 greporadb_ora_2577.trc
[oracle@devdb09 trace]$ cat greporadb_ora_2577.trc |grep hanganalyze
Received ORADEBUG command (#1) 'dump hanganalyze 3' from process 'Unix process pid: 4068, image: '
Finished processing ORADEBUG command (#1) 'dump hanganalyze 3'

Awsome, hãn?

There is some similiar posts about:
Tanel Poder: Oradebug hanganalyze with a prelim connection and “ERROR: Can not perform hang analysis dump without a process state object and a session state object”.
Arup Nanda: Diagnosing Library Cache Latch Contention: A Real Case Study
How to log on even when SYSDBA can’t do so?
How to Use HANGANALYZE and How to Interpreting HANGANALYZE trace files

Matheus.