Transactions troubleshooting

Please refer to note 126069.1 for complete details

db 1 ---go-to----> db 2

To avoid pivileges issues, I always suppose a superuser

On db 1:
#I can't type 99999 chars at second, so I need help...
alter system disable distributed recovery;
#perform any dml over db 2
delete from tmp@db2 where rownum = 1;
#perform any dml over db 1
insert into tmp values(1);
#!! THIS SIMULATE A DB 2 CRASH !!
commit comment 'ORA-2PC-CRASH-TEST-1';
#please break the dblink over tnsnames.ora and close this session
#reopen another session
#It may happen some SYS objects are on catalog but not existant! Recompile what you need.
alter system enable distributed recovery;
#Now lets see the problem:
select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
#if you issue a shutdown immediate on db 1 WILL HANG for a lot,
#may be you abort the instance. Anyway start it up again

#At this point you can restart the instance many times but the lost transaction
#will never disappear because db 2 is unreacheable

Now we solve the problem.
On db 1 as SYS:
select local_tran_id, state from dba_2pc_pending;
#Bring the transaction to rollback state if in prepared state
rollback force '1.27.303078';
#not available in 10g
#execute this, otherwise you get 
#ORA-30019 Illegal rollback Segment operation in Automatic Undo mode
alter session set "_smu_debug_mode" = 4;
#purge the lost transaction
begin
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.27.303078');
end;
/
alter system enable distributed recovery;