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-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
Post a Comment