Oracle Distributed transactions

1. Identify the id of the transaction:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

2. Purge the transaction:

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
SQL> COMMIT;

3. Confirm that the transaction has been purged:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;



Step 2:
=====


SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386');

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386'); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1


Fix:
===
This problem is logged as
Bug.2191458 (unpublished) UNABLE TO EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY WITH AUTO UNDO MANAGEMENT and is worked by development.

Use the following workaround:

You have to use local_tran_id.....

Issue commit before alter system set "_smu_debug_mode" = 4;

Follow the steps,

SQL>commit;

SQL> alter session set "_smu_debug_mode" = 4;

SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');


Step 3:
=====

When executing the following procedure(dbms_transaction.purge_lost_db_entry)
to delete entries from
dba_2pc_pending one encounters the following error:

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); ==>For example..
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94
ORA-06512: at line 1

Fix:
===

The transaction to be deleted is in the prepared state and has to be either
force committed or rolled back

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared

SQL> rollback force '37.16.108'; ==>For example

Rollback complete.

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback

SQL>Commit;

SQL>alter system set "_smu_debug_mode" = 4;
Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example


SQL> sho parameter distributed_l

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout integer 60

SQL> select value from v$parameter where name='distributed_lock_timeout';

VALUE
--------------------------------------------------------------------------------
60


select addr, kaddr, sid, type, id1 from v$lock where type = 'DX';

select
username
,osuser
,status
,sid
,serial#
,machine
,process
,terminal
,program
from
v$session
where saddr in ( select k2gtdses from sys.x$k2gte );


select
s.username
,s.osuser
,s.status
,s.sid
,s.serial#
,s.machine
,s.process
,s.terminal
,s.program
from
v$session s
where s.saddr in ( select x.k2gtdses from sys.x$k2gte x )
and not exists ( select
l.sid
from v$lock l
where l.type = 'DX'
and l.sid = s.sid
)
;


Сначала смотрим список транзакций и удостоверяемся, что их можно грохнуть:

SELECT P.LOCAL_TRAN_ID, P.STATE, P.HOST, N.DBUSER_OWNER
FROM DBA_2PC_PENDING P, DBA_2PC_NEIGHBORS N
WHERE P.LOCAL_TRAN_ID=N.LOCAL_TRAN_ID
ORDER BY DBUSER_OWNER
/

Потом, загоняем в спул результаты этого скрипта:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('’);
SELECT ‘COMMIT FORCE ‘'’ || LOCAL_TRAN_ID || ‘'’;’
FROM DBA_2PC_PENDING
/
Запускаем контент спула. И после того как все транзакции помечены force committed запускаем это:

DECLARE
CURSOR C_TRANSACTIONS IS
SELECT LOCAL_TRAN_ID, STATE
FROM DBA_2PC_PENDING;
TRAN_ID VARCHAR2(22);
STATE VARCHAR2(16);
BEGIN
OPEN C_TRANSACTIONS;

LOOP
FETCH C_TRANSACTIONS INTO TRAN_ID, STATE;
EXIT WHEN C_TRANSACTIONS%NOTFOUND;
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(TRAN_ID);
COMMIT;
END LOOP;
CLOSE C_TRANSACTIONS;
END;
/




SELECT s.current_queue_duration, s.sql_address, t.name,
t.start_time, t.status AS t_status, s.status AS s_status, s.sid
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr


select * from v$session;
select status, osuser, username, process, machine, program, module, logon_time sid, serial# from v$session
Убиваем 7--sid 19--serial#
alter system kill session '7,19'
alter system disconnect session '8,66' immediate
--либо
alter system disconnect session '8,66' post_transaction


ну orakill как раз и прибивает процесс:
SELECT 'orakill ' || i.instance_name || ' ' || p.spid as Kill_cmd
FROM v$process p, v$session s, v$instance i
WHERE p.addr = s.paddr AND
s.status = 'KILLED';

Oracle Purging dba_2pc_pending

Purging dba_2pc_pending

Years ago, when I worked more with Oracle Replication than I do now, I knew more or less every command I needed to handle in-doubt transactions (as a result of failed two phase commit). On one of our development server something went wrong with distributed transactions between Oracle10g and Transparent Gateway for MS SQL Server.

First, I tried to force rollback of transaction listed in dba_2pc_pending:
SQL> rollback force '19.27.91915';

where 19.27.91915 is LOCAL_TRAN_ID from dba_2pc_pending.

I guess the problem was with TG at MS SQL Server side, because Oracle occasionally tried unsuccessfully to execute rollback on MS SQL Server. At the end, developers simply turned off Oracle TG Listener. As a result I got plenty of error messages in alert.log like this:
Fri Sep 26 12:12:38 2008
Errors in file F:\oracle\admin\oraDB\bdump\orcl_reco_1736.trc:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: Message 2063 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [ lines] [TGATEWAY] []

I knew that Oracle has a package procedure that can permanently purge such transactions...., here it is: if you know that database is permanently lost and you can not rollback or commit, then you can use dbms_transaction.purge_lost_db_entry to remove failed transaction.

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

LOCAL_TRAN_ID STATE TRAN_COMMENT
---------------------- ---------------- ------------------------------
19.27.91915 forced rollback

SQL> connect / as sysdba
S
QL> execute dbms_transaction.purge_lost_db_entry('19.27.91915');

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

no rows selected