Database Reference
In-Depth Information
timeout after: never
wait id: 131
blocking: 1 session
*** 2012-12-16 17:19:18.725
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[102]/2/103/1243/c0000000e4ae9518/12250/NLEAF/[
262
]
[
262
]/1/14/125/c0000000d4a03f90/8047/LEAF/
*** 2012-12-16 17:19:47.303
===============================================================================
HANG ANALYSIS DUMPS:
oradebug_node_dump_level: 3
===============================================================================
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[102]/2/103/1243/c0000000e4ae9518/12250/NLEAF/[262]
===============================================================================
END OF HANG ANALYSIS
===============================================================================
In the preceding example,
SID 102
on instance 2 is blocked by
SID 261
on instance 1. Upon identifying the
holder, either complete the transaction or abort the session to release the lock from the database.
It is sometimes advisable to have the
SYSTEMSTATE
dump along with the
HANGANALYSIS
to generate more detailed
diagnostic information to identify the root cause of the issue. Depending upon the level that is used to dump the
SYSTEMSTATE
, the cursor might take a very long time to return to the SQL prompt. The trace file details also can be
found in the
database alter.log
file.
You shouldn't be generating the
SYSTEMSTATE
dump under normal circumstances; in other words, unless
you have some serious issues in the database or are advised by Oracle support to troubleshoot some serious
database issues. Besides, the
SYSTEMSTATE
tends to generate a vast trace file, or it can cause an instance crash under
unpredictable circumstances.
Above all, Oracle provides the
HANGFG
tool to automate the collection of
systemstate
and
hang analysis
for a
non-RAC and RAC database environment. You need to download the tool from
my_oracle_suport
(previously known
as
metalink
). Once you invoke the tool, it will generate a couple of output files, named
hangfiles.out
and
hangfg.log
, under the
$ORACLE_BASE/diag/rdbms/database/instance_name/trace
location.
Summary
This chapter discussed the architecture and components of the Oracle Clusterware stack, including the updates in
Oracle Clusterware 12cR1. We will talk about some other new Oracle Clusterware features introduced in Oracle 12cR1
in Chapter 4.
This chapter also discussed tools and tips for Clusterware management and troubleshooting. Applying the tools,
utilities, and guidelines described in this chapter, you can diagnose many serious cluster-related issues and address
Clusterware stack startup failures. In addition, you have learned how to modify the default tracing levels of various
Clusterware daemon processes and their subcomponents to obtain detailed debugging information to troubleshoot
various cluster-related issues. In a nutshell, the chapter has offered you all essential cluster management and
troubleshooting concepts and skills that will help you in managing a medium-scale or large-scale cluster environment.
