首页 » ORACLE 9i-23ai » To hold the latch manually, DB hang demo(手动持用latch使数据库hang)

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 selected
Notice: 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 [level] oradebug poke
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>

打赏

对不起,这篇文章暂时关闭评论。