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
