sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】ORACLE分布式事务故障的处理 结合MOS官方文档分析

时间:2016-10-25 10:59   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库分布式事务故障处理的文章,结合MOS官方文档进行统计分析故障。
PURPOSE
——-

The purpose of this bulletin is to assist support analysts in understanding and resolving the stranded dba_2pc_entries.

SCOPE & APPLICATION
——————-

The intended audience are support analysts having good experience on distributed databases.

TITLE
—–

How to resolve stranded dba_2pc_pending entries


Contents
========

1. Problem Description
2. Solutions
2.1 Dba_2pc entries without a corresponding transaction
2.2 Distributed transaction without corresponding dba_2pc entries

1. Problem Description:
=======================

As a result of a failed commit of a distributed transaction, some entries can be left in dba_2pc views, i.e. dba_2pc_pending and dba_2pc_neighbors. The RECO process checks these views to recover the failed txn. However, in some cases RECO cannot perform the recovery. One cause is that all sites involved in the transaction not being accessible at the same time. Another cause is dba_2pc views being inconsistent with the transaction table, which is the topic of this article. This cause can further be classified as follows:

1. dba_2pc views have entries for a non-existent distributed transaction
2. There is a distributed transaction for which there are no entries in dba_2pc views

Solutions to each subclass is provided in the rest of the article.


2. Solutions:
=============

2.1 Dba_2pc entries without a corresponding transaction
——————————————————-

In this case dba_2pc views show distributed transactions but there are no txns in reality. If the state of the transaction is committed, rollback forced or commit forced then this is normal and it can be cleaned up using

dbms_transaction.purge_lost_db_entry

However, if the state of the transaction is PREPARED and there iOracleoracleplus.nets no entry in the transaction table for it then this entry can be cleaned up manually as follows:

set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$
where local_tran_id = ;
delete from sys.pending_sessions$ where local_tran_id = ;
delete from sys.pending_sub_sessions$ where local_tran_id = ;
commit;

Example:
——–

The following query reports a dist. txn. in prepared state
select local_tran_id, state from dba_2pc_pending;

LOCAL_TRAN_ID STATE
———————- —————-
1.92.66874 prepared

Given that a transaction id is composed of triple, ‘1.92.66874’ is located in rollback segment# 1. To find out the list of active transactions in that rollback segment, use:
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 1; <== this is the rollback segment#

no rows selected
It is not possible to rollback force or commit force this transaction.
rollback force ‘1.92.66874’;
ORA-02058: no prepared transaction found with ID 1.92.66874
Hence, we have to manually cleanup that transaction:

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.2 Distributed transaction without corresponding dba_2pc entries
—————————————————————–

In this case dba_2pc views are empty but users are receiving distributed txn related errors, e.g. ORA-2054, ORA-1591. Normally such a case should not appear and if it is reproducible a bug should be filed. Here is the list of several alternative solutions that can be used in this case:

a. Perform incomplete recovery
b. Truncate the objects referenced by that transaction and import them refer to Note 76603.1 for this.
c. Use _corrupted_rollback_segments parameter to drop that rollback segment as discussed in Note 106638.1.
d. Insert dummy entries into dba_2pc views and either commit or rollback
force the distributed transaction

The first three solutions are discussed in Backup and Recovery manuals and in the notes referred above. In the 4th solution a dummy entry is inserted into the dictionary so that the transaction can be manually committed or rolled back.

Note that RECO will not be able to process this txn and distributed txn recovery should be disabled before using this method. Furthermore, please take a BACKUP of your database before using this method.

The following example describes how to diagnose and resolve this case. Suppose that users are receiving

ORA-1591: lock held by in-doubt distributed transaction 1.92.66874

and the following query returns no rows:

select local_tran_id, state from dba_2pc_pending
where local_tran_id=’1.92.66874′;

no rows selected

Furthermore querying the rollback segment shows that 1.92.66874 remains in prepared state

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 1; /* <== Replace this value with your txn undo seg#
Which is displayed in the first part of
the transaction ID */

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
———- ———- ———- —————- ————————
1 92 66874 PREPARED SCO|COL|REV|DEAD


Trying to manually commit or rollback this transaction

commit force ‘1.92.66874’;

ORA-02058: no prepared transaction found with ID 1.92.66874

raises ORA-02058 since dba_2pc views are empty. In order to use commit force or
rollback force a dummy record should be inserted into pending_trans$ as follows:

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’, /* <== Replace this with your local tran id */
306206, /* */
‘XXXXXXX.12345.1.2.3’, /* These values can be used without any */
‘prepared’,’P’, /* modification. Most of the values are */
hextoraw( ‘00000001’ ), /* constant. */
hextoraw( ‘00000000’ ), /* */
0, sysdate, sysdate );

insert into pending_sessions$
values( ‘1.92.66874’,/* <==Replace only this with your local tran id */
1, hextoraw(‘05004F003A1500000104’),
‘C’, 0, 30258592, ”,
146
);

commit;

commit force ‘1.92.66874’;

If commit force raises an error then note the errormessage and execute the
following:

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;

Otherwise run

alter system enable distributed recovery;

and purge the dummy entry from the dictionary, using

connect / as sysdba

alter session set "_smu_debug_mode" = 4; /* if automatic undo management
is being used */
commit; /* this is to prevent the ORA-01453 in purge_lost_db_entry call */
exec dbms_transaction.purge_lost_db_entry( ‘1.92.66874’ )
本文固定链接: http://www.htz.pw/2014/06/19/oracle%e5%88%86%e5%b8%83%e5%bc%8f%e4%ba%8b%e5%8a%a1%e5%a4%84%e7%90%862%e5%bc%82%e5%b8%b8%e6%83%85%e5%86%b5.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】ORACLE分布式事务故障的处理 结合MOS官方文档分析

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1064.html

Oracle研究中心

关键词:

分布式事务故障的处理

ORACLE分布式事务