首页 » ORACLE 9i-23ai » Drop temp tablespace command hang in oracle 11g R2

Drop temp tablespace command hang in oracle 11g R2

  • 2014/04/01
  • ORACLE 9i-23ai
  • 932 views
  • Drop temp tablespace command hang in oracle 11g R2已关闭评论

昨天DB 磁盘空间预警空间不足，发现临时表空间扩展的非常之大，于是规划调整临时表空间换个磁盘路径，本来online switch temp tablespace 并不难，结果还是遇到小问题。采用的方法是

1, create new temp tablespace(my case named tempicme)
2, alter database set default temp tablespace to new temp tablesapce
3, check original temp tablespace used at this time
4, try to drop original temp tablespace from DB (my case named temp).

可是最后一步试着删除原temp tablespace 时，等待了好久都无响应， 随后尝试对该表空间进行offline , datafie offline , resize datafile 也是无响应。下面对原空间进行一系列检查，试着找出是哪些进程还hold 些temp tbs的资源。

sys@ICME>SELECT /*+rule*/DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
                id1, id2, lmode, request, type
           FROM V$LOCK
          WHERE (id1, id2, type) IN
                    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
          ORDER BY id1, request;

no rows selected

sys@ICME>select * from v$tablespace;

                 TS# NAME                           INC BIG FLA ENC
-------------------- ------------------------------ --- --- --- ---
                   0 SYSTEM                         YES NO  YES
                   1 SYSAUX                         YES NO  YES
                  10 TEMPICME                       NO  NO  YES
                   4 USERS                          YES NO  YES
                   3 TEMP                           NO  NO  YES
...

sys@ICME>select substr(segment_name,1,30) "Object name", 
  2  substr(segment_type,1,8) "Type", extents, substr(owner,1,20) "Owner"
  3  from dba_segments where tablespace_name in ('TEMP'); 

no rows selected

sys@ICME>select count(*) from uet$ where ts#=3;
            COUNT(*)
--------------------
                   0

sys@ICME>select count(*) from fet$ where ts#=3;
            COUNT(*)
--------------------
                   0

note:
查找uet$和fet$定义
[oracle@dbserver56 ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@dbserver56 admin]$ cat sql.bsq |grep -vi rem|xargs -e grep -n "uet" ;
dcore.bsq:233:  size 225    /* cluster key ~ 25, sizeof(seg$) ~ 50, 5 * sizeof(uet$) ~ 150 */
dcore.bsq:251:create table uet$     

sys@ICME>select status,event,PROGRAM ,blocking_session from v$session 
    where saddr in(select session_addr from v$tempseg_usage where tablespace='TEMP');

STATUS   EVENT                                                            PROGRAM                                BLOCKING_SESSION
-------- ---------------------------------------------------------------- ---------------------------------- --------------------
ACTIVE   Streams AQ: waiting for time management or cleanup tasks         oracle@dbserver56 (Q001)
ACTIVE   smon timer                                                       oracle@dbserver56 (SMON)

TIP:
从上面也可以发现是Q001和SMON 后台进程hold在使用，对于q001了解后进行kill，但是smon还一直占用，这进程是不可乱来。接下来对DB 做systemstate 266 level dump,及drop tablespace做10046 event.

sys@ICME>oradebug setmypid
sys@ICME>oradebug unlimit;
sys@ICME>alter session set events ' immediate trace name systemstate level 266';
sys@ICME>oradebug tracefile_name

# analyze trace file using ass1033.awk

Ass.Awk Version 1.0.33
~~~~~~~~~~~~~~~~~~~~~~
Source file : /oracle/diag/rdbms/icme/icme/trace/icme_ora_18797.trc

System State 1  (2014-03-31 11:08:49.438)
~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~
1:                                      [DEAD] 
2:  waiting for 'pmon timer'            
3:  waiting for 'rdbms ipc message'     
4:  waiting for 'VKTM Logical Idle Wait' 
5:  waiting for 'rdbms ipc message'     
6:  waiting for 'DIAG idle wait'        
7:  waiting for 'rdbms ipc message'     
8:  waiting for 'DIAG idle wait'        
9:  waiting for 'rdbms ipc message'     
10: waiting for 'rdbms ipc message'     
11: waiting for 'rdbms ipc message'     
12: waiting for 'rdbms ipc message'     
13: waiting for 'rdbms ipc message'     
14: waiting for 'rdbms ipc message'     
15: waiting for 'rdbms ipc message'     
16:                                     
17: waiting for 'rdbms ipc message'     
18: waiting for 'rdbms ipc message'     
     Cmd: Select
38: waiting for 'Streams AQ: waiting for time management or cleanup tasks' 
...
Tip:
Not found  clue. to check trace file manual.

#########
 Current Wait Stack:
      Not in wait; last wait ended 0.003796 sec ago
    Wait State:
      fixed_waits=0 flags=0x21 boundary=(nil)/-1
    Session Wait History:
        elapsed time of 0.003861 sec since last wait
     0: waited for 'smon timer'
        sleep time=0x12c, failed=0x0, =0x0
        wait_id=214331 seq_num=17727 snap_id=1
        wait times: snap=11.909072 sec, exc=11.909072 sec, total=11.909072 sec
        wait times: max=5 min 0 sec
        wait counts: calls=1 os=4
        occurred after 0.137277 sec of elapsed time
     1: waited for 'smon timer'
        sleep time=0x12c, failed=0x0, =0x0
        wait_id=214330 seq_num=17726 snap_id=1
        wait times: snap=2 min 47 sec, exc=2 min 47 sec, total=2 min 47 sec
        wait times: max=5 min 0 sec
        wait counts: calls=1 os=56
        occurred after 0.002549 sec of elapsed time
     2: waited for 'smon timer'
        sleep time=0x12c, failed=0x0, =0x0
        wait_id=214329 seq_num=17725 snap_id=1
		...

#10046 EVENT

sys@ICME>oradebug setmypid
sys@ICME>oradebug event 10046 trace name context forever,level 12
sys@ICME>drop tablespace temp;
drop tablespace temp

# wait 5 minutes, ctrl+ c
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

#To formant Trace file

[oracle@dbserver56 trace]$ tkprof  icme_ora_26936.trc  ~/drop_tts.out    

#check 
********************************************************************************

SQL ID: 1f60qsfj6rnrk Plan Hash: 0
drop tablespace temp

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02     240.26          0          0          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02     240.26          0          0          1           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS       
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited  
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                                 1        0.00          0.00
  enq: TS - contention                            1      240.23        240.23
  log file sync                                   1        0.00          0.00
  SQL*Net break/reset to client                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
********************************************************************************

TIP:
接下来以 drop temp tablespace 和smon 进程 及 enq: TS – contention 等待事件查询一下是否有相关bug. 最后我在MOS 看到了1500044.1 非常的接近

Cause
This seems to be caused by Bug 13028161: DROP TEMP TABLESPACE HANGS DUE TO TS ENQUEUE CONTENTION, still being worked by the Development Team.

Solution
The following workaround could be used until a permanent fix for the bug is released

– Complete all the needed prerequisites (create the new temp tablespace and set as default and no user is assigned to the old tablespace)
– Bounce the DB
– Once the DB starts up disconnect from SQLPLUS completely and close the console
– Open a new console and launch SQLPLUS
– Run the drop command as the VERY FIRST COMMAND
In case of temporary tablespace groups being used, create a new temporary tablespace Group and drop the old temporary tablespace group.

Summary:
在不重启DB的前提下我尝试一些方法后未果，于时申请下班后重启了DB ，发现SMON 也释放了TEMP TBS 的资源占用，最后顺利DROP。

Related Posts:

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：