Avatar of LindaC
LindaC
Flag for Puerto Rico asked on

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

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)
Unix OSOracle Database

Avatar of undefined
Last Comment
Wasim Akram Shaik

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23