生产报表数据库出现了活动事务的回滚段损坏（一）

来源：互联网发布：windows 7摄像头编辑：程序博客网时间：2024/05/16 18:33

接到业务人员的报告，说是报表数据库最近很慢，于是登陆上去检查。结果确意外的发现有一个异常的活动事务：

START_TIME SID SERIAL# SEGMENT_ID SEGMENT_NAME PROCESS SPID SES_ADDR LOCKWAIT USED_UBLK
-------------------- ------ ------- ---------- ------------ ------------ ------------ ---------------- ---------------- ----------
12/30/05 01:48:33 71 20835 19 _SYSSMU19$ 23055 15664 C0000001A45BC4A0 1

START_TIME              SID SERIAL# SES_ADDR             XIDUSN OWNER
-------------------- ------ ------- ---------------- ---------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OS_USER_NAME                   ORACLE_USERNAME
------------------------------ ------------------------------
12/30/05 01:48:33        71   20839 C0000001A45BC4A0         19 REPORT
WAP_AUTHPRICE_USER_SP_RD
report16                       REPORT

SID SERIAL# SPID USERNAME TERMINAL PROGRAM
------ ------- ------------ ---------- -------------------- ----------------------------------------
71 20913 15664 oracle UNKNOWN oracle@rdb01 (TNS V1-V3)

经过反复的检查，我们发现这个回滚段的事务始终是active的，并且其并没有任何正在做回滚段动作：

SQL> /

SID XIDUSN USED_UBLK
------ ---------- ----------
71 19 1

SQL> /

SID XIDUSN USED_UBLK
------ ---------- ----------
71 19 1

SQL> /

SID XIDUSN USED_UBLK
------ ---------- ----------
71 19 1

SQL>

更加奇怪的是，这个spid 15664在操作系统上根本就不存在！此时，已经开始怀疑，有人曾将在这个事务繁忙时，将其从操作系统上直接kill了，因此这个spid在操作系统上已经不存在了，但是由于当时有活动事务存在于回滚段，因此，这个事务的sid始终存在于数据库中：

oracle@rdb01:/tmp/lunar/rda/output > ps -ef | grep 15664
oracle 21452 21450 0 12:48:10 pts/tg 0:00 grep 15664
oracle@rdb01:/tmp/lunar/rda/output >

这时检查等待事件：

       SID EVENT                                                                    P1         P2
---------- ---------------------------------------------------------------- ---------- ----------
         1 pmon timer                                                             1000          0
        71 control file sequential read                                              0          9
        70 db file sequential read                                                 410      67836
       126 db file sequential read                                                 284      82227
       153 db file sequential read                                                 429     211496
        96 direct path read                                                         98      47845
       170 direct path read                                                        163       5477
       161 direct path read                                                       1006      41733
        61 direct path write                                                      1006     275069
         5 smon timer                                                              300          0
        97 PX Deq Credit: need buffer                                        268566527          2
        86 PX Deq Credit: send blkd                                          268566527          1
        26 PX Deq: Execute Reply                                                   200          2

没有什么收获。

现在来看看他正在操作哪些对象：

Enter value for sid: 71
old 3: (select SQL_HASH_VALUE from v$session where sid ='&sid')
new 3: (select SQL_HASH_VALUE from v$session where sid ='71')

SQL_TEXT
----------------------------------------------------------------
INSERT INTO WAP_AUTHPRICE_USER_SP_RD (LOCATIONID,ICPID,SUBSCAT
,ICPATTR,ICPCODE,COUNTS,PERIOD,STAT_TIME) VALUES (:LOCATIONID,:I
CPID,:SUBSCAT,:ICPATTR,:ICPCODE,:COUNTS,:PERIOD,TO_DATE(:STAT_TI
ME,'yyyy-mm-dd hh24:mi:ss'))

于是想到，首先kill这个sid，试试看：

SQL> alter system kill session '71,20979';
alter system kill session '71,20979'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

SQL> select sid,serial# from v$session where sid=71;

SID SERIAL#
------ -------
71 20986

SQL> /

SID SERIAL#
------ -------
71 20986

SQL> /

SID SERIAL#
------ -------
71 20986

SQL> alter system kill session '71,20986';
alter system kill session '71,20986'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

SQL> select sid,serial# from v$session where sid=71;

SID SERIAL#
------ -------
71 20988

SQL> alter system kill session '71,20988';
alter system kill session '71,20988'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

SQL>
可见，这个sid根本就不是一个正常的会话，不能被kill。