消费报表数据库泛起了举止变乱的回滚段破损（一）
来源：网海拾贝
接到业务职员的申报，说是报表数据库最近很慢，于是登陆上去反省。结果确意外的创造有一个非常的举止变乱：
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。
版权声明：
原创作品，许可转载，转载时请务必以超链接要领标明文章 原始原因 、作者信息和本声明。否则将清查功令责任。
