分类目录归档：Oracle

利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据

发表于 2011 年 12 月 6 日 由 惜分飞

部署ogg，需要两边初始化数据，但是因为业务不能停止。所以考虑到使用ogg init功能或者impdp。考虑到数据量比较大，自己对ogg init不是很有信心，所以选择了使用impdp结合network_link+FLASHBACK_TIME处理
一、处理思路
1、选择时间点(尚未达到时间点)
选择一个时间点，用于重设ogg同步时间点，impdp导入时间点
要求:ogg尚未加载到该时间点(在该时间点之前停止ogg相关ext/pump/replicat进程)

2、开始导入指定时间点数据(该时间点已经达到后)

[oracle@srtcreen OGG]$ impdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 13:03:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\"

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 13:04:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SRT_CREEN"."SYS_IMPORT_TABLE_01":  srt_creen/******** directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME="to_timestamp('2011-12-06 13:03:00','yyyy-mm-dd hh24:mi:ss')" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 824.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
    . . imported "SRT_CREEN"."TAB_CS_CALLLOG"               3289293 rows
    . . imported "SRT_CREEN"."TAB_CS_CALL_COMING"           1218843 rows
 . . imported "SRT_CREEN"."TAB_CS_CALL_PICKUP"           1092937 rows
. . imported "SRT_CREEN"."TAB_CS_QUEUES"                   4614 rows
. . imported "SRT_CREEN"."TAB_CS_USER"                      458 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SRT_CREEN"."SYS_IMPORT_TABLE_01" successfully completed at 13:12:37

3、重设同步时间点，开启进程

alter ext_1,begin 2011-12-06 13:03:00
alter ext_2,begin 2011-12-06 13:03:00
alter ext_3,begin 2011-12-06 13:03:00
alter ext_4,begin 2011-12-06 13:03:00
alter ext_5,begin 2011-12-06 13:03:00

alter p_1,begin 2011-12-06 13:03:00
alter p_2,begin 2011-12-06 13:03:00
alter p_3,begin 2011-12-06 13:03:00
alter p_4,begin 2011-12-06 13:03:00
alter p_5,begin 2011-12-06 13:03:00
start *

alter r_1,begin 2011-12-06 13:03:00
alter r_2,begin 2011-12-06 13:03:00
alter r_3,begin 2011-12-06 13:03:00
alter r_4,begin 2011-12-06 13:03:00
alter r_5,begin 2011-12-06 13:03:00
start *

4、验证同步情况

--源端
GGSCI (tykf) 23> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_2       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_3       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_4       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_5       00:00:00      00:00:07    
EXTRACT     RUNNING     P_1         00:00:00      00:00:06    
EXTRACT     RUNNING     P_2         00:00:00      00:00:06    
EXTRACT     RUNNING     P_3         00:00:00      00:00:06    
EXTRACT     RUNNING     P_4         00:00:00      00:00:06    
EXTRACT     RUNNING     P_5         00:00:00      00:00:06    

GGSCI (tykf) 24> stats p_3

Sending STATS request to EXTRACT P_3 ...

Start of Statistics at 2011-12-06 13:15:46.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00

Output to /opt/OGG/dirdat/U3:

Extracting from CSCNEW.TAB_CS_CALL_PICKUP to CSCNEW.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.



--目标端
GGSCI (srtcreen) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:06    
REPLICAT    RUNNING     R_2         00:00:00      00:00:06    
REPLICAT    RUNNING     R_3         00:00:00      00:00:08    
REPLICAT    RUNNING     R_4         00:00:00      00:00:04    
REPLICAT    RUNNING     R_5         00:00:00      00:00:02    


GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 13:15:35.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.

二、配置过程遇到问题
1、impdp报无权限

[oracle@srtcreen OGG]$ expdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 11:00:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" dumpfile=a.dmp

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 11:15:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

srt_creen用户需要有imp_full_database权限
dblink中用户需要exp_full_database权限

2、目标端进程报OGG-01003

Opened trail file /opt/OGG/dirdat/U3000002 at 2011-12-06 12:34:47

Switching to next trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21 due to EOF, with current RBA 1028
Opened trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21

Processed extract process graceful restart record at seq 3, rba 993.
Processed extract process graceful restart record at seq 3, rba 1051.

MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP):
  MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
Using following columns in default map by name:
  PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, 
  FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME

Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID.


2011-12-06 12:35:22  WARNING OGG-00869  OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT
E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL
L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-06 12:35:23  WARNING OGG-01004  Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat
e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = 
:a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>).

2011-12-06 12:35:23  WARNING OGG-01003  Repositioning to rba 1111 in seqno 3.

部署ogg时配置relicat错误:
非MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
而是MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (PICKUP_ID);

3、update同步失败

GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 12:37:49.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Daily statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Hourly statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Latest statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

End of Statistics.

源端忘记执行add trandata 同步表

add trandata CSCNEW.TAB_CS_USER
add trandata  CSCNEW.TAB_CS_QUEUES
add trandata  CSCNEW.TAB_CS_CALL_PICKUP
add trandata CSCNEW.TAB_CS_CALL_COMING
add trandata CSCNEW.TAB_CS_CALLLOG
发表在 GoldenGate, 逻辑备份/恢复 | 评论关闭

V$PWFILE_USERS和密码文件关系(续)

发表于 2011 年 12 月 6 日 由 惜分飞

1、验证密码文件已经修改

SQL> create user xff01 identified by xifenfei;

User created.

SQL> grant sysdba to xff01;

Grant succeeded.

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
04cedb56b62d94fd7e14124619722348  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> revoke sysdba from xff01;

Revoke succeeded.

SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g
]\[Z
ORACLE Remote Password file
INTERNAL
A1174901D667F113
18698BFD1A045BCC
XFF01
D32693095588EF4F

SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g
1f6d120acb913a1877cfb0ab57702744  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

2、查看基表

SQL> col owner for a20
SQL> col object_name for a30
SQL> SELECT owner,object_name,object_type FROM DBA_objects WHERE object_NAME ='V$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
PUBLIC               V$PWFILE_USERS                 SYNONYM

SQL> SELECT table_owner,TABLE_name FROM Dba_Synonyms a WHERE a.synonym_name='V$PWFILE_USERS';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$PWFILE_USERS

SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name='V_$PWFILE_USERS';

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
SYS                  V_$PWFILE_USERS                VIEW

SQL> set long 1000
SQL> set line 200
SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$PWFILE_USERS','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$PWFILE_USERS','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PWFILE_USERS" ("USERNAME", "SYSDBA", "SYSOPER", "SYSASM") AS
  select "USERNAME","SYSDBA","SYSOPER","SYSASM" from v$pwfile_users

SQL> select * from v$fixed_table where name ='V$PWFILE_USERS';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$PWFILE_USERS                 4294951116 VIEW       65537


SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id = USERENV('Instance')

SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$PWFILE_USERS';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE','FALSE'),
 decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and username != 'INTERNAL'

SQL> set line 100
SQL> desc x$kzsrt
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 USERNAME                                                       VARCHAR2(30)
 SYSDBA                                                         NUMBER
 SYSOPER                                                        NUMBER
 SYSASM                                                         NUMBER
 VALID                                                          NUMBER

SQL> col username for a10
SQL> select * from x$kzsrt;

ADDR                   INDX    INST_ID USERNAME       SYSDBA    SYSOPER     SYSASM      VALID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00002B9563678690          0          1 INTERNAL            1          1          0          1
00002B9563678690          1          1 SYS                 1          1          0          1
00002B9563678690          2          1 XFF01               0          0          0          0

从这里可以看出V$PWFILE_USERS视图的基表是x$kzsrt，这里和我们查看的密码文件一样，这些记录都存在，而是在密码文件中有不被strings显示的字符表示了账号是否启用，x$kzsrt.valid用1和0标示
上篇:V$PWFILE_USERS和密码文件关系

发表在 Oracle | 评论关闭

Oracle分布式事务故障处理

发表于 2011 年 12 月 5 日 由 惜分飞

分布式事务，简单来说，是指一个事务在本地和远程执行，本地需要等待确认远程的事务结束后，进行下一步本地的操作。如通过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

发表在 Oracle | 一条评论