Library cache lock and pin Simulation of 1OG Database combined with hanganalyze location and systemdump location
Session 1:
SQL > select sid from v$mystat where rownum=1
SID
-
one hundred and forty eight
SQL > create or replace procedure pining
Is
Begin
Null
End
/
Procedure created
SQL >
SQL > create or replace procedure calling
Is
Begin
Pining
Dbms_lock.sleep (200)
End
/
SQL >
SQL > call calling ()
.
Session 2:
SQL > select sid from v$mystat where rownum=1
SID
-
one hundred and fifty eight
SQL > alter procedure pining compile
Hang.
Session 3:
SQL > select sid from v$mystat where rownum=1
SID
-
one hundred and forty nine
SQL > drop procedure pining
Hang.
Session 4:
SQL > select sid, event,wait_class, seconds_in_wait from v$session_wait w where w.WAIT_CLASS 'Idle'
SID EVENT WAIT_CLASS SECONDS_IN_WAIT
--
146 SQL*Net message to client Network 0
149 library cache lock Concurrency 3
158 library cache pin Concurrency 6
SQL > oradebug setmypid
Statement processed.
SQL > oradebug hanganalyze 3
Hang Analysis in/ u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
SQL > oradebug close_trace
Statement processed.
Session 5:
SQL > oradebug setmypid
Statement processed.
SQL > oradebug unlimit
Statement processed.
SQL > oradebug dump systemstate 266
Statement processed.
SQL > oradebug tracefile_name
/ u01/app/oracle/admin/orcl/udump/orcl_ora_17000.trc
SQL > oradebug close_trace
Statement processed.
SQL >
-- View the trace file of hang:
[root@rhel ~] # cat / u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
/ u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = / u01/app/oracle/product/10.2.0.1/db_1
System name: Linux
Node name: rhel
Release: 2.6.18-238.el5
Version: # 1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
* SERVICE NAME: (SYS$USERS) 2017-11-23 15 purl 0824.320
* SESSION ID: (146.17) 2017-11-23 1514 0824.320
* * 2017-11-23 15 purl 08purl 24.320
=
HANG ANALYSIS:
=
Open chains found:
-- from here on, the following session is blocked by the previous session.
Chain 1::
--
--
Other chains found:
The session below is also blocked by the front and indirectly blocked.
Chain 2::
Chain 3::
Chain 4::
Chain 5::
Chain 6::
Extra information that will be dumped at higher levels:
[level 4]: 1 node dumps-- [REMOTE_WT] [LEAF] [LEAF_NW]
[level 5]: 5 node dumps-- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6]: 2 node dumps-- [NLEAF]
[level 10]: 12 node dumps-[IGN]
State of nodes
([nodenum] / cnode/sid/sess_srno/session/ospid/state/start/finish/ [adjlist] / predecessor):
[139] / 0/140/1/0x83b58c18/2825/SINGLE_NODE/1/2//none
[144] / 0/145/12/0x83b5f130/17000/IGN/3/4//none
[145] / 0/146/17/0x83b60568/16916/SINGLE_NODE_NW/5/6//none
[146] / 0/147/2/0x83b619a0/2823/SINGLE_NODE/7/8//none
[147] / 0/148/102/0x83b62dd8/16769/LEAF/9/10//157
[148] / 0/149/135/0x83b64210/16914/NLEAF/11/14/ [157] / none
[150] / 0/151/987/0x83b66a80/17003/SINGLE_NODE/15/16//none
[153] / 0/154/1/0x83b6a728/2797/SINGLE_NODE/17/18//none
[157] / 0/158/250/0x83b6f808/16912/NLEAF/12/13/ [147] / 148
[159] / 0/160/1/0x83b72078/2789/IGN/19/20//none
[160] / 0/161/1/0x83b734b0/2787/IGN/21/22//none
[161] / 0/162/1/0x83b748e8/2785/IGN/23/24//none
[162] / 0/163/1/0x83b75d20/2783/IGN/25/26//none
[163] / 0/164/1/0x83b77158/2781/IGN/27/28//none
[164] / 0/165/1/0x83b78590/2779/IGN/29/30//none
[165] / 0/166/1/0x83b799c8/2777/IGN/31/32//none
[166] / 0/167/1/0x83b7ae00/2775/IGN/33/34//none
[167] / 0/168/1/0x83b7c238/2773/IGN/35/36//none
[168] / 0/169/1/0x83b7d670/2771/IGN/37/38//none
[169] / 0/170/1/0x83b7eaa8/2769/IGN/39/40//none
=
END OF HANG ANALYSIS
=
/ u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = / u01/app/oracle/product/10.2.0.1/db_1
System name: Linux
Node name: rhel
Release: 2.6.18-238.el5
Version: # 1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
* 2017-11-23 15 Fringe 08purl 44.057
Cannot find symbol
Cannot find symbol
Cannot find symbol
[root@rhel ~] #
Orcl_ora_17000.txt
