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


Error:

ADKPOST001: BOJ at Mon Jul 20 15:00:15 2013 with PID=07314 ORACLE_SID=AZKKDB
LOGON COMMIT - U065025 U065025
LOGON_LG U065025
LOGON_LG U065025 C
ADKPOST BEBEK HATA ALDI -- txsrv.serno = -1591 ORA-01591: lock held by in-doubt distributed transaction 606.36.301120
ORA-01591: lock held by in-doubt distributed transaction 606.36.301120
ORA-06512: at "AZKKDB.REPCC_ADKPOST", line 5922
ORA-06512: at line 1

******************************************************************************

Select * from dba_2pc_pending where local_tran_id='606.36.301120';

Here, according to the situation before the commit is issued after the rollback.

commit  force  '606.36.301120';

rollback force  '606.36.301120';

If you can not solve the problem, go purge.

alter system enable distributed recovery;

alter session set "_smu_debug_mode" = 4;

commit;

exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('606.36.301120');

commit;

After this stage transaction dba_2pc_pending from are eliminated.

---------------------------------------------------------------------------------------------------------------------

After this step, the problem should be solved.

Or not go to next step:

Connect to sys user

Set serveroutput on
Exec SYS.clear_ora_1591('606.36.301120');

------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SYS.clear_ora_1591 (p_transaction_id IN VARCHAR2)
/* ORA-1591 Metalink note 401302.1 
 
    2.2 Distributed transaction without corresponding dba_2pc entries
    In this case dba_2pc views are empty but users are receiving distributed txn related errors, e.g. ORA-2054, ORA-1591.
 
    */
AS
   v_dummy      NUMBER (1);
   v_status     VARCHAR2 (30);
   v_username   VARCHAR2 (30);
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
     INTO v_username
     FROM DUAL;
 
   IF v_username <> 'SYS'
   THEN
      DBMS_OUTPUT.put_line ('run to SYS user’);
      RETURN;
   END IF;
 
   SELECT 1
     INTO v_dummy
     FROM dba_2pc_pending
    WHERE local_tran_id = p_transaction_id;
 
   DBMS_OUTPUT.put_line
                ('if dba_2pc yes entries then other solutions should be considered in');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (' if dba_2pc no entries);
 
      SELECT ktuxesta
        INTO v_status
        FROM x$ktuxe
       WHERE ktuxesta != 'INACTIVE'
         AND ktuxeusn =
                SUBSTR (p_transaction_id, 1, INSTR (p_transaction_id, '.') - 1);
 
      IF v_status = 'PREPARED'
      THEN
         DBMS_OUTPUT.put_line;
         DBMS_OUTPUT.put_line;
 
         EXECUTE IMMEDIATE '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 (p_transaction_id, 306206, 'XXXXXXX.12345.1.2.3',
                      'prepared', 'P', HEXTORAW ('00000001'),
                      HEXTORAW ('00000000'), 0, SYSDATE, SYSDATE
                     );
 
         INSERT INTO pending_sessions$
              VALUES (p_transaction_id, 1, HEXTORAW ('05004F003A1500000104'),
                      'C', 0, 30258592, '', 146);
 
         COMMIT;
         DBMS_OUTPUT.put_line;
 
         BEGIN
            DBMS_OUTPUT.put_line ('commit force');
 
            EXECUTE IMMEDIATE 'commit force ''' || p_transaction_id || '''';
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                   ('if commit force error took then delete entries ');
 
               DELETE FROM pending_trans$
                     WHERE local_tran_id = p_transaction_id;
 
               DELETE FROM pending_sessions$
                     WHERE local_tran_id = p_transaction_id;
 
               COMMIT;
 
               EXECUTE IMMEDIATE 'alter system enable distributed recovery';
 
               DBMS_OUTPUT.put_line
                  ('COMMIT FORCE FAIL , INSERT The recordings have been deleted'
                  );
               RETURN;
         END;
 
         DBMS_OUTPUT.put_line ('commit force successful');
 
         EXECUTE IMMEDIATE 'alter system enable distributed recovery';
 
         EXECUTE IMMEDIATE 'alter session set "_smu_debug_mode" = 4';
 
         COMMIT;
         DBMS_TRANSACTION.purge_lost_db_entry (p_transaction_id);
         DBMS_OUTPUT.put_line
            ('COMMIT FORCE successful purge transaction was, control should be again');
      END IF;
END;
/

*****************************************************************************

Other Step’s:

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

Oracle Support id Note: 401302.1

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( '2508.16.36962', /* <== 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( '2508.16.36962',/* <==Replace only this with your local tran id */
        1, hextoraw('05004F003A1500000104'),
        'C', 0, 30258592, '',
        146
      );

    commit;

    commit force '2508.16.36962';

SQL> alter system enable distributed recovery;

System altered.

SQL> alter session set "_smu_debug_mode" = 4;

Session altered.

SQL> commit;

Commit complete.

SQL> exec dbms_transaction.purge_lost_db_entry('2508.16.36962');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: