dba_2pc_pending
分布式事务的手工处理主要按 dba_2pc_pending 的结果来灵活对应。
当 dba_2pc_pending 中的数据时，主要看 state字段，
1） 如果 state状态是prepared，表示事务未提交或回滚，此时需要手工进行强制回滚或提交。
ROLLBACK FORCE 'transaction_id';
-OR-
COMMIT FORCE 'transaction_id','commit#';
2）如果state状态是committed, rollback forced或者commit forced状态，表示事务已经完成了，但是在FORGET阶段处理时，数据库字典的信息没能及时清除。此时，我们调用oracle的清理丢失事务信息的语句就可以完成处理：
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.26.883');
Commit;
3 ） 当 dba_2pc_pending 中无数据时
有可能基表 pending_trans$ ， pending_sessions$ 被执行过删除操作，此时需要向表中重新插回伪造的数据后再进行强制回滚或提交。
set markup html on
spool dist_queries.html
show user
select name from v$DATABASE;
select * from v$version;
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM GLOBAL_NAME;
select * from DBA_2PC_PENDING;
select * from DBA_2PC_NEIGHBORS;
select * from sys.pending_trans$;
select * from SYS.PENDING_SESSIONS$;
select * from SYS.PENDING_SUB_SESSIONS$;
select * from V$GLOBAL_TRANSACTION;
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM sys.x$ktuxe
WHERE ktuxesta!='INACTIVE';
spool off
set markup html off. =====查看DB_LINK会话信息
思路：由于db-link会话会在源端启用global transaction，并且在回滚段中进行标记, x$k2gte视图提供了全局性事务，包含了从本地发出以及接收的db-link transaction信息，其中“K2GTITID_ORA”字段为全局事务id，“K2GTDSES”为会话地址。
--源端根据SQL（sql_id: xxx）查询会话SID、sql_id、会话地址等信息：
SQL> select saddr,sid,sql_id,module,program,machine from gv$session where sql_id = '&sql_id';
--源端根据会话地址，查询到全局事务ID：
SQL> select inst_id,K2GTITID_ORA ,K2GTDSES from x$k2gte where K2GTDSES in ('&saddr');
--登录目标端（使用DB_LINK的）数据库，根据事务ID，目标端查看会话信息：
SQL> select inst_id,K2GTITID_ORA ,K2GTDSES from x$k2gte where K2GTITID_ORA in ('&source_K2GTITID_ORA');
--根据会话地址查询会话sql_id，以及终端详细信息：
select saddr,sid,serial#,sql_id,module,program,machine,osuser from gv$session where saddr in ('&saddr'); How to Check the User and the SQL text (DML) of an In-doubt Distributed Transaction found in dba_2pc_pending (Doc ID 1327159.1)
Having the transaction_id of the in-doubt transaction and the corresponding COMMIT# from DBA_2PC_PENDING , use Logminer to examine the online redo log(s) or the archived redo log(s) where the transaction is in , to determine the user name and the SQL text.
Example:
SQL> show user
USER is "SYS"
SQL> select local_tran_id, commit#, state
2 from DBA_2PC_PENDING;
LOCAL_TRAN_ID COMMIT# STATE
---------------------- ---------------- ----------------
1.16.942 1585840 prepared
SQL>
With the COMMIT# , check whether the transaction is in Online Redo logs (query V$LOG and V$LOGFILE) or in Archived Red logs (query V$ARCHIVED_LOG)
Online Redo:
SQL> select a.thread#, a.sequence#, b.member
2 from v$log a, v$logfile b
3 where a.group# = b.group# and
4 1585840 between a.FIRST_CHANGE# and a.NEXT_CHANGE#;
THREAD# SEQUENCE#
---------- ----------
MEMBER
--------------------------------------------------------------------------------
1 55
/bugmnt3/am/celcaix4/app/oracle/oradata/TARJP/redo01.log
Archived Redo:
SQL> select thread#, sequence#, name
2 from v$archived_log
3 where 1585840 between FIRST_CHANGE# and NEXT_CHANGE#;
THREAD# SEQUENCE#
---------- ----------
NAME
--------------------------------------------------------------------------------
1 55
/bugmnt3/am/celcaix4/app/oracle/product/11.2.0.4.0/dbs/arch1_55_937607111.dbf
Now user Logminer to examine the redo, for example:
SQL> show user
USER is "SYS"
SQL> exec dbms_logmnr.add_logfile('/bugmnt3/am/celcaix4/app/oracle/product/11.2.0.4.0/dbs/arch1_55_937607111.dbf');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> select OPERATION, TABLE_NAME, SQL_REDO, USERNAME
2 from V$LOGMNR_CONTENTS
3 where XIDUSN=1 and
4 XIDSLT = 16 and
5 XIDSQN=942 order by SCN asc;
OPERATION TABLE_NAME
-------------------------------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
USERNAME
------------------------------
START
set transaction read write;
SCOTT
INSERT EMP
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('1041','MULDER',NULL,NULL,NULL,NULL,NULL,'10');
SCOTT
INTERNAL
SCOTT
INTERNAL
SCOTT
SQL>
As seen above, the in-doubt transaction 1.16.942 was executed by SCOTT and was an INSERT into SCOTT.DEPT
页:
[1]