ITPub博客

首页 > 数据库 > Oracle > 删除临时表空间hang处理

删除临时表空间hang处理

原创 Oracle 作者:passion_of_data 时间:2014-11-19 14:41:34 0 删除 编辑
今天在本机数据库做测试的时候当删除临时表空间时hang住了,等了好久没见反应,
后面果断kill退出会话重来删除成功。经分析遭遇Bug 15913577,真是够背,欲知详细,请容我一一道来。


1、删除表空间,一直处于等待状态,最后不得以ctrl+c:
SQL> alter database default  temporary tablespace temp1;


Database altered.


SQL> drop  tablespace temp including contents and datafiles;














drop  tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


2、查看进程hang的等待事件如下:
11gdb[/home/oracle]$ps -ef |grep LOCAL=YES
oracle   24109 24108  0 15:37 ?        00:00:01 oracleora11gdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   24257 24182  0 16:03 pts/1    00:00:00 grep LOCAL=YES


SQL> select sid,sql_id,status,event from v$session where paddr = (select addr from v$process where spid = 24109);


       SID SQL_ID        STATUS   EVENT                                                           
---------- ------------- -------- ----------------------------------------------------------------
         1 63yna2jqw03x0 ACTIVE   enq: TS - contention                                            




3、做了下systemdump如下:
SQL> oradebug setospid 24109
Oracle pid: 27, Unix process pid: 24109, image: oracle@11gdb (TNS V1-V3)
SQL>  oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 258
Statement processed.
SQL>  oradebug dump systemstate 258
Statement processed.
SQL> exit




4、继续分析dump出来的trace文件
 ----------------------------------------
    SO: 0xa6930740, type: 4, owner: 0xa9a861d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0xa9a861d0, name=session, file=ksu.h LINE:12729, pg=0
    (session) sid: 1 ser: 19 trans: 0x7268d430, creator: 0xa9a861d0
              flags: (0x8100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x48009) -/DDLT2/INC
              DID: , short-term DID: 
              txn branch: (nil)
              edition#: 100              oct: 41, prv: 0, sql: 0x2b596c77fdf0, psql: 0xab2eaf78, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/0, ospid: 24108
      machine: 11gdb program: sqlplus@11gdb (TNS V1-V3)
      application name: sqlplus@11gdb (TNS V1-V3), hash value=2572252297
    Current Wait Stack:
     0: waiting for 'enq: TS - contention'                       <<=============当前等待'enq: TS - contention'
        name|mode=0x54530006, tablespace ID=0x3, dba=0x1
        wait_id=412 seq_num=413 snap_id=1
        wait times: snap=16 min 39 sec, exc=16 min 39 sec, total=16 min 39 sec
        wait times: max=infinite, heur=16 min 39 sec
        wait counts: calls=334 os=334
        in_wait=1 iflags=0x15a0
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 13, ser: 1                     <<=============堵塞源为sid: 13 
      Dumping final blocker:
        inst: 1, sid: 13, ser: 1
    Wait State:
      fixed_waits=0 flags=0x2b boundary=0xa6932628/0
    Session Wait History:
        elapsed time of 23586231 min 16 sec since current wait
     0: waited for 'ksdxexeotherwait'
        =0x0, =0x0, =0x0
        wait_id=436 seq_num=437 snap_id=1
        wait times: snap=0.021888 sec, exc=0.021888 sec, total=0.021888 sec
        wait times: max=30.000000 sec
        wait counts: calls=0 os=0
        occurred after 0.000000 sec of elapsed time
     1: waited for 'ksdxexeotherwait'
        =0x0, =0x0, =0x0
        wait_id=435 seq_num=436 snap_id=1
        wait times: snap=0.025816 sec, exc=0.025816 sec, total=0.025816 sec
        wait times: max=30.000000 sec
        wait counts: calls=0 os=0
        occurred after 0.000000 sec of elapsed time
。。。。。。。。。。
    Process Group: DEFAULT, pseudo proc: 0xa836fac8
    O/S info: user: oracle, term: UNKNOWN, ospid: 24109 
    OSD pid info: Unix process pid: 24109, image: oracle@11gdb (TNS V1-V3)
    Short stack dump: 
ksedsts()+465<-ksdxfstk()+32<-ksdxdocmdmultex()+3405<-ksdxdocmdmult()+29<-ksudmp_proc()+1747<-ksudss()+3712<-ksdxdmp()+1625<-ksdxfdmp()+126<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-ksqcmi()+2848<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktsttdrop()+123<-dtsdrv()+3503<-opiexe()+19369<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+244


分析:会话当前等待'enq: TS - contention',且call stack中有函数ktsttdrop,符合Bug 15913577,
该BUG影响12.2以下的所有系统平台的数据库。
        
5、查看SID: 13对应的进程是什么
SQL> select spid from v$process where addr = (
  2   select paddr from v$session where sid = 13);


SPID
------------------------
23843


SQL> !ps -ef |grep 23843
oracle   23843     1  0 14:41 ?        00:00:00 ora_smon_ora11gdb
oracle   24596 24422  0 17:49 pts/1    00:00:00 /bin/bash -c ps -ef |grep 23843


分析总结:
SMON进程堵了删除临时表空间会话,会话当前等待'enq: TS - contention',且call stack中有函数ktsttdrop,符合Bug 15913577,
采取临时措施,重新sqlplus在删除表空间成功。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25964700/viewspace-1338961/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
DBA

注册时间:2011-06-20

  • 博文量
    53
  • 访问量
    310458