刪除臨時表空間hang處理
今天在本機資料庫做測試的時候當刪除臨時表空間時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
分析:會話當前等待'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在刪除表空間成功。
後面果斷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
分析:會話當前等待'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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Oracle故障處理】-Oracle9i臨時表空間刪除重建Oracle
- 臨時表空間的建立、刪除,設定預設臨時表空間
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- Oracle Temp臨時表空間處理Oracle
- 臨時表空間資料刪除問題
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 刪掉Oracle臨時表空間Oracle
- oracle臨時表空間過大的原因&&處理Oracle
- 【Oracle-資料庫維護】-刪除臨時表空間Oracle資料庫
- 臨時表空間的增刪改查
- oracle8i誤刪除臨時表空間後的恢復Oracle
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查:Oracle
- oracle臨時表空間的增刪改查Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法!Oracle
- IZ0-053 Q699(臨時表空間臨時檔案被刪除恢復)
- Jenkins臨時空間不足處理辦法Jenkins
- 一次臨時表空間大量佔用問題的處理
- mysql臨時表,臨時表空間,ibtmp1表空間暴增原因初探MySql
- Cancel 刪除 正在使用的臨時表空間的操作 將導致異常
- oracle 表空間 不足時如何處理Oracle
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- 刪除UNDO表空間並處理ORA-01548問題
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- 刪除表空間和表空間包含的檔案
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案(續)
- oracle臨時表空間相關Oracle
- MySQL InnoDB臨時表空間配置MySql
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle