Oracle 分布式事务定位源头SQL

此案例在数据字典视图 DBA_2PC_PENDING 中存在一个不确定的分布式事务。如何找到发起此事务的用户以及事务的 SQL 语句。 通过DBA_2PC_PENDING 的对应的 COMMIT#和 事务ID，结合Logminer 来检查事务所在的在线重做日志或归档重做日志，以确定用户名和 SQL 文本。具体示例如下：

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

使用 COMMIT#，检查事务是否在在线重做日志中（查询 V$LOG 和 V$LOGFILE）或在归档重做日志中（查询 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             /u01/oradata/test/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              /u01/arch/arch1_55_937607111.dbf 使用Logminer进行挖掘：

SQL> show user USER is "SYS" SQL> exec dbms_logmnr.add_logfile(' /u01 /arch/arch1_55_937607111.dbf');

PL/SQL procedure successfully completed.

使用 DBA_2PC_PENDING查询 local_tran_id带入查询

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>

最终定位事务1.16.942是由SCOTT用户执行了EMP表的insert操作