To hold the latch manually, DB hang demo(手动持用latch使数据库hang)
It is widely known that the Oracle server uses kslgetl – Kernel Service Lock Management Get Latch function to acquire the latch,oradebug call kslgetl/kslfre can be used to acquire the latch manually. This is very useful to simulate latch related hangs and contention.
Oracle 11g externalizes latch structures in x$kslltr_parent and x$kslltr_children fixed tables for parent and child latches respectively,view v$latch_parent and v$latch_children were created on thers tables, Versions 10g and before used x$ksllt table,since 11g used x$kslltr table , Fixed views v$latch these tables.
x$ksmfsv kernel service, memory fixed SGA variables detailing fixed SGA:
select a.ksmfsnam, a.ksmfstyp, a.ksmfssiz, b.ksmmmval
from x$ksmfsv a, x$ksmmem b
where a.ksmfsadr = b.addr and a.ksmfsnam like…
that they point to. X$KSMMEM has one row for every memory address in the SGA,. For a latch, get ksmfsnam by matching x$ksmfsv.ksmfadr (KSMFSADR in 11g)with x$kslld.kslldadr(v$latchname created by this table). You can see SGA parameters in ksmfsnam column and get their values with oradebug dumpvar varname or all values with oradebug dumpsga
The Redo Allocation Latch as Seen from X$KSMFSV
on 10g
select ksmfsnam, ksmfstyp, ksmfssiz, ksmfsadr from x$ksmfsv where ksmfsnam like ‘kcrfal%’
on 11g
sys@ANBOB>select ksmfsnam, ksmfstyp, ksmfssiz, ksmfsadr from x$ksmfsv where ksmfsnam ='kcrfral_'; KSMFSNAM KSMFSTYP KSMFSSIZ KSMFSADR -------------------- -------------------- -------------------- ---------------- kcrfral_ ksllt 160 0000000060022778 sys@ANBOB>select a.KSMFSADR,b.KSLLTNAM,b.ksmfsnam,b.KSMFSTYP from x$kslltr a, x$ksmfsv b where b.KSMFSADR=a.KSLLTADDR and ksmfsnam ='kcrfral_'; KSMFSADR KSLLTNAM KSMFSNAM KSMFSTYP ---------------- ------------------------------ -------------------- -------------------- 0000000060022778 redo allocation kcrfral_ ksllt sys@ANBOB> select k.ksmfsadr, ksmfsnam, ksmfstyp, ksmfssiz, kslldnam, kslldlvl 2 from x$ksmfsv k, x$kslld a 3 where k.ksmfsadr = a.kslldadr 4 and ksmfsnam = 'kcrfral_' 5* order by ksmfsnam KSMFSADR KSMFSNAM KSMFSTYP KSMFSSIZ KSLLDNAM ---------------- --------------- --------------- ------------- ------------------------ 0000000060022778 kcrfral_ ksllt 160 redo allocation
Now we holde the latch manually.For several years it was commonly supposed that kslgetl() has two parameters:[latch address] [wait].
sys@ANBOB>oradebug call kslgetl 0x0000000060022778 1
Function returned 1
sys@ANBOB>select * from v$latchholder;
PID SID LADDR NAME GETS
-------------------- -------------------- ---------------- ------------------------------ --------------------
19 96 0000000060022778 redo allocation 391
sys@ANBOB>oradebug peek 0x0000000060022778 20
[060022778, 06002278C) = 00000016 00000000 00000187 000000BB 00000005
To release the latch
sys@ANBOB>oradebug call kslfre 0x0000000060022778 0 Function returned 0 sys@ANBOB>oradebug peek 0x0000000060022778 20 [060022778, 06002278C) = 00000000 00000000 00000187 000000BB 00000005 SQL> select * from v$latchholder; no rows selectedNotice: Never perform the function on your production environment,if to hold redo allocation latch possible hit the follow problem event ORA-00600: internal error code, arguments: [504], [0x0F5519E28], [32], [1], [session idle bit] ORA-07445: exception encountered: core dump [ksl_hierarchy_error()+560] [SIGSEGV] [ADDR:0xFB5D8A530] [PC:0x4860A22] [Address not mapped to object] Now to hold all shared pool latches ,try to let DB instance hang. Print/Dump memory of one shared pool latch with oradebug peek oradebug peek
sys@ANBOB>oradebug setmypid Statement processed. sys@ANBOB>oradebug peek 0x60107A78 4 [060107A78, 060107A7C) = 00000000 sys@ANBOB>select addr from v$latch_children where name='shared pool'; ADDR ---------------- 0000000060107A78 00000000601079D8 0000000060107938 0000000060107898 00000000601077F8 0000000060107758 00000000601076B8 sys@ANBOB>select 'oradebug poke 0x'||addr||' 4 0x01' poke_sha from v$latch_children where name='shared pool'; sys@ANBOB>oradebug poke 0x0000000060107A78 4 0x01 BEFORE: [060107A78, 060107A7C) = 00000000 AFTER: [060107A78, 060107A7C) = 00000001 sys@ANBOB>oradebug poke 0x00000000601079D8 4 0x01 BEFORE: [0601079D8, 0601079DC) = 00000000 AFTER: [0601079D8, 0601079DC) = 00000001 sys@ANBOB>oradebug poke 0x0000000060107938 4 0x01 BEFORE: [060107938, 06010793C) = 00000000 AFTER: [060107938, 06010793C) = 00000001 sys@ANBOB>oradebug poke 0x0000000060107898 4 0x01 BEFORE: [060107898, 06010789C) = 00000000 AFTER: [060107898, 06010789C) = 00000001 sys@ANBOB>oradebug poke 0x00000000601077F8 4 0x01 BEFORE: [0601077F8, 0601077FC) = 00000000 AFTER: [0601077F8, 0601077FC) = 00000001 sys@ANBOB>oradebug poke 0x0000000060107758 4 0x01 BEFORE: [060107758, 06010775C) = 00000000 AFTER: [060107758, 06010775C) = 00000001 sys@ANBOB>oradebug poke 0x00000000601076B8 4 0x01 BEFORE: [0601076B8, 0601076BC) = 00000000 AFTER: [0601076B8, 0601076BC) = 00000001
Tip:
oradebug poke function directly modify the memory not like oradebug call kslgetl
sys@ANBOB>oradebug peek 0x60107A78 4 [060107A78, 060107A7C) = 00000001
Create new session try to logon DB with sqlplus will be hang(eg. sqlplus anbob/pwd or sqlplus / as sysdba)
Trace system state dump
[oracle@db231 ~]$ sqlplus -prelim SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 16:39:06 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter user-name: / as sysdba ERROR: ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 idle>oradebug setmypid Statement processed. idle>oradebug dump systemstate 266; Statement processed. idle>oradebug tracefile_name /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc idle>exit
Tip:
The system dump trace file path will be written in alert log ,the following output:
Wed Aug 07 16:40:36 2013
System State dumped to trace file /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
[oracle@db231 ~]$ awk -f ass109.awk /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
Starting Systemstate 1
..............................
Ass.Awk Version 1.0.9 - Processing /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_21745.trc
System State 1
~~~~~~~~~~~~~~~~
1:
2: 0: waiting for 'pmon timer'
3: 0: waiting for 'rdbms ipc message'
4: 0: waiting for 'VKTM Logical Idle Wait'
5: 0: waiting for 'rdbms ipc message'
6: 0: waiting for 'DIAG idle wait'
7: 0: waiting for 'rdbms ipc message'
8: 0: waiting for 'DIAG idle wait'
9: 0: waiting for 'rdbms ipc message'
10: 0: waiting for 'rdbms ipc message'
11: 0: waiting for 'rdbms ipc message'
12: 0: waiting for 'rdbms ipc message'
13: 0: waiting for 'smon timer'
14: 0: waiting for 'latch: shared pool'[Latch 601076b8]
15: 0: waiting for 'latch: shared pool'[Latch 601076b8]
16: 0: waiting for 'rdbms ipc message'
17:
18:
19: 0: waiting for 'SQL*Net message from client'
20: 0: waiting for 'Streams AQ: qmn slave idle wait'
21: 0: waiting for 'latch: shared pool'[Latch 60107758]
22: 0: waiting for 'SQL*Net message from client'
23: 0: waiting for 'latch: shared pool'[Latch 60107758]
24: 0: waiting for 'Space Manager: slave idle wait'
25: [Latch 601076b8]
26: [Latch 60107758]
27: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
30: 0: waiting for 'latch: shared pool'[Latch 60107758]
40: 9: waited for 'Streams AQ: waiting for time management or cleanup tasks'
41: 0: waiting for 'rdbms ipc message'
Blockers
~~~~~~~~
Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.
Resource Holder State
Latch 601076b8 ??? Blocker
Latch 60107758 ??? Blocker
Object Names
~~~~~~~~~~~~
Latch 601076b8 Child shared pool
Latch 60107758 Child shared pool
To release all holded shared pool latches
[oracle@db231 ~]$ sqlplus -prelim '/ as sysdba' SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 16:59:12 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. ERROR: ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 idle>oradebug setmypid Statement processed. idle>oradebug poke 0x0000000060107A78 4 0x00 BEFORE: [060107A78, 060107A7C) = 00000001 AFTER: [060107A78, 060107A7C) = 00000000 idle>oradebug poke 0x00000000601079D8 4 0x00 BEFORE: [0601079D8, 0601079DC) = 00000001 AFTER: [0601079D8, 0601079DC) = 00000000 idle>oradebug poke 0x0000000060107938 4 0x00 BEFORE: [060107938, 06010793C) = 00000001 AFTER: [060107938, 06010793C) = 00000000 idle>oradebug poke 0x0000000060107898 4 0x00 BEFORE: [060107898, 06010789C) = 00000001 AFTER: [060107898, 06010789C) = 00000000 idle>oradebug poke 0x00000000601077F8 4 0x00 BEFORE: [0601077F8, 0601077FC) = 00000001 AFTER: [0601077F8, 0601077FC) = 00000000 idle>oradebug poke 0x0000000060107758 4 0x00 BEFORE: [060107758, 06010775C) = 00000001 AFTER: [060107758, 06010775C) = 00000000 idle>oradebug poke 0x00000000601076B8 4 0x00 BEFORE: [0601076B8, 0601076BC) = 00000001 AFTER: [0601076B8, 0601076BC) = 00000000 sys@ANBOB>oradebug peek 0x60107A78 4 [060107A78, 060107A7C) = 00000000
oracle@db231 ~]$ sqlplus anbob/anbob
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 7 17:04:55 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
anbob@ANBOB>
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。