dba_2pc_pending

admin

分布式事务的手工处理主要按 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$ 被执行过删除操作，此时需要向表中重新插回伪造的数据后再进行强制回滚或提交。

admin

1. set markup html on
   
2. spool dist_queries.html
   
3. 
   
4. show user
   
5. 
   
6. select name from v$DATABASE;
   
7. 
   
8. select * from v$version;
   
9. 
   
10. SELECT * FROM DBA_DB_LINKS;
    
11. 
    
12. SELECT * FROM GLOBAL_NAME;
    
13. 
    
14. select * from DBA_2PC_PENDING;
    
15. select * from DBA_2PC_NEIGHBORS;
    
16. select * from sys.pending_trans$;
    
17. select * from SYS.PENDING_SESSIONS$;
    
18. select * from SYS.PENDING_SUB_SESSIONS$;
    
19. select * from V$GLOBAL_TRANSACTION;
    
20. 
    
21. SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
    
22.           KTUXESTA Status,
    
23.           KTUXECFL Flags
    
24.    FROM sys.x$ktuxe
    
25.    WHERE ktuxesta!='INACTIVE';
    
26. 
    
27. spool off
    
28. set markup html off.

复制代码

admin

=====查看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');

admin

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