Oracle个人技巧 -- 分布式事务处理

1. Identify the id of the transaction:

column global_tran_id format a25
column database format a22
column global_name format a22
SELECT * from global_name;
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

2. Purge the transaction:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
COMMIT;

3. Confirm that the transaction has been purged:

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

其中有如下5中state：
Collecting
--execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255'）；
prepared
--rollback force tran_id/commit force tran_id;
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
committed
--execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
forced commit
--execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
forced rollback
--execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');

如果遇到ORA-30019错误，可以采取如下方式：
alter session set "_smu_debug_mode" = 4;
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');

情况1 在dba_2pc_pending表中还有事务记录，但是实际已经不存在该事务了

SELECT LOCAL_TRAN_ID,
GLOBAL_TRAN_ID,
to_char(FAIL_TIME, 'dd-mon-yyyy HH24:MI:SS'),
STATE,
MIXED
FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID 1.92.66874 prepared

1 为回滚段号

SELECT KTUXEUSN,
KTUXESLT,
KTUXESQN, / Transaction ID /
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta != 'INACTIVE'
AND ktuxeusn = 1

返回为0

如果当状态为prepared，且事务表中也不存在相关信息，那么我们只能手工进行清理：
---使用如下方式进行手工处理：
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id = '1.92.66874';
delete from sys.pending_sessions$ where local_tran_id = '1.92.66874';
delete from sys.pending_sub_sessions$ where local_tran_id = '1.92.66874';
commit;

情况2 在dba_2pc_pending 表中无法查到分布式事务信息，但是实际上却是存在该分布式事务的

SELECT LOCAL_TRAN_ID,
GLOBAL_TRAN_ID,
to_char(FAIL_TIME, 'dd-mon-yyyy HH24:MI:SS'),
STATE,
MIXED
FROM DBA_2PC_PENDING;

---查询无记录
select local_tran_id, state
from dba_2pc_pending
where local_tran_id = ' 1.92.66874 '; --为空

SELECT KTUXEUSN,
KTUXESLT,
KTUXESQN, / Transaction ID /
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta != 'INACTIVE'
AND ktuxeusn = 1;

---查询有记录

此种情况下，我们无法手工进行rollback或commit、

我们用如下的方式手工清理：

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
(' 1.92.66874 ',
306206,
' XXXXXXX.12345.1.2.3 ',
' prepared ',
' P ',
hextoraw(' 00000001 '),
hextoraw(' 00000000 '),
0,
sysdate,
sysdate);

insert into pending_sessions $
values
(' 1.92.66874 ',
1,
hextoraw(' 05004F003A1500000104 '),
' C ',
0,
30258592,
'',
146);

commit ;

commit force ' 1.92.66874 ' ;

此时如果commit force还是出现报错，需要继续执行：

1.delete from pending_trans $ where local_tran_id = '1.92.66874' ;
2.delete from pending_sessions $ where local_tran_id = '1.92.66874' ;
3.commit ;
4.alter system enable distributed recovery ;
5.alter session set " _smu_debug_mode " = 4 ;
6.exec dbms_transaction . purge_lost_db_entry ( '1.92.66874' )

另外我们还可以通过如下sql来捕获到导致分布式事务失败的sql：

---获取local_tran_id
select a.sql_text, s.osuser, s.username
from v$transaction t, v$session s, v$sqlarea a
where s.taddr = t.addr
and a.address = s.prev_sql_addr
and t.xidusn = 1
and t.xidslot = 25
and t.xidsqn = 589367;

如果v$session和v$sqlarea已经无法查到，那么我们还可以关联一些dba_hist_*试图进行查询。