drop tablespace temp~等待事件 enq: TS - contention

saup007

要重建临时表空间（太大了，重新创建一个小的）：
参考：http://www.eygle.com/archives/20 ... ary_tablespace.html

这个是我在itpub发的贴子，暂时没有好办法。。。

create temporary tablespace temp2 tempfile '/u01/oradata/[SID]/temp02.dbf' size 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

alter database default temporary tablespace temp2;

select username,temporary_tablespace from dba_users;

drop tablespace temp including contents and datafiles;
此时不能删除，v$session_wait 等待事件 enq: TS - contention

在网上找了一下：SMON后台清理

随便一个新session执行
alter session set events 'immediate trace name DROP_SEGMENTS level 4';

但是依然不行，肿么办？

通过查询，得到使用temp的PID，当然在数据库里v$sort_usage、v$session也可以查询
/usr/sbin/lsof | grep temp01.dbf

1. select * from v$session t1 where t1.PADDR in (select t2.addr from v$process t2 where t1.PADDR=t2.addr and t2.spid in( 1799, 1799, 1984, 1984, 6092, 6094, 6098,15992,25827,25829))

复制代码

INACTIVE就没在关注，ACTIVE状态的有如下：
schemaname                  program
                     
SYS                              oracle@game (SMON)
SYS                              oracle@game (DBW0)
SYS                              oracle@game (LGWR)

还有几个INACTIVE的schema

SMON、DBW0、LGWR三个后台进程

/usr/sbin/lsof | grep temp02.dbf
也有相同SMON、DBW0、LGWR三个后台进程

难道我只能等了吗？已经一周多了。


[ 本帖最后由 saup007 于 2012-4-6 14:53 编辑 ]

Maclean Liu(刘相兵

Action Plan:

做一个systemstate dump 并 打包压缩后上传：

确认是 非RAC系统，且enq: TS - contention等待时间正存在：

oradebug setmypid;
oradebug unlimit;
oradebug dump systemstate 266;


oradebug tracefile_name;


<===============上传以上显示的TRACE文件

saup007

不是RAC，是Data Guard系统。最高可用模式

只有当drop tablespace temp including contents and datafiles;时才会有enq: TS - contention等待

那我就执行这个SQL，再dump 吧？
没做过dump，
oradebug setmypid;
oradebug unlimit;
oradebug dump systemstate 266;
这三个做完就退出，生成三个，还是生成一个，你比较容易看？

[ 本帖最后由 saup007 于 2012-4-6 15:35 编辑 ]

Maclean Liu(刘相兵

只会生成一个TRACE文件，打包上传即可

saup007

附件上传了。
版本：
10.2.0.5.0

Maclean Liu(刘相兵

ODM DATA:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_2
System name:    Linux
Node name:      game
Release:        2.6.9-55.ELsmp
Version:        #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine:        i686


==> 10.2.0.5 on Linux x86


[root@vrh2 ~]# awk -f ass109.awk wending_ora_2545.trc


Blockers
~~~~~~~~

        Above is a list of all the processes. If they are waiting for a resource
        then it will be given in square brackets. Below is a summary of the
        waited upon resources, together with the holder of that resource.
        Notes:
        ~~~~~
         o A process id of '???' implies that the holder was not found in the
           systemstate.

                    Resource Holder State
Enqueue TS-00000003-00000001     8: waiting for 'smon timer'

Object Names
~~~~~~~~~~~~
Enqueue TS-00000003-00000001                                 



SMON 持有Enqueue TS-00000003-00000001     ，SMON本身  'smon timer' 空闲等待

PROCESS 8:
  ----------------------------------------
  SO: 0x9fab23e8, type: 2, owner: (nil), flag: INIT/-/-/0x00
  (process) Oracle pid=8, calls cur/top: 0x9d2c41d0/0x9d2c41d0, flag: (16) SYSTEM
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 121
              last post received-location: kcbzww
              last process to post me: 9fae39cc 196 0
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 9fab1860 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x9fc2287c
    O/S info: user: oracle, term: UNKNOWN, ospid: 6098
    OSD pid info: Unix process pid: 6098, image: oracle@game (SMON)
    Short stack dump:
ksdxfstk()+19<-ksdxcb()+1366<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+221<-skgpwwait()+139<-ksliwat()+774<-kslwaitns_timed()+35<-kskthbwt()+194<-kslwait()+135<-kt
mmon()+536<-ktmSmonMain()+22<-ksbrdp()+706<-opirip()+505<-opidrv()+502<-sou2o()+91<-opimai_real()+232<-main()+111<-__libc_start_main()+211

      SO: 0x9d3a96f4, type: 5, owner: 0x9d26be50, flag: INIT/-/-/0x00
      (enqueue) TS-00000003-00000001    DID: 0001-0008-00000002
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x2
      mode: SX, lock_flag: 0x0, lock: 0x9d3a9708, res: 0x9d513e74
      own: 0x9d26be50, sess: 0x9d26be50, proc: 0x9fab23e8, prv: 0x9d513e7c

   SO: 0x9d26be50, type: 4, owner: 0x9fab23e8, flag: INIT/-/-/0x00
    (session) sid: 1125 trans: (nil), creator: 0x9fab23e8, flag: (100051) USR/- BSY/-/-/-/-/-
              DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
    service name: SYS$BACKGROUND
    waiting for 'smon timer' wait_time=0, seconds since wait started=126
                sleep time=12c, failed=0, =0
                blocking sess=0x(nil) seq=1829
    Dumping Session Wait History
     for 'smon timer' count=1 wait_time=2 min 2 sec
                sleep time=12c, failed=0, =0
     for 'db file sequential read' count=1 wait_time=0.000147 sec
                file#=1, block#=11d8, blocks=1
     for 'db file sequential read' count=1 wait_time=0.000254 sec
                file#=1, block#=11df, blocks=1
     for 'db file sequential read' count=1 wait_time=0.000201 sec
                file#=1, block#=f017, blocks=1
     for 'db file sequential read' count=1 wait_time=0.000252 sec


=====================================>
SMON hold TS-00000003-00000001  in SX mode , and wait for 'smon timer'



          SO: 0x9d3a7f64, type: 5, owner: 0x94586f90, flag: INIT/-/-/0x00
      (enqueue) TS-00000003-00000001    DID: 0001-0065-002008D5
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x2
      req: X, lock_flag: 0x0, lock: 0x9d3a7f78, res: 0x9d513e74
      own: 0x9d17ae90, sess: 0x9d17ae90, proc: 0x9fad3c1c, prv: 0x9d513e84


   SO: 0x9d17ae90, type: 4, owner: 0x9fad3c1c, flag: INIT/-/-/0x00
    (session) sid: 931 trans: 0x9cd39300, creator: 0x9fad3c1c, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0065-002008D5, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              oct: 41, prv: 0, sql: 0xb72dfc04, psql: 0x91dc06d8, user: 253/DBA_LIUJINLIN
    service name: WENDING_TAF.LK
    O/S info: user: Liujinlin, term: LIUJINLIN, ospid: 4960:6756, machine: WORKGROUP\LIUJINLIN
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: SQL ′°?ú - grant select on ECHAT, hash value=1064613069
    waiting for 'enq: TS - contention' wait_time=0, seconds since wait started=34
                name|mode=54530006, tablespace ID=3, dba=1
                blocking sess=0x0x9d26be50 seq=211
    Dumping Session Wait History
     for 'enq: TS - contention' count=1 wait_time=1.207939 sec
                name|mode=54530006, tablespace ID=3, dba=1
     for 'enq: TS - contention' count=1 wait_time=2.930486 sec
                name|mode=54530006, tablespace ID=3, dba=1
     for 'enq: TS - contention' count=1 wait_time=2.931502 sec
                name|mode=54530006, tablespace ID=3, dba=1
     for 'enq: TS - contention' count=1 wait_time=2.931485 sec
                name|mode=54530006, tablespace ID=3, dba=1
     for 'enq: TS - contention' count=1 wait_time=2.930535 sec
                name|mode=54530006, tablespace ID=3, dba=1
                                


==============================>

sid: 931 wait for   'enq: TS - contention',  request to hold   TS-00000003-00000001  X mode ,  SID 931 被阻塞

Maclean Liu(刘相兵

ODM FINDING:

mos上的相关Note:

Bug 8982705: SMON HOLDING TS ENQUEUE IS SX MODE WHILE WAITING FOR SMON TIMER

Hdr: 9902939 10.2.0.4 RDBMS 10.2.0.4 SPACE PRODID-5 PORTID-23
Abstract: AN IDLE SMON PROCESS HOLDS A TS ENQUEUE INDEFINITELY CAUSING DATABASE HANG

  BUG TYPE CHOSEN
  ===============
  Performance
  
  SubComponent: Dictionary
  ========================
  DETAILED PROBLEM DESCRIPTION
  ============================
  o Session waits for "enq: TS - contention" on TEMP tablespace is blocking
  all the other processes, and even if killed, another similar session blocks
  the others.
  o Analysis shows that an idle SMON process holds a TS enqueue indefinitely
  causing database hang.


bug 可能导致SMON 无休止地持有 TEMP tablespace的TS euqueue ，导致其他session被阻塞。 该Bug Note没有提到 存在patch 或 workaround的方式。


建议：
1.
可以通过设置诊断事件event=’10061 trace name context forever, level 10′禁用SMON清理临时段(disable SMON from cleaning temp segments)。

alter system set events '10061 trace name context forever, level 10';

http://www.oracledatabase12g.com ... porary-segment.html

2. 设置以上事件10061 events估计仍无法 释放目前 SMON持有的 TS enqueue

action:


忽略该问题， 等待下次重启实例时drop 原临时表空间

saup007

嗯。谢谢分析。。。。

thanks~！