ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-25402 transaction must roll back 且无法找到SID来处理的问题

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

原创 Linux操作系统 作者:westzq1984 时间:2009-07-25 01:56:06 0 删除 编辑

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

 

 

--启动实例12

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来杀死事务来回滚。只能重启

 

实验过errorstack10046,都不能得到什么相关的信息

 

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

如下解决方法只是反向工程处理的结果,不保证正确性,正确性待验证

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

查看了gv$transactionSQL:

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

 

有了XIDUSNXIDSLOTXIDSQN,可以考虑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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 段头损坏的处理
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    951378