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.
Pingback: SYSTEMSTATE DUMP – |GREP ORA
Pingback: ASHDUMP for Instance Crash/Hang ‘Post Mortem’ Analysis – |GREP ORA
Pingback: HANGANALYZE Part 1 – |GREP ORA