3、 查询视图dba_2pc_pending，查询悬挂事务信息：
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
FROM DBA_2PC_PENDING
WHERE LOCAL_TRAN_ID = '??.';
如果没有记录，说明RECO进程已经自动处理了该事务。
4、 在所有节点上查询视图dba_2pc_neighbors
5、 得到所有节点的COMMIT_POINT_STRENGTH值，值最大的为commit point site，即最早提交的点，如果悬挂事务发生在commit point site，则它的state决定了整个分布式事务的状态。悬挂事务是否应该commit force或者是rollback force，由此节点决定。
6、 检查dba_2pc_pending的state列，如果是commited，意味着本地数据库提交已经成功。其他节点需要根据本地事务号和最大的commit#进行强制提交。用法如下：
SVRMGR> COMMIT FORCE 'your local transactionID on this node', 'highest SCN from already committed site';
SVRMGR> COMMIT FORCE '1.13.5197', '88123887';
7、 如果commit point site的state为commited外的其他状态，则表明commit point site 没有提交成功，分布式事务需要强制回滚。这里不再需要所有节点的最大commit#。用法如下：
SVRMGR> ROLLBACK FORCE 'your local transactionID on this node';
SVRMGR> ROLLBACK FORCE '1.13.5197';
8、 清除dba_2pc_pending和dba_2pc_neighbers的相关记录。一般分布式事务自动恢复后，视图内容会自动清除，如果是手工提交的事务，则需要用dbms_transaction包手工清除，清除规则如下表所示：
确定何时能使用DBMS_TRANSACTION
Collecting
Purge_lost_db_entry(只有当自动回复不能解决事务时)
Committed
Committed
Committed
Purge_lost_db_entry(只有当自动回复不能解决事务时)
Forced
Commit
Committed
Purge_lost_db_entry(只有当自动回复不能解决事务时)
Forced rollback
Purge_lost_db_entry(只有当自动回复不能解决事务时)
Forced commit
Committed
手动删除不一致性﹐然后使用purge_mixed
Forced rollback
手动删除不一致性﹐然后使用purge_mixed
测试记录
? 设置db1的commit_point_strength为1，db2的commit_point_strength为2，db2为commit point site。
? db1、db2上执行100次insert循环，每次循环用分布式事务插入db1和db2中的测试表。中间reboot db2服务器。此时db1对测试表的查询出现以下错误：
SQL> select count(1) from temp.my_table;
select count(1) from temp.my_table
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 7.30.7415
[oracle@db2 bdump]$ tail -f alert_ntespay.log
Tue Mar 4 14:14:28 2008
DISTRIB TRAN 1234.4F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
is local tran 7.30.7415 (hex=07.1e.1cf7)
insert pending prepared tran, scn=934346533 (hex=0.37b0ff25)
db1中分布式事务相关的2个视图内容如下：
select a.* from dba_2pc_pending a where LOCAL_TRAN_ID='7.30.7415';
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED ADVICE TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL HOST DB_USER COMMIT#
1 7.30.7415 4660.4F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 prepared no 2008-3-4 14:14:28 2008-3-4 14:22:56 zhenxingzhai ZHAIZHENXING NETEASE\ZHAIZHENXING 934346533
其中，
state有以下几种状态：
Collecting, prepared, committed, forced commit, or forced rollback
mixed表示是否部分提交，部分回滚
advice：
C
for commit,
R
for rollback, else
NULL
select a.* from dba_2pc_neighbors a where LOCAL_TRAN_ID='7.30.7415';
LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INTERFACE DBID SESS# BRANCH
1 7.30.7415 in NULLjavaxa.oracle.com TEMP N javaxa_orcl 1 01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
此视图说明了数据源1的输入连接信息。因为数据源2不是通过dblink连接的，以此没有出现它的记录。
? db2重启后查询my_tab：
SQL> select count(1) from my_tab;
COUNT(1)
----------
75
? 因为db2中dba_2pc_pending和dba_2pc_neighbers中没有记录，并且db2为commit point site，没有记录意味着没有进行任何操作，所以db1应该和db2一样，进行强制rollback。
SQL> conn / as sysdba
Connected.
SQL> rollback force '7.30.7415';
Rollback complete.
SQL> select count(12) from temp.my_table;
COUNT(12)
----------
75
db1的alert日志中显示了可疑事务的回滚过程：
Tue Mar 4 15:14:31 2008
DISTRIB TRAN 1234.4F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
is local tran 7.30.7415 (hex=07.1e.1cf7)
change pending prepared tran, scn=934346533 (hex=0.37b0ff25)
to pending forced rollback tran, scn=934346533 (hex=0.37b0ff25)
? 回滚后，两个视图中的状态更改为如下：
select a.* from dba_2pc_pending a where LOCAL_TRAN_ID='9.33.5992';
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED ADVICE TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL HOST DB_USER COMMIT#
1 7.30.7415 4660.4F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 forced rollback no 2008-3-4 14:14:28 2008-3-4 15:14:31 2008-3-4 15:20:07 zhenxingzhai ZHAIZHENXING NETEASE\ZHAIZHENXING 934346533
select a.* from dba_2pc_neighbors a where LOCAL_TRAN_ID='9.33.5992';
LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INTERFACE DBID SESS# BRANCH
1 7.30.7415 in NULLjavaxa.oracle.com TEMP N javaxa_orcl 1 01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
? 去除dba_2pc_pending和dba_2pc_ neighbors中的记录：
（1） Disable分布式恢复
SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
System altered.
（2）Puege(清空)in-doubt transaction entry：
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.30.7415');
PL/SQL procedure successfully completed.
（3）commit;
（4）然后enable 分布式恢复：
SQL> ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
分布式事务相关资料
Note:1012842.102
Note:100664.1
Note:274321.1
Note:126069.1
责任编辑：小草
您现在的位置： 