在v$lock试图中出现RT锁

dba_sky

环境：AIX5.3 + 10G RAC 10.2.0.5

业务一段不能进行，检查v$lock发现RT锁
SQL> select * from v$lock where lmode>4;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
070000028A0D1488 070000028A0D14A8       2245 RT          1          0          6          0    5420845          2
070000028A0D1728 070000028A0D1748       2245 RT          1          1          6          0    5420842          2
070000028A0D17D0 070000028A0D17F0       2247 RT          1          2          6          0    5420842          2

进一步检查发现2245对应的是lgwr后台进行

SQL> select paddr from v$session where sid=2245;

PADDR
----------------
0700000292663148

SQL> select spid from v$process where addr='0700000292663148';

SPID
------------
499872


$ ps -ef | grep 499872
  oracle  499872       1   1   Aug 30      - 67:03 ora_lgwr_bfgfdb1
  oracle 1114306 2158632   0 16:54:02  pts/4  0:00 grep 499872
  
这个需要如何处理呢？

dump.zip (7.2 MB, 下载次数: 517)

2013-11-1 17:09 上传

点击文件名下载附件

  

Maclean Liu(刘相兵

10.2.0.5.0 +   AIX 5.3  +RAC

*** 2013-10-26 11:01:21.919
Warning: log write time 8366947190ms, size 1KB
*** 2013-11-01 15:13:51.712
Warning: log write broadcast wait time 1090ms
*** 2013-11-01 16:05:28.100
Warning: log write broadcast wait time 650ms
*** 2013-11-01 16:05:34.498
Warning: log write broadcast wait time 1040ms
*** 2013-11-01 16:05:52.921
Warning: log write broadcast wait time 540ms
*** 2013-11-01 16:05:58.613
Warning: log write broadcast wait time 1150ms
*** 2013-11-01 16:06:01.487
Warning: log write broadcast wait time 510ms
*** 2013-11-01 16:06:01.487
Warning: log write broadcast wait time 510ms
*** 2013-11-01 16:09:46.381
Warning: log write broadcast wait time 550ms
*** 2013-11-01 16:56:20.778
Received ORADEBUG command 'unlimit' from process Unix process pid: 4460786, image:
Received ORADEBUG command 'unlimit' from process Unix process pid: 4460786, image:

在11-01 当天发生过多次 Log write BOC 等待

Maclean Liu(刘相兵

很可惜 你做 systemstate dump 的时候LGWR已经没等待了

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. (The holder may have released the resource before we
           dumped the state object tree of the blocking process).
         o Lines with 'Enqueue conversion' below can be ignored *unless*
           other sessions are waiting on that resource too. For more, see
           http://dlsunuk11.uk.oracle.com/Public/TOOLS/Ass.html#enqcnv)

                    Resource Holder State
    Enq TX-0032000B-0001FA07    ??? Blocker
           Buffer 0x07807df3   705: waiting for 'gc buffer busy'

Blockers According to Tracefile Wait Info:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. This may not work for 64bit platforms. See bug 2902997 for details.
2. If the blocking process is shown as 0 then that session may no longer be
   present.
3. If resources are held across code layers then sometimes the tracefile wait
   info will not recognise the problem.

No blockers seen.

Object Names
~~~~~~~~~~~~
Enq TX-0032000B-0001FA07                                      
Buffer 0x07807df3     


，你可以上传当时的AWR

dba_sky

附件是当时的AWR报告
ashrpt_1_1029_1120.html (36.19 KB, 下载次数: 443)

2013-11-3 08:58 上传

点击文件名下载附件

awrrpt_1_16599_16601.html (356.78 KB, 下载次数: 470)

2013-11-3 08:58 上传

点击文件名下载附件

Maclean Liu(刘相兵

Elapsed:                  120.10 (mins)                  
DB Time:                  20.34 (mins)                  

负载很低， 也没有看到明显的前后台因为 enq:RT而等待的，如果这确实是你遇到问题的时的快照的话，我认为你可以忽略该问题


Top User Events
Event        Event Class        % Event        Avg Active Sessions
CPU + Wait for CPU        CPU        64.71        0.04
log file sync        Commit        5.88        0.00
Back to Top Events
Back to Top


Top Background Events
Event        Event Class        % Activity        Avg Active Sessions
control file parallel write        System I/O        11.76        0.01
CPU + Wait for CPU        CPU        5.88        0.00
db file async I/O submit        System I/O        5.88        0.00
log file parallel write        System I/O        5.88        0.00

dba_sky

好的，谢谢ML