EXP-00056: ORACLE error 1591 encountered ORA-01591: lock held by in-doubt distributed transaction 8.20.1052628

LindaC
LindaC used Ask the Experts™
on
Hi experts.
The exports of a 8.1.6 database is failing on the dc_reservations table with the error ORA-01591.

This database is replicating from production every day.
I need to resolve the following issue, so that the export does not fail with this:

This error has been there "forever".

. . exporting table                DC_RESERVATIONS
EXP-00056: ORACLE error 1591 encountered
ORA-01591: lock held by in-doubt distributed transaction 8.20.1052628



The following query does not return the transaction 8.20.1052618:
cONNECT / AS SYSDBA
COL GLOBAL_TRAN_ID FOR a50
COL DATABASE FOR A30
SET LINESIZE 200
SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;


LOCAL_TRAN_ID          GLOBAL_TRAN_ID                                     STATE
           MIX COMMIT#
---------------------- -------------------------------------------------- ------
---------- --- ----------------
19.187.1218667         TALBDB.WORLD.8c258f1.11.0.5104642                  commit
ted        no  7813265661895
3.144.1097288          BAYBDB.WORLD.a317405b.9.13.1101181                 forced
 rollback  no  7810226855806
4.66.1097277           BAYBDB.WORLD.a317405b.6.2.982686                   forced
 rollback  no  7810226872966
4.13.1112808           TALBDB.WORLD.8c258f1.12.9.4849104                  commit
ted        no  7811913504200
5.41.1152117           TALADB.WORLD.255ba3b.24.6.2180228                  commit
ted        no  7811913504697
6.189.1161138          BAYBDB.WORLD.a317405b.27.13.70778                  commit
ted        no  7811913504252
6.196.1161135          BAYBDB.WORLD.a317405b.28.18.66778                  commit
ted        no  7811913504660
7.14.1152631           TALADB.WORLD.255ba3b.46.8.7680                     commit
ted        no  7811913504681
9.77.1159370           TALBDB.WORLD.8c258f1.5.9.6699335                   commit
ted        no  7811913504199
11.34.1158910          BAYBDB.WORLD.a317405b.17.16.322501                 commit
ted        no  7811913504648
12.30.1158786          TALADB.WORLD.255ba3b.42.17.20139                   commit
ted        no  7811913504693

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                                     STATE
           MIX COMMIT#
---------------------- -------------------------------------------------- ------
---------- --- ----------------
14.82.1152868          BAYBDB.WORLD.a317405b.20.1.198916                  commit
ted        no  7811913504174
15.31.1092588          TALBDB.WORLD.8c258f1.26.21.1253347                 commit
ted        no  7811913504695
17.72.1147821          TALBDB.WORLD.8c258f1.29.6.724775                   commit
ted        no  7811913504709
19.64.1161431          BAYBDB.WORLD.a317405b.10.13.1203951                commit
ted        no  7811913504701
21.125.1157939         TALADB.WORLD.255ba3b.43.5.14230                    commit
ted        no  7811913504710
21.128.1157944         TALBDB.WORLD.8c258f1.9.20.6432633                  commit
ted        no  7811913504694
5.105.1152965          TALBDB.WORLD.8c258f1.51.5.2988                     commit
ted        no  7811931078569
6.114.1161728          TALBDB.WORLD.8c258f1.16.11.3973747                 commit
ted        no  7811931078568
11.192.1159946         TALBDB.WORLD.8c258f1.35.19.158196                  commit
ted        no  7811931078576
16.92.1160263          TALBDB.WORLD.8c258f1.25.6.1755781                  commit
ted        no  7811931078567
4.86.1165186           TALBDB.WORLD.8c258f1.47.18.272751                  commit
ted        no  7812878481374

22 rows selected.

SQL>
SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;


LOCAL_TRAN_ID          IN_ DATABASE                       I
---------------------- --- ------------------------------ -
3.144.1097288          in  BAYBDB.WORLD                   N
4.66.1097277           in  BAYBDB.WORLD                   N
4.13.1112808           in  TALBDB.WORLD                   C
4.13.1112808           in  TALBDB.WORLD                   N
5.41.1152117           in  TALADB.WORLD                   N
5.41.1152117           in  TALADB.WORLD                   C
6.189.1161138          in  BAYBDB.WORLD                   N
6.189.1161138          in  BAYBDB.WORLD                   C
6.196.1161135          in  BAYBDB.WORLD                   C
7.14.1152631           in  TALADB.WORLD                   N
7.14.1152631           in  TALADB.WORLD                   C

LOCAL_TRAN_ID          IN_ DATABASE                       I
---------------------- --- ------------------------------ -
9.77.1159370           in  TALBDB.WORLD                   N
9.77.1159370           in  TALBDB.WORLD                   C
11.34.1158910          in  BAYBDB.WORLD                   C
12.30.1158786          in  TALADB.WORLD                   N
12.30.1158786          in  TALADB.WORLD                   C
14.82.1152868          in  BAYBDB.WORLD                   C
15.31.1092588          in  TALBDB.WORLD                   N
15.31.1092588          in  TALBDB.WORLD                   C
17.72.1147821          in  TALBDB.WORLD                   N
17.72.1147821          in  TALBDB.WORLD                   C
19.64.1161431          in  BAYBDB.WORLD                   C

LOCAL_TRAN_ID          IN_ DATABASE                       I
---------------------- --- ------------------------------ -
21.125.1157939         in  TALADB.WORLD                   N
21.125.1157939         in  TALADB.WORLD                   C
21.128.1157944         in  TALBDB.WORLD                   N
21.128.1157944         in  TALBDB.WORLD                   C
5.105.1152965          in  TALBDB.WORLD                   N
5.105.1152965          in  TALBDB.WORLD                   C
6.114.1161728          in  TALBDB.WORLD                   N
6.114.1161728          in  TALBDB.WORLD                   C
11.192.1159946         in  TALBDB.WORLD                   N
11.192.1159946         in  TALBDB.WORLD                   C
16.92.1160263          in  TALBDB.WORLD                   N

LOCAL_TRAN_ID          IN_ DATABASE                       I
---------------------- --- ------------------------------ -
16.92.1160263          in  TALBDB.WORLD                   C
4.86.1165186           in  TALBDB.WORLD                   C
4.86.1165186           in  TALBDB.WORLD                   N
19.187.1218667         in  TALBDB.WORLD                   C

37 rows selected.



Tue May 22 18:10:55 2012
Errors in file /usr/opt/app/oracle/admin/bvdc8/udump/bvdc8_ora_16346.trc:
ORA-01591: lock held by in-doubt distributed transaction 8.20.1052628
Shutting down instance (immediate)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This seems to be a little  bit wierd.. normally distributed transactions should be available in either neighbours/pending(dba_2pc_**)

have you did a select as soon as the error occured. might be by the time you performed the query, the transaction might have got committed or rolled back(probably, wild guess not sure)..

as per the metalink note id 100664.1

when ever such error occurs, you should find that dist. tran. id

select * from dba_2pc_pending where local_tran_id='8.20.1052628' in this..

incase the error still persists, then follow  steps mentioned in the metalink note and see if that helps...!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial