Below is an example for transaction id 1.92.66874, you need to replace correct transaction id.
Trying to manually commit or rollback this transaction
commit force ‘1.92.66874’;
ORA-02058: no prepared transaction found with ID 1.92.66874
raises ORA-02058 since dba_2pc views are empty. In order to use commit force or
rollback force a dummy record should be inserted into pending_trans$ as follows:
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( ‘1.92.66874’, /* <== Replace this with your local tran 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( ‘1.92.66874’,/* <==Replace only this with your local tran id */
1, hextoraw(‘05004F003A1500000104’),
‘C’, 0, 30258592, ”,
146
);
commit;
commit force ‘1.92.66874’;
If commit force raises an error then note the error message and execute the
following:
delete from pending_trans$ where local_tran_id=’1.92.66874′;
delete from pending_sessions$ where local_tran_id=’1.92.66874′;
commit;
alter system enable distributed recovery;
Otherwise run purge the dummy entry from the dictionary, using
alter system enable distributed recovery;
connect / as sysdba
COMMIT;
Use following query to retrieve the value for such _smu_debug_mod parameter:
col Parameter for a20
col “Session Value” for a20
col “Instance Value” for a20
SELECT a.ksppinm “Parameter”,b.ksppstvl “Session Value”,c.ksppstvl “Instance Value”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = ‘_smu_debug_mode’
/
— set it temporarily to 4:
alter system set “_smu_debug_mode” = 4; /* if automatic undo management
is being used */
— in 9.2x alter session can be used instead.
commit; /* this is to prevent the ORA-01453 in purge_lost_db_entry call */
exec dbms_transaction.purge_lost_db_entry( ‘1.92.66874’ );
SQL> commit;
SQL> alter system set “_smu_debug_mode” = <original value>;
SQL> commit;
Note: do this action plan for all transaction which are in status PREPARED for query
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM sys.x$ktuxe
WHERE ktuxesta!=’INACTIVE’;
