Post-Mortem Analysis Tools: Using Preliminary Connection

Editor’s note: This “post-mortem analysis” series consists of four posts in total. Make sure you also check out:

• Post-Mortem Analysis Tools: Systemstate Dump
• Post-Mortem Analysis Tools: ASH Dump
• Post-Mortem Analysis Tools: Hanganalyze

Now, onto today’s post:

Many DBAs have been in the situation where a database is hanging, you restart it and after restart everything seems OK, with the problem solved. However, in terms of RCA (root cause analysis), you’re at a loss. I wrote these posts to help in situations like this.

I’ve already covered a few tools, like hanganalyze, ASH dump and systemstate dump.

But what if you can’t create a connection to the database with SQL*Plus (even as SYSDBA), to address the hang situation?

You can create a “preliminary connection” without creating a real session, like this:

sqlplus -prelim / as sysdba

This feature has been available since Oracle 10g, and it basically skips the session creation requirement (which could block your efforts) 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 (system global area) shared memory segments.
3. The new process allocates process and session state objects and initializes new session structures in SGA.

Step three can obviously create a lock situation once it’s allocating (locking) memory (usually latches/KGX mutexes). So, the preliminary connection allows you to skip step three. This is the reason it solves memory hang situations.

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

There’s another observation though—with -prelim you’re able to get a systemstate or an ASH dump, but as of 11.2.0.2 you can’t get a hanganalyze. What if I experience the following error in the trace file, for example?

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

No problem—a quick kludge can be applied again. You can use another ospid to generate the hanganalyze. Please note: Using a vital process isn’t recommended.

Here I’ve listed some sessions connected to the 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 oraclepythiandb (LOCAL=NO)
oracle    2420     1  0 13:54 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2422     1  0 13:54 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2565     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2567     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2569     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2571     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2573     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2575     1  0 13:55 ?        00:00:00 oraclepythiandb (LOCAL=NO)
oracle    2577     1  0 13:55 ?        00:00:00 oraclepythiandb (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

Now the hanganalyze has been generated on spid tracefile. Let’s have a look:

[oracle@devdb09 userdumpdest]$ ls -lrt |grep 2577
-rw-rw---- 1 oracle oracle      125 Jun 16 14:02 pythiandb_ora_2577.trm
-rw-rw---- 1 oracle oracle     2772 Jun 16 14:02 pythiandb_ora_2577.trc
[oracle@devdb09 trace]$ cat pythiandb_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'

Awesome, right?

Here are some additional reference materials I recommend:

• How to Collect Systemstate Dumps When you Cannot Connect to Oracle (Doc ID 121779.1)
• Important Customer information about using Numeric Events (Doc ID 75713.1)

I hope this was helpful! If you have questions or thoughts, please leave them in the comments.

You can find other posts in this series here:

• Post-Mortem Analysis Tools: Systemstate Dump
• Post-Mortem Analysis Tools: ASH Dump
• Post-Mortem Analysis Tools: Hanganalyze