临盆报表数据库出现了流动事件的回滚段破坏(一)

滥觞:网海拾贝




 

接到业务职员的陈述,说是报表数据库最近很慢,于是上岸上去搜查。成效确不测的创造有一个非常的流动事件:

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。




版权声明: 原创作品,允许转载,转载时请务必以超链接情势标明文章 原始因由 、作者信息和本声明。不然将追查执法责任。

posted @ 2011-03-07 21:32  蓝色的天空III  阅读(109)  评论(0编辑  收藏  举报