ORA-01591: lock held by in-doubt distributed transaction X.X.Y ORA-01591:
lock held by in-doubt distributed transaction 13.13.2314594
-- If following error occur and DBA_2PC_PENDING and DBA_2PC_NEIGHBORS do not have info abt that transaction. Please proceed in following manner.
--- please execute the folowing queries
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
AND ktuxeusn= 13; ## this is the first digit of the transaction.
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ------------------------------------------------ ----------------------------
13 13 2314594 PREPARED SCO|COL|REV|DEAD
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE' ;
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ------------------------------------------------ ----------------------------
13 13 2314594 PREPARED SCO|COL|REV|DEAD
select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';
no rows selected
SQL> select * from sys.pending_trans$;
no rows selected
SQL> select * from sys.pending_sessions$ ;
no rows selected
SQL> select * from sys.pending_sub_sessions$;
no rows selected
#Please execute the following to fix the problem : (Testing)
====================================================================
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( '13.13.2314594', ## mention the distributed transaction no u r getting with error
306206,
'XXXXXXX.12345.1.2.3', ## left these no. as it is
'prepared','P',
hextoraw( '00000001' ),
hextoraw( '00000000' ),
0, sysdate, sysdate );
insert into pending_sessions$
values( '13.13.2314594', ## mention the distributed transaction no u r getting with error
1, hextoraw('05004F003A1500000104'), ## left these no. as it is
'C', 0, 30258592, '',
146
);
commit;
commit force '13.13.2314594';
========================================================================
--------If commit force raises an error then note the error message and execute the following:
delete from pending_trans$ where local_tran_id='13.13.2314594';
delete from pending_sessions$ where local_tran_id='13.13.2314594';
commit;
alter system enable distributed recovery;
----------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 if it is different:
alter system set "_smu_debug_mode" = 4;
-- 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( '13.13.2314594' );
SQL> commit;
SQL> alter system set "_smu_debug_mode" = <original value>;
SQL> commit;
2nd .Step
Step:-
SQL> execute dbms_transaction.purge_lost_db_entry('4.6.12870');
BEGIN dbms_transaction.purge_lost_db_entry('4.6.12870'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
However, this is not always the case, as the transaction is seen as pending in the “pending two phase commit” view (DBA_2PC_PENDING)
SQL> select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE FAIL_TIME RETRY_TIME
------------- ------------------------------- --------- --------- ----------
70.31.1376339 REMDB.WORLD.f9784a67.3.9.924681 collecting 09-JUN-11 09-JUN-11
If the state of the transaction is “prepared”, it is possible to force
rollback the transaction by appending the transaction id to the command as follows (as sysdba):
SQL> ROLLBACK FORCE '70.31.1376339';
If the state of the transaction is “collecting”, you will suffer the following error:
SQL> ROLLBACK FORCE '70.31.1376339';
ROLLBACK FORCE '70.31.1376339'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 70.31.1376339
In this case, you need to execute the following procedure in the DBMS_TRANSACTION package to clear.
SQL> execute dbms_transaction.purge_lost_db_entry('70.31.1376339')
PL/SQL procedure successfully completed.
Rerun the query against DBA_2PC_PENDING to confirm the pending local transaction has gone.
SQL> select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;
no rows selected
Also check DBA_2PC_NEIGHBORS to confirm the pending remote transaction has gone.
SQL> select database,local_tran_id,dbid,sess#,branch from dba_2pc_neighbors;
no rows selected
3rd Step
---------------Follow this steps-----------------------------
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 ;
=======================================
Alert error:
Thu Jun 13 10:03:07 2013
Errors in file /home/oracle/oracle/admin/orcl/bdump/sarathi_reco_4239.trc:
ORA-12170: TNS:Connect timeout occurred
Thu Jun 13 10:05:07 2013
DISTRIB TRAN ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM.85433c2c.4.6.12870
is local tran 4.6.12870 (hex=04.06.3246)
change pending prepared tran, scn=1022644320881 (hex=ee.1a59fa71)
to pending forced rollback tran, scn=1022644320881 (hex=ee.1a59fa71)
Thu Jun 13 10:08:16 2013
Errors in file /home/oracle/oracle/admin/sarathi/bdump/sarathi_reco_4239.trc:
ORA-12170: TNS:Connect timeout occurred
No comments:
Post a Comment