Oracle分布式事务故障处理

分布式事务,简单来说,是指一个事务在本地和远程执行,本地需要等待确认远程的事务结束后,进行下一步本地的操作。如通过dblink update远程数据库的一行记录,如果在执行过程中网络异常,或者其他事件导致本地数据库无法得知远程数据库的执行情况,此时就会发生in doublt的报错。此时需要dba介入,且需要分多种情况进行处理。分布式事务在commit提交时候,会经历3个阶段:
1.PREPARE PHASE
1.1 决定哪个数据库为commit point site。(注,参数文件中commit_point_strength值高的那个数据库为commit point site)
1.2 全局协调者(Global Coordinator)要求所有的点(除commit point site外)做好commit或者rollback的准备。此时,对分布式事务的表加锁。
1.3 所有分布式事务的节点将它的scn告知全局协调者。
1.4 全局协调者取各个点的最大的scn作为分布式事务的scn。(eygle在这篇文章中也测试过)

至此,所有的点都完成了准备工作,我们开始进入COMMIT PHASE阶段,此时除commit point site点外所有点的事务均为in doubt状态,直到COMMIT PHASE阶段结束。

如果数据库在此阶段出现问题,我们查询(假设远程数据库为commit point site,且本地数据库为Global Coordinator):

本地:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845              collecting

远程:

select local_tran_id,state from dba_2pc_pending;
no rows selected

即表示本地数据库要求其他点做好commit或者rollback准备,现在正在“收集”其他点的数据库的返回信息,但是远程数据库未知状态(in doubt)。我们需要将本地的Global Coordinator的状态清除掉:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

或者我们在查询的时候发现是如下的状态:

本地:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845             prepared

远程:

select local_tran_id,state from dba_2pc_pending;
no rows selected

即表示本地Global Coordinator已经做好准备,已经将分布式锁放到各个事务的表上,但是远程数据库的状态再次未知(in doubt),我们需要手工的将本地的transaction rollback掉,并且清除分布式事务信息:
本地:

rollback force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

2.COMMIT PHASE
2.1 Global Coordinator将最大scn传到commit point site,要求其commit。
2.2 commit point尝试commit或者rollback。分布式事务锁释放。
2.3 commit point通知Global Coordinator已经commit。
2.4 Global Coordinator通知分布式事务的所有点进行commit。

如果数据库在此阶段出现问题,我们查询

本地:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845             prepared

远程:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             commited

即远程数据库可能已经commit,但是本地Global Coordinator未知远程数据库的状态,还是处于prepare的状态。我们需要在如下处理:

本地:

commit force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

远程:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

或者我们在查询的时候发现是如下的状态:

本地:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845            commited

远程:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             commited

即远程数据库和本地数据库均已经完成commit,但是分布式事务的信息尚未清除,我们需要在本地和远程运行:

本地:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

远程:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

3.FORGET PHASE
3.1 参与的点通知commit point site他们已经完成commit,commit point site就能忘记(forget)这个事务。
3.2 commit point site在远程数据库上清除分布式事务信息。
3.3 commit point site通知Global Coordinator可以清除本地的分布式事务信息。
3.4 Global Coordinator清除分布式事务信息。

此时如果出现问题,我们查询:

本地:

select local_tran_id,state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
2.12.64845            commited

远程:

select local_tran_id,state from dba_2pc_pending;
no rows selected

即远程commit point site已经完成commit,通知Global Coordinator清除本地的分布式事务信息,但是Global Coordinator没有收到该信息。我们需要这样处理:

本地:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

综上,分布式事务的依次状态为:

phase       local_state    remote_state       action
----------- ---------- ------------------  --------------------------------------------
prepare     collecting       /              本地DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY
            prepared         /              本地rollback force后PURGE_LOST_DB_ENTRY
 
commit      prepared        commited        本地commit force后本地和远程均PURGE
            commited        commited        本地和远程均PURGE_LOST_DB_ENTRY
   
forget      commited         /              本地PURGE_LOST_DB_ENTRY

另,当我们遇到使用rollback/commit force的时候,无法正常的清除分布式事务的信息,会报错ORA-02058: no prepared transaction found with ID X.XX.XXXXX时,我们需要通过手工方式来清除该信息。
(注,以下方式修改数据字典,存在风险,使用前请备份好你的数据库)

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

select local_tran_id, state from dba_2pc_pending;
LOCAL_TRAN_ID          STATE
---------------------- ----------------
1.92.66874             prepared

(注:’1.92.66874′的结构为rbs#, slot#, wrap#,此事务在rollback segment #1)

我们再用如下语句找出使用rollback segment #1且状态是active的transaction:

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
       KTUXESTA Status,
       KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
      AND ktuxeusn= 1; <== 这是rollback segment#,即rbs#
no rows selected

因此我们在rollback force的时候会报错:

ORA-02058: no prepared transaction found with ID 1.92.66874
我们需要如下处理:

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表中无法查到分布式事务信息,但是实际上却是存在该分布式事务的:

我们在alertlog中可以看到:

ORA-1591: lock held by in-doubt distributed transaction 1.92.66874
我们查询dba_2pc的表,发现没有分布式事务信息:

select local_tran_id, state from dba_2pc_pending 
where local_tran_id='1.92.66874';
no rows selected

但是去查实际的rollback segment信息,却发现有prepared状态的分布式事务存在:

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
       KTUXESTA Status,
       KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
      AND ktuxeusn= 1;
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         1         92      66874 PREPARED         SCO|COL|REV|DEAD

我们无法做commit force或者rollback force:

rollback force '1.92.66874';
ORA-02058: no prepared transaction found with ID 1.92.66874

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

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', /* <== 此处为你的local tran id */
    306206,                  /*                                         */
    'XXXXXXX.12345.1.2.3',   /*  这些值不必更改,   */
    'prepared','P',          /*  是静态参数,可以直接   */
    hextoraw( '00000001' ),  /*  在这个sql中使用                             */
    hextoraw( '00000000' ),  /*                                         */
    0, sysdate, sysdate );
 
insert into pending_sessions$
values( '1.92.66874',/* <==此处为你的local tran id  */
    1, hextoraw('05004F003A1500000104'),
    'C', 0, 30258592, '',
    146
  );
 
commit;
 
commit force '1.92.66874';

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

delete from pending_trans$ where local_tran_id='1.92.66874';
delete from pending_sessions$ where local_tran_id='1.92.66874';
commit;
alter system enable distributed recovery;

此时如果没有报错,则执行以下语句:

alter system enable distributed recovery;
and purge the dummy entry from the dictionary, using
connect / as sysdba
alter session set "_smu_debug_mode" = 4;
--注:如果使用auto的undo管理方式,需要执行此步骤,此步骤能避免在后续执行
--purge_lost_db_entry出现ORA-01453 的报错,详细信息可见Bug 2191458
commit; 
exec dbms_transaction.purge_lost_db_entry('1.92.66874');

原文:http://www.dbifan.com/200812/how-to-deal-with-distributed-transaction.html

Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable

1、检查alert日志发现错误

Wed Nov 30 13:36:47 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:48 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:48 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:49 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:50 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:51 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:52 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:52 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:53 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:54 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:55 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:56 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:56 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:57 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:57 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:36:58 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 13:37:12 2011
Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable
Wed Nov 30 22:00:09 2011
…………
Wed Nov 30 22:00:15 2011
GATHER_STATS_JOB encountered errors.  Check the trace file.
Wed Nov 30 22:00:15 2011
Errors in file /opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc:
ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK"  or partition of such index is in unusable state

2、查看trace文件

/opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:        x86_64
Instance name: ora9i
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 21372, image: oracle@localhost.localdomain (J001)

*** 2011-11-30 22:00:15.782
*** ACTION NAME:(GATHER_STATS_JOB) 2011-11-30 22:00:15.763
*** MODULE NAME:(DBMS_SCHEDULER) 2011-11-30 22:00:15.763
*** SERVICE NAME:(SYS$USERS) 2011-11-30 22:00:15.763
*** SESSION ID:(532.60095) 2011-11-30 22:00:15.763
ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK"  or partition of such index is in unusable state
*** 2011-11-30 22:00:15.782
GATHER_STATS_JOB: GATHER_TABLE_STATS('"VAS"','"TAB_XN_CALLLOG_ANALYSIS"','""', ...)
ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK"  or partition of such index is in unusable state

3、日志初步结论
通过alert日志,感觉应该是对分区表操作,导致”VAS”.”XN_CALLLOG_ANALYSIS_PK”索引变成了unusable state,然后在数据库自动收集统计信息的时候报错(最大可能是全局index导致)

4、验证猜测是否正确

SQL> SELECT owner,index_name,table_name,status FROM DBA_indexes 
   2 WHERE index_name='XN_CALLLOG_ANALYSIS_PK' AND owner='VAS';
 
OWNER                          INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
VAS                            XN_CALLLOG_ANALYSIS_PK         TAB_XN_CALLLOG_ANALYSIS        UNUSABLE

5、解决相关问题问题

SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME ||
'REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;'
FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
AND STATUS = 'UNUSABLE'
UNION ALL
SELECT 'alter index ' ||OWNER || '.' || A.INDEX_NAME || ' REBUILD online nologging;'
FROM DBA_INDEXES A
WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
AND STATUS = 'UNUSABLE';

执行生成sql,解决相关index unusable问题