问题描述
唉，悲剧的月初啊，星期天的早上8点去客户的路上就接到电话说，查询dba_free_space的进程一直hang住，查询不出来结果，到现场后，手动查询dba_free_space果然hang住。
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name * ERROR at line 1: ORA-01013: user requested cancel of current operation
专家解答
故障现象分析：
1，查询v$lock视图发现如下的信息
CURTIME SESS HASH_VALUE ID1 ID2 LMODE REQUEST TYPE CTIME ---------- ------------------------- ---------- ---------- ---------- ---------------- ---------------- ------------------------- ---------- 09:08:08 Waiter: stat1:67 1477532720 85 0 4||Share Temp Table 35705 09:08:08 Waiter: stat1:152 2159410169 85 0 4||Share Temp Table 1813 09:08:08 Waiter: stat1:311 4053158416 85 0 4||Share Temp Table 29645 09:08:08 Waiter: stat1:374 1477532720 85 0 4||Share Temp Table 35705 09:08:08 Waiter: stat1:582 4053158416 85 0 4||Share Temp Table 31080 09:08:08 Waiter: stat1:591 1478719543 85 0 4||Share Temp Table 105 09:08:08 Waiter: stat1:399 4053158416 85 0 4||Share Temp Table 27042 09:08:08 Waiter: stat1:31 3374778041 85 1787451954 6||Exclusive Other type 37445 09:08:08 Holder: stat1:31 3374778041 85 0 6||Exclusive Temp Table
2，trace进程发现如下信息
SQL> oradebug setospid 16772 Oracle pid: 47, Unix process pid: 16772, image: oracle@query1 (TNS V1-V3) SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. SQL> oradebug tracefile_name /oracle/app/oracle/admin/stat1/udump/stat1_ora_16772.trc WAIT #1: nam='enqueue' ela= 2939375 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939300 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939330 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939432 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939343 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939370 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939358 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939362 p1=1414791172 p2=85 p3=0 WAIT #1: nam='enqueue' ela= 2939367 p1=1414791172 p2=85 p3=0
3，查询当前的sql
SQL> select sid,username,program,module,osuser ,sql_hash_value,prev_hash_value from v$session where sid=31; SID USERNAME PROGRAM MODULE OSUSER SQL_HASH_VALUE ---------- ------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------------ -------------- PREV_HASH_VALUE --------------- 31 SYS sqlplus@query1 (TNS V1-V3) sqlplus@query1 (TNS V1-V3) oracle 0 3374778041 SQL> select sql_text from v$sql where hash_value=3374778041; SQL_TEXT -------------------------------------------------------------------------------------------------------------------- select file#, block# from seg$ where ts# = :1
通过上面的信息，可以看出进程31自己把自己给阻塞了，锁的类型是tt，而执行的切是一个select语句，怀疑是由于bug导致的，由于tt多数都是bug导致。
通过mos查询到tt相当的一些bug
Bug 8313598 – ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8] Bug 4732503 – Self-deadlock on TT enqueue [ID 4732503.8] Bug 6809093 – Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8] Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1] Bug 3397983 – Sessions may hang waiting for TT enqueue [ID 3397983.8] Bug 3467364 – Adding datafiles serializes on the TT enqueue [ID 3467364.8] Bug 8332021 – Cannot add a datafiles when sessions reporting ORA-1653 / TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8] Bug 2272671 – DEADLOCK possible on CREATE INDEX ONLINE (KGL lock v. TT enqueue) [ID 2272671.8] Bug 6762619 : ENQUEUE “TT” REMAINS WHEN ACCESSING DBA_FREE_SPACE Bug 6858962 : TT AND US ENQUEUE DEADLOCK Bug 9439759 : TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY Bug 6265482 : TT ENQUEUE REMAINS AFTER CANCEL “SELECT * FROM DBA_EXTENTS” Bug 7217723 : ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE Bug 4732503 : SELF-DEADLOCK TT ENQUEUE ON 9.2.0.7 SIMILAR TO 3425298 Bug 9403168 : WAIT FOR TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS Bug 9451566 : DEADLOCK ON TT-ENQUEUE Bug 3833893 : SMON GETS “TT ENQUEUE” OF TEMP IF DATEFILE OF TEMP DOESN’T EXIST. Bug 9948775 : SELF-DEADLOCK ON TT ENQUEUE 此信息来生于maclean的blog
查询的n个bug就是没有找到一个跟我们环境一样的。在数据库中没有找到其它有异常的进程，查看了一下数据库，已经有几年没有重启过，实例是10年起起来的，想想那时，我还当入dba这个行业，于是想是否能通过重启解决问题，当跟客户一说，客户直接就重启了，重启后，问题得到解决。
SQL> select * from v$instance; INSTANCE_NUMBER INSTANCE HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_ LOGINS SHU --------------- -------- ---------------------------------------------------------------- ----------------- ------------------- ------------ --- ---------- ------- ----------- ---------- --- DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST ----------------- ------------------ --------- 1 stat1 query1 9.2.0.8.0 2010-11-17 21:41:02 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
