Skip to main content

ORA-01591: lock held by in-doubt distributed transaction

Issue :- 

DISTRIB TRAN US38.WORLD.81804cc.4.10.955447
is local tran 63.14.2518245 (hex=3f.0e.266ce5)
insert pending prepared tran, scn=17776081022679 (hex=102a.d0feaed7)
 

ORA-01591: lock held by in-doubt distributed transaction 83.18.2018137

ORA-02063: preceding line from MNXAPPC

 ==============================================

 

 Check -

 select LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE from DBA_2PC_PENDING;

select * from DBA_2PC_NEIGHBORS;
select * from sys.pending_trans$;
select * from SYS.PENDING_SESSIONS$;
select * from SYS.PENDING_SUB_SESSIONS$;
select count(*),state from DBA_2PC_PENDING group by state;

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE';

 

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
19 2 2844362 ACTIVE NONE
27 1 2928731 ACTIVE NONE
34 10 3056030 ACTIVE NONE
44 7 2895279 PREPARED SCO|COL|REV|DEAD

50 27 2825488 ACTIVE NONE
57 20 2438917 PREPARED SCO|COL|REV|DEAD
67 21 2961246 ACTIVE NONE
78 14 2290740 ACTIVE NONE
83 18 2018137 PREPARED SCO|COL|REV|DEAD
111 17 2282079 ACTIVE NONE
113 8 2303742 ACTIVE NONE

 

Workaround:- 

 

This is a metadata mismatch between the 2pc tables (pending_trans$,etc) and the undo area x$ktuxe, where the in-doubt tx is purged from 2pc tables but not from the undo as it is being held by some other process.This does not happen very often, but when it happens we must insert the rows back into the 2pc and then purge as per steps provided, there is no side effects or any further damage to the database.

 

for session no 44,57,83 ( below statement is prepared for session no 83 only ) 

SQL> ROLLBACK FORCE '83.18.2018137';
ROLLBACK FORCE '83.18.2018137'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 83.18.2018137


SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('83.18.2018137');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('83.18.2018137'); END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 105
ORA-06512: at line 1

 

sqlplus / as sysdba

spool support1.lst

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 83;

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( '83.18.2018137',
306206, /* */
'XXXXXXX.12345.1.2.3',
'prepared','P',
hextoraw( '00000001' ),
hextoraw( '00000000' ),
0, sysdate, sysdate );

insert into pending_sessions$
values( '83.18.2018137',
1, hextoraw('05004F003A1500000104'),
'C', 0, 30258592, '',
146
);

commit;

commit force '83.18.2018137';

commit;

alter system enable distributed recovery;

commit;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('83.18.2018137');

commit;

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 83;
 

 

  

Comments

Popular posts from this blog

Hard dependency with ip address Oracle RAC Cluster.

Command error out due to hard dependency with ip address [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource RDBMS_DB -n Node2 CRS-2527: Unable to start 'RDBMS_DB' because it has a 'hard' dependency on 'sDB' CRS-2525: All instances of the resource 'sDB' are already running; relocate is not allowed because the force option was not specified CRS-4000: Command Relocate failed, or completed with errors. [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource sDB  -n Node2 CRS-2529: Unable to act on 'DB' because that would require stopping or relocating 'LISTENER_DB', but the force option was not specified CRS-4000: Command Relocate failed, or completed with errors. [-Node1]/app/grid/oracle/product/11.2.0/grid/bin>./crsctl relocate resource LISTENER_DB  -n Node2 CRS-2527: Unable to start 'LISTENER_DB' because it has a 'hard' dependency on 'sD...

19C NID ( Rename Database)

 [oracle@localhost ~]$ nid DBNEWID: Release 19.0.0.0.0 - Production on Thu Dec 23 00:05:36 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET      Username/Password              (NONE) DBNAME      New database name              (NONE) LOGFILE     Output Log                     (NONE) REVERT      Revert failed change           NO SETNAME     Set a new database name only   NO APPEND      Append to output log           NO HELP        Displays these messages    ...

ORA-01017/ORA-28000 with AUDIT_TRAIL

With default profile in Oracle 11g, accounts are automatically locked 1 day ( PASSWORD_LOCK_TIME ) after 10 failed login attempt ( FAILED_LOGIN_ATTEMPTS ): SQL > SET lines 200 SQL > SET pages 200 SQL > SELECT * FROM dba_profiles WHERE PROFILE = 'DEFAULT' ORDER BY resource_name; PROFILE                         RESOURCE_NAME                      RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT                         COMPOSITE_LIMIT                 ...