[72000] ORA-01591: lock held by in-doubt distributed transaction 1.65.465

Check the dba_2pc_pending view
If there are in-doubt distributed transaction, you'l see them there.
Oracle's doc has as whole section on "Managing Distributed Transactions"
There is a procedured documented there "dbms_transactioin.purge_lost_db_entry"
That will clean these transactions up.

select * from dba_2pc_pending view will see

local_trans_id

1.65.465

 

SYS.PENDING_TRANS$, SYS.PENDING_SESSIONS$

 

login as sysdba and execute the proc

export ORACLE_SID=main
sqlplus / as sysdba

execute dbms_transactioin.purge_lost_db_entry;

 

Sometimes, it is not possible because you do not have rows in DBA_2PC_PENDING view. If this is the case, you can insert dummy rows into SYS.PENDING_TRANS$, and SYS.PENDING_SESSIONS$ tables to correct this. Below is a script that can help you if you do not have rows in DBA_2PC_PENDING view. Just enter the TRANSACTION ID, got from ORA-1591 error or from alertlog files.

---

--
-- Execute it connected as SYS or SYSDBA
--
ACCEPT TRANSACTION_ID PROMPT "Enter TRANSACTION ID: "
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( '&&TRANSACTION_ID',
306206, /* */
'XXXXXXX.12345.1.2.3', /* These values can be used without any */
'prepared','P', /* modification. Most of the values are */
hextoraw( '00000001' ), /* constant. */
hextoraw( '00000000' ), /* */
0, sysdate, sysdate );
insert into pending_sessions$
values( '&&TRANSACTION_ID',
1, hextoraw('05004F003A1500000104'),
'C', 0, 30258592, '',
146
);
commit;
rollback force '&&TRANSACTION_ID'; /* or commit force */
commit;
alter system enable distributed recovery;
exec dbms_transaction.purge_lost_db_entry( '&&TRANSACTION_ID' );
commit;
delete from pending_trans$ where local_tran_id='&&TRANSACTION_ID';
delete from pending_sessions$ where local_tran_id='&&TRANSACTION_ID';
commit;