Wednesday, December 16, 2015

auto clean up in-doubt distributed transaction

[How the problem comes]

Mirror database and DB link technology are used intensively in my environment.
Mirror database is created by using storage level technology daily at mid-night specific time.

In the source database, transactions may not be still running (yet commit yet), however the hard storage split has to happen, which also transparent to operator.

As such, although the mirror database is open to application team, but below error can be encountered when accessing the interested table.

ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction



[Solution]

To solve the issue, we come out a script as shown below, to perform the clean up after database recovery. 




 cat check_dba_2pc_pending_trans.sql
--set echo on
set serveroutput on
set lines 150
col HOST format a20
col OS_USER format a20
col spoolname new_value spoolname
select '/oracle/node1/ops/logs/check_dba_2pc_pending_'||to_char(sysdate, 'yymmddhh24mmss')||(select '_'||instance_name from v$instance) spoolname from dual;
spool '&spoolname'
select LOCAL_TRAN_ID,STATE,FAIL_TIME,OS_USER,HOST,DB_USER,COMMIT#,ADVICE from dba_2pc_pending;
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
declare
   cursor_name     pls_integer default dbms_sql.open_cursor;
   ignore          pls_integer;
--   x  varchar2(22);

   procedure execute_immediate( p_sql in varchar2 )
   is
   BEGIN
      dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);
      ignore := dbms_sql.execute(cursor_name);
      --dbms_sql.close_cursor(cursor_name);
   END;

begin
   for x in ( select local_tran_id,state from dba_2pc_pending ) loop
      dbms_output.put_line( 'Local Trans Id:  ' || x.local_tran_id );
      if x.state = 'committed' then
        dbms_transaction.purge_lost_db_entry(x.local_tran_id);
        commit;
      else
        execute_immediate( 'rollback force ''' || x.local_tran_id || '''' );
        commit;
        dbms_transaction.purge_lost_db_entry(x.local_tran_id);
        commit;
      end if;
   end loop;
   dbms_sql.close_cursor(cursor_name);
end;
/
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
select LOCAL_TRAN_ID,STATE,FAIL_TIME,OS_USER,HOST,DB_USER,COMMIT#,ADVICE from dba_2pc_pending;
spool off