关于ORA-01591问题

CODE_随心

系统中偶尔会出现ORA-01591  分布式事务锁的问题。每次把事务回滚了。隔一段时间（几天或是十几天）又出现。

哪位大神可以告诉我ORA-01591 产生的原因。以及解决方案么？

对于ORACLE  还不是很精。所以请尽量描述详细点。谢谢

CODE_随心

这个问题一直存在，但又不知道怎样人为的去重现。
我最最想知道的就是ORA_01591 倒底是怎样产生的。哪些场景会报ORA_01591错误...每种场景报错后对应的处理方式。有哪位大神赐教一下呀。

CODE_随心

求指教哇。。

CODE_随心

求指教哇。。

CODE_随心

求指教哇。。

anonymous0502

不懂，帮顶
ORA-01591: lock held by in-doubt distributed transaction string
Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action: DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

bbliu001

I got the followings from Oracle Forums. It works for others. Good Luck!!

When you got ORA-1591 error, first of all, check the view DBA_2PC_PENDING. COMMIT FORCE or ROLLBACK FORCE the in-doubt transactions.

Sometimes, it is not possible because you do not have rows in DBA_2PC_PENDING view. If this is the case, you can insert dummy rows into SYS.PENDING_TRANS$, and SYS.PENDING_SESSIONS$ tables to correct this. Below is a script that can help you if you do not have rows in DBA_2PC_PENDING view. Just enter the TRANSACTION ID, got from ORA-1591 error or from alertlog files.

--
-- Execute it connected as SYS or SYSDBA
--
ACCEPT TRANSACTION_ID PROMPT "Enter TRANSACTION ID: "
alter system disable distributed recovery;
insert into pending_trans$ (
LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
values( '&&TRANSACTION_ID',
306206, /* */
'XXXXXXX.12345.1.2.3', /* These values can be used without any */
'prepared','P', /* modification. Most of the values are */
hextoraw( '00000001' ), /* constant. */
hextoraw( '00000000' ), /* */
0, sysdate, sysdate );
insert into pending_sessions$
values( '&&TRANSACTION_ID',
1, hextoraw('05004F003A1500000104'),
'C', 0, 30258592, '',
146
);
commit;
rollback force '&&TRANSACTION_ID'; /* or commit force */
commit;
alter system enable distributed recovery;
exec dbms_transaction.purge_lost_db_entry( '&&TRANSACTION_ID' );
commit;
delete from pending_trans$ where local_tran_id='&&TRANSACTION_ID';
delete from pending_sessions$ where local_tran_id='&&TRANSACTION_ID';
commit;

flutter

这个错误遇到过，常见于rac系统，估计是进程和资源占用死锁造成得，没其他办法，

楼上得英文办法貌似就是手工杀死死锁进程，不知道理解正确否

CODE_随心

回复 7# bbliu001


    我知道解决这个问题可以把事务手工提交，或强制回滚了。

    但这样，治标不治本。隔一段时间又会出现。我想把出现这个问题的根源找出来。

aindk

不太清楚什么应用，

但是我曾经见过类似的错误，大概是 好像 可能是 在 select * from .... for update 时候 修改了数据没有 commit

这种情况要看应用的，

建议看看 用户 的授权是什么情况，若是不需要修改数据，建议不要配置太大的权限


还有若是有条件咨询 一下oracle 的技术支持，