transaction recovery问题
本帖最后由 makaveli 于 2013-4-10 15:30 编辑有一套两节点11.1.0.7 rac solaris asm的数据库alert日志报如下错误:
Transaction recovery: lock conflict caught and ignored
通过查看v$fast_start_transactions视图发现一只有个transaction在recovering，但是一直都没成功.查看等待事件发现smon处于dfs lock handle,已经修改fast_start_parallel_rollback为false，想请教下现在能如何确定这些recover的事务能够rollback成功？select * from v$fast_start_transactions;
--
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
1 10 19 9816080 RECOVERING 0 138563 683616 000A00130095C810 0select ktuxeusn usn,
ktuxeslt slot,
ktuxesqn seq,
ktuxesta state,
ktuxesiz undo
from x$ktuxe a
where ktuxesta <> 'INACTIVE'
and ktuxecfl like '%DEAD%'
order by ktuxesiz asc;
--
USN SLOT SEQ STATE UNDO
10 19 9816080 ACTIVE 138563
select useg.segment_name,
useg.segment_id,
useg.tablespace_name,
useg.status
from dba_rollback_segs useg
where useg.segment_id in
(select ktuxeusn usn
from x$ktuxe a
where ktuxesta <> 'INACTIVE'
and ktuxecfl like '%DEAD%')
--
SEGMENT_NAME SEGMENT_ID TABLESPACE_NAME STATUS
_SYSSMU10_1265347054$ 10 UNDOTBS1 ONLINE
--
select rl.usn,rl.name from v$rollname rl where usn in (select ktuxeusn usn
from x$ktuxe a
where ktuxesta <> 'INACTIVE'
and ktuxecfl like '%DEAD%')
--
USN NAME
10 _SYSSMU10_1265347054$
--
select p1,chr(bitand(1413545989,-16777216)/16777215) ||
chr(bitand(1413545989,16711680)/65535) "LOCK",
to_char(bitand(1413545989,65535)) "mode",
p2,
p3,
seconds_in_wait
from v$session_wait a
where a.EVENT = 'DFS lock handle';
LOCK mode P2 P3 SECONDS_IN_WAIT
1 TA 5 3 28 0
--
select b.name useg,b.inst# inst#,b.status$ status,a.ktuxeusn xid_usn,a.ktuxeslt xid_slot,a.ktuxesqn xid_seq,a.ktuxesiz undoblocks,a.ktuxesta txstatus
from x$ktuxe a,undo$ b
where ktuxesta <> 'INACTIVE'
and ktuxecfl like '%DEAD%'
and a.ktuxeusn=b.us#
USEG INST# STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS TXSTATUS
1 _SYSSMU10_1265347054$ 1 3 10 19 9816080 138563 ACTIVE
_SYSSMU10_1265347054$ undo header信息上传附件了。
select * from v$session_longops where sofar<totalwork
+
smon trace上传 看看 Maclean Liu(刘相兵 发表于 2013-4-10 15:25 static/image/common/back.gif
select * from v$session_longops where sofar
查询v$session_longops没有返回结果。smon trace已经上传，刚才做了一个10046。
看trace里
WAIT #0: nam='DFS lock handle' ela= 684 type|mode=1413545989 id1=3 id2=20 obj#=0 tim=12785304193330
WAIT #0: nam='DFS lock handle' ela= 566 type|mode=1413545989 id1=3 id2=28 obj#=0 tim=12785304194130
DFS lock handle 已经结束了啊
你查一下是不是回滚完了 Maclean Liu(刘相兵 发表于 2013-4-10 15:56 static/image/common/back.gif
看trace里
WAIT #0: nam='DFS lock handle' ela= 684 type|mode=1413545989 id1=3 id2=20 obj#=0 tim=1278 ...
好像没有，查看v$fast_start_transactions视图,状态还是RECOVERING，UNDOBLOCKSDONE是0，UNDOBLOCKSTOTAL是138563还，而且alert里还一直报Transaction recovery: lock conflict caught and ignored这个错误。USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
1 10 19 9816080 RECOVERING 0 138563 683616 000A00130095C810 0 最近我也遇到这个问题,到现在还没解决。
搜索相关资料说要重建那几个对象，不知道可行不。 参考资料
http://dba010.wordpress.com/2013/04/30/transaction-recovery-lock-conflict-caught-and-ignored/
页:
[1]