Tuesday 2 December 2014

ORA-01591: lock held by in-doubt distributed transaction A.B.C


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: