troubleshooting Question

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

Avatar of LindaC
LindaCFlag for Puerto Rico asked on
Unix OSOracle Database
1 Comment1 Solution1342 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
Wasim Akram Shaik

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros