ORA-25402 transaction must roll back 且无法找到SID来处理的问题

最新推荐文章于 2021-04-11 06:32:14 发布

cti27515

最新推荐文章于 2021-04-11 06:32:14 发布

阅读量496

点赞数

文章标签：数据库网络

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