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:
- A new Oracle process is started.
- The new process attaches to SGA (system global area) shared memory segments.
- 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:
