ORA-25402 transaction must roll back 且無法找到SID來處理的問題

ORA-25402 transaction must roll back 且無法找到SID來處理的問題

 

今天在客戶現場遇到了這個問題，當時自己的電腦不讓連線伺服器，又沒有SYS的密碼，客戶機器上的工具太難用，沒時間處理問題。最後通過重啟伺服器解決的。其實想了下，用errorstack就可以搞定

 

回來做了下現場模擬

 

環境是這樣的，一個庫（源庫）通過DB LINK向另外一個資料庫（目標庫）寫入資料，目標庫為RAC(測試的是9版本)，源庫(測試的也為9版本)的DBLINK使用了TAF

 

TAF指向的TNSNAMES連線:

RAC =   (DESCRIPTION =     (LOAD_BALANCE = ON)     (FAILOVER = ON)     (ADDRESS = (PROTOCOL = TCP)(HOST = WESTZQ1)(PORT = 1521))     (ADDRESS = (PROTOCOL = TCP)(HOST = WESTZQ2)(PORT = 1521))     (CONNECT_DATA =       (service_name = rac9i)       (failover_mode =         (type = select)         (method = basic)       )     )   )

 

實驗

源庫	目標庫
SQL> INSERT INTO test_25402@rac VALUES(1);   1 row created.
SQL> SELECT DISTINCT sid FROM v$mystat;          SID ----------         10   SQL> SELECT spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid=10);   SPID ------------ 6617
	SQL> SELECT inst_id,sid FROM gv$session WHERE process='6617';      INST_ID        SID ---------- ----------          2         15 DBLINK是連線到例項2的
	關閉例項2 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> select count(*) from test1;     COUNT(*) ----------       6248   SQL> select count(*) from test_25402@rac;  select count(*) from test_25402@rac                      * ERROR at line 1: ORA-02055: distributed update operation failed; rollback required ORA-02068: following severe error from RAC ORA-03113: end-of-file on communication channel     SQL> / select count(*) from test_25402@rac                      * ERROR at line 1: ORA-25402: transaction must roll back
--new session          SQL> SELECT * FROM v$lock WHERE sid=10;   ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST -------- -------- ---------- -- ---------- ---------- ---------- ----------      CTIME      BLOCK ---------- ---------- 52FABC40 52FABD4C         10 TX     720934          5          6          0       1003          0   可以看到還有鎖資訊
	關閉例項1 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> rollback; rollback * ERROR at line 1: ORA-02068: following severe error from RAC ORA-03113: end-of-file on communication channel
SQL>  SELECT * FROM v$lock WHERE sid=10;   no rows selected
	--啟動例項1，2
SQL> select count(*) from test_25402@rac; select count(*) from test_25402@rac                                 * ERROR at line 1: ORA-25402: transaction must roll back
SQL> select * from v$transaction;   no rows selected

 

這個時候，事務的SID丟失，如果是BS結構中，某個連線出這個錯誤，無法找到SID來殺死事務來回滾。只能重啟

 

實驗過errorstack，10046，都不能得到什麼相關的資訊

 

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

如下解決方法只是反向工程處理的結果，不保證正確性，正確性待驗證

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

檢視了gv$transaction的SQL:

select inst_id,ktcxbxba,kxidusn,kxidslt,kxidsqn,ktcxbkfn,kubablk, kubaseq,kubarec, decode(ktcxbsta,0,'IDLE',1,'COLLECTING',2,'PREPARED',3,'COMMITTED',                 4,'HEURISTIC ABORT',5,'HEURISTIC COMMIT',                 6,'HEURISTIC DAMAGE',7,'TIMEOUT',9,'INACTIVE',                 10,'ACTIVE',11,'PTX PREPARED',12,'PTX COMMITTED',                 'UNKNOWN'), ktcxbstm,ktcxbssb,ktcxbssw, ktcxbsen,ktcxbsfl,ktcxbsbk,ktcxbssq,ktcxbsrc, ktcxbses,ktcxbflg, decode(bitand(ktcxbflg,16),0,'NO','YES'), decode(bitand(ktcxbflg,32),0,'NO','YES'), decode(bitand(ktcxbflg,64),0,'NO','YES'), decode(bitand(ktcxbflg,8388608),0,'NO','YES'), ktcxbnam, ktcxbpus,ktcxbpsl,ktcxbpsq, ktcxbpxu,ktcxbpxs,ktcxbpxq, ktcxbdsb, ktcxbdsw, ktcxbubk,ktcxburc,ktcxblio,ktcxbpio,ktcxbcrg,ktcxbcrc from x$ktcxb where bitand(ksspaflg,1)!=0 and bitand(ktcxbflg,2)!=0

 

發現這個檢視存在一個過濾。去掉BITAND(KTCXBFLG, 2) != 0 ，可以得到幾條資料。

根據前面得到的前面這個事務的ADDR  52FABC40,進行反向工程，得到如下條件

 

SQL> SELECT addr,xidusn,xidslot,xidsqn FROM my_transaction WHERE ubafil=0 AND ubablk=0 AND ubasqn=0 AND start_time IS NOT NULL;

 

ADDR         XIDUSN    XIDSLOT     XIDSQN

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

52FABC40         11         38          5

 

有了XIDUSN，XIDSLOT，XIDSQN，可以考慮logminer

 

SELECT   timestamp,

           session#,

           username,

           sql_redo

    FROM   V$LOGMNR_CONTENTS

   WHERE   xidusn = 11 AND xidslt = 38 AND xidsqn = 5

ORDER BY   scn

 

TIMESTAMP                       SESSION#        USERNAME        SQL_REDO

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

2009-7-25 0:27:53       10              CTAIS2  set transaction read write;

2009-7-25 0:46:33       10              CTAIS2  rollback;

 

現在就找到了這個SID，可以把這個SESSION給殺掉了

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-610401/，如需轉載，請註明出處，否則將追究法律責任。