Distributed Transactions Trouble Shooting
Distruibuted Transactions Trouble Shooting

Reference:(Chinese)
http://hi.baidu.com/wa0362/blog/item/93e400efddd841ebcf1b3e7d.html

simulate in-doubt status of distruibuted 2pc transactions

we can just simulate prepare phase in-doubt, after commit the distributed processing is too fast to simulate.

environment orcl01(ORCL01.TEST.COM),orcl02(ORCL02.TEST.COM) version 10.2.0.2

--- orcl01 dblink:
create public database link pu_link connect to hr identified by hr using 'orcl02';
 
-- orcl01 execute distributed sql:
update hr.employees set salary=salary+1000 where employee_id=200;
update employees@pu_link set salary=salary+1000 where employee_id=200;
-- orcl02 simulate nic failure
ifconfig eth0 down

orcl01 is hanged before commit, and auto-recover when network is recovered.
but the administrator runs shutdown

-- orcl01
shutdown abort

the following is orcl01 alert log and trace file after startup again:

-- altert log
 
Thu Mar 27 17:48:24 2008
SMON: enabling cache recovery
Thu Mar 27 17:48:25 2008
DISTRIB TRAN ORCL01.TEST.COM.8705ca3e.5.32.251
is local tran 5.32.251 (hex=05.20.fb)
insert pending prepared tran, scn=498537 (hex=0.00079b69)
Successfully onlined Undo Tablespace 1.
Thu Mar 27 17:48:26 2008
SMON: enabling tx recovery
Thu Mar 27 17:48:26 2008
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=16, OS id=7938
Thu Mar 27 17:48:34 2008
Completed: ALTER DATABASE OPEN
Thu Mar 27 17:49:22 2008
Errors in file /u01/app/oracle/oracle/product/10.2.0/db_1/admin/orcl01/bdump/orcl01_reco_7032.trc:
ORA-12560: TNS:protocol adapter error
Thu Mar 27 17:49:54 2008
Errors in file /u01/app/oracle/oracle/product/10.2.0/db_1/admin/orcl01/bdump/orcl01_reco_7032.trc:
ORA-12560: TNS:protocol adapter error
 
--- trace file saids cannot connect database, trans 5.32.251
[oracle@dg1 bdump]$ more /u01/app/oracle/oracle/product/10.2.0/db_1/admin/orcl01/bdump/orcl01_reco_7032.trc
。。。
 
*** SERVICE NAME:(SYS$BACKGROUND) 2008-03-27 17:49:22.716
*** SESSION ID:(108.1) 2008-03-27 17:49:22.716
*** 2008-03-27 17:49:22.716
ERROR, tran=5.32.251, session#=1, ose=0:
ORA-12560: TNS:protocol adapter error
*** 2008-03-27 17:49:54.732
ERROR, tran=5.32.251, session#=1, ose=0:
ORA-12560: TNS:protocol adapter error
*** 2008-03-27 17:50:43.748
ERROR, tran=5.32.251, session#=1, ose=0:
ORA-12560: TNS:protocol adapter error
 
-- error is raised when query hr.employees:
SQL> select employee_id,salary from hr.employees;
select employee_id,salary from hr.employees
                                  *
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 5.32.251

Detail of error message:

ORA-01591: lock held by in-doubt distributed transaction string 
Cause: Trying to access resource that is locked by a dead two-phase commit 
transaction that is in prepared state.
Action: DBA should query the pending_trans$ and related tables, and attempt 
to repair network connection(s) to coordinator and commit point. If timely 
repair is not possible, DBA should contact DBA at commit point if known or 
end user for correct outcome, or use heuristic default if given to issue a 
heuristic commit or abort command to finalize the local portion of the 
distributed transaction.

status of dba_2pc_pending dict
state of 5.32.251 is prepared.
we can find the database is pu_link.test.com with dba_2pc_neighbors dict.

SQL> col local_tran_id format a13
SQL> col global_tran_id format a30
SQL> col state format a8
SQL> col mixed format a3
SQL> col host format a10
SQL> col commit# format a10
SQL> select local_tran_id, global_tran_id, state, mixed, host, commit#
2 from dba_2pc_pending;
 
LOCAL_TRAN_ID GLOBAL_TRAN_ID                            STATE       MIX   HOST       COMMIT#
--------------------   ------------------------------------------     --------      ---    ----------   ----------
5.32.251               ORCL01.TEST.COM.8705ca3e.5.32. prepared   no    dg1          498537
                             251
 
SQL> col local_tran_id format a13
SQL> col in_out format a6
SQL> col database format a25
SQL> col dbuser_owner format a15
SQL> col interface format a3
SQL> select local_tran_id, in_out, database, dbuser_owner, interface 
2 from dba_2pc_neighbors;
 
LOCAL_TRAN_ID IN_OUT DATABASE                  DBUSER_OWNER    INT
--------------------    --------- -------------------------     ------------------          ---
5.32.251             in                                                SYS                           N
5.32.251               out         PU_LINK.TEST.COM     SYS                           C

we have 2 choices
1. commit/rollback manually
2. rollback after fix environment

under product environment, we many have no time to execute choice 2.

rollback manually
based on prepared status, until orcl01 under prepared status, orcl02 cannot be commited.
to keep the consistence we had better

rollback force '5.32.251'

commit force

SQL> COMMIT FORCE '5.32.251';
 
Commit complete.
 
orcl01
SQL> select employee_id,salary from hr.employees;
 
EMPLOYEE_ID     SALARY
------------------    ----------
        198             2600
        199            2600
        200            4800
orcl02
SQL> select employee_id,salary from hr.employees;
 
EMPLOYEE_ID     SALARY
---------------------    ----------
        198               2600
        199               2600
        200               4700
 
SQL> select local_tran_id, global_tran_id, state, mixed, host, commit#
2 from dba_2pc_pending;
 
LOCAL_TRAN_ID GLOBAL_TRAN_ID                            STATE         MIX HOST       COMMIT#
-------------             -------------------------------------------       -----------     ---   ----------     ----------
5.32.251               ORCL01.TEST.COM.8705ca3e.5.32. forced c     no    dg1
                             251                                                       ommit
 
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.32.251');