与temp表空间相关的操作数据库都会hang

最新推荐文章于 2022-06-02 10:39:48 发布
最新推荐文章于 2022-06-02 10:39:48 发布
阅读量599 收藏
点赞数
分类专栏： 故障 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/112589111
版权

目录

故障分析

1、查看等待事件（在等什么）

2、分析阻塞源（源头在做什么）

3、跟踪

3.1先对smon进程做10046跟踪：

3.2strace 跟踪smon

4、一波未平一波又起

4.1查看等待事件和阻塞源，是否和之前是一致的。

5、终极大招

6、后续操作

7、测试是否正常

8、故障总结

后记

业务维护人员在登录数据库的时候，temp表空间使用率告警，他就去重新resize tempfile的大小，但是在resize 32G的时候，由于手误写错了，resize 2G,发现不对，就立即ctrl+c取消操作。

之后又往temp表空间里面添加了2个数据文件。操作如下：

操作完成后，发现在查询temp表空间相关信息的时候，就会hang，没有结果。

如以上操作，查询视图v$temp_space_header，可以统计临时表空间的使用情况，但是一直会hang。补充：查询此视图的结果不准确，具体参考：ORACLE使用GV_$TEMP_SPACE_HEADER统计临时表空使用情况不准确的问题

查询dba_temp_files数据文件，也会hang。

故障分析

1、查看等待事件（在等什么）

执行上面的查询语句，造成数据库会话hang。

session 1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        77
SQL> select * from v$temp_space_header;

。。。。

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        792
SQL> select * from dba_temp_files;

。。。。

查询数据库的等待事件.因为我们已经知道是那条sql造成的hang，所以没必要查询全库的等待事件，只需要查看执行该sql的会话在等待什么即可。

select blocker_instance_id holder_inst,
       blocker_sid holder_sid,
       (select sql_text
          from gv$session b, gv$sql c
         where b.inst_id = a.blocker_instance_id
           and b.sid = a.blocker_sid
           and c.inst_id = c.inst_id
           and c.sql_id = nvl(b.sql_id, b.prev_sql_id)
           and rownum = 1) sql,
       inst_id waiter_inst,
       sid waiter_sid,
       (select sql_text
          from gv$session b, gv$sql c
         where b.inst_id = a.inst_id
           and b.sid = a.sid
           and c.inst_id = c.inst_id
           and c.sql_id = b.sql_id
           and rownum = 1) sql,
       wait_event_text event
  from gv$session_blockers a;

输出结果为：

HOLDER_INST HOLDER_SID SQL                                                WAITER_INST WAITER_SID SQL                                                EVENT
----------- ---------- -------------------------------------------------- ----------- ---------- -------------------------------------------------- --------------------
          1        957 select f.file#, f.block#, f.ts#, f.length from fet           1         77 select * from V$TEMP_SPACE_HEADER                  buffer busy waits
                       $ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an
                       d t.bitmapped=0

          1        957 select f.file#, f.block#, f.ts#, f.length from fet           1        792  select * from dba_temp_files                      buffer busy waits
                       $ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an
                       d t.bitmapped=0

          1        384                                                              1        957                                                    local write wait
我们可以看出：sid=77和sid=792会话都在等待：buffer busy waits。阻塞源头都是sid=957。

补充：

buffer busy waits

常见的出现在以下场景：
1）同时对同一个块进行写操作时的等待
2）代表并发的DML
3）大量针对单调递增索引的更新或者插入（index contention）
4)通过分区分表、避免单调递增索引等操作缓解数据块争用。
这个等待事件有三个参数：
File#： 要读取的数据块锁在数据文件的文件号。
Block#： 要读取的起始数据块号。
class#：块类型

上面只是单个用户查询一个简单的动态性能视图，好像不太符合上面所说的场景

2、分析阻塞源（源头在做什么）

即然是sid=957的会话阻塞了会话，那么我们看下sid=957的会话到底在干什么？

SQL> @sid
Enter value for inst_id: 1
Enter value for sid_in: 957
INST_ID                       : 1
SADDR                         : 000000021A4A7AC0
SID                           : 957
SERIAL#                       : 1
AUDSID                        : 0
PADDR                         : 000000021FB6DDA8
USER#                         : 0
USERNAME                      :
COMMAND                       : 0
OWNERID                       : 2147483644
TADDR                         :
LOCKWAIT                      :
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 0
SCHEMANAME                    : SYS
OSUSER                        : oracle
PROCESS                       : 4749
MACHINE                       : ekpdb
PORT                          : 0
TERMINAL                      : UNKNOWN
PROGRAM                       : oracle@ekpdb (SMON)
TYPE                          : BACKGROUND
SQL_ADDRESS                   : 00
SQL_HASH_VALUE                : 0
SQL_ID                        :
SQL_CHILD_NUMBER              :
SQL_EXEC_START                :
SQL_EXEC_ID                   :
PREV_SQL_ADDR                 : 000000021E800538
PREV_HASH_VALUE               : 4052085454
PREV_SQL_ID                   : chsyr0gssbuqf
PREV_CHILD_NUMBER             : 0
PREV_EXEC_START               : 2021-01-13 16:05:38
PREV_EXEC_ID                  : 16786696
PLSQL_ENTRY_OBJECT_ID         :
PLSQL_ENTRY_SUBPROGRAM_ID     :
PLSQL_OBJECT_ID               :
PLSQL_SUBPROGRAM_ID           :
MODULE                        :
MODULE_HASH                   : 0
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 0
ROW_WAIT_OBJ#                 : -1
ROW_WAIT_FILE#                : 202
ROW_WAIT_BLOCK#               : 11
ROW_WAIT_ROW#                 : 0
TOP_LEVEL_CALL#               : 59
LOGON_TIME                    : 2020-12-01 18:37:51
LAST_CALL_ET                  : 3712029
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       :
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : DISABLED
PQ_STATUS                     : DISABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :
BLOCKING_SESSION_STATUS       : VALID
BLOCKING_INSTANCE             : 1
BLOCKING_SESSION              : 384
FINAL_BLOCKING_SESSION_STATUS : VALID
FINAL_BLOCKING_INSTANCE       : 1
FINAL_BLOCKING_SESSION        : 384
SEQ#                          : 18895
EVENT#                        : 90
EVENT                         : local write wait
P1TEXT                        : file#
P1                            : 202
P1RAW                         : 00000000000000CA
P2TEXT                        : block#
P2                            : 11
P2RAW                         : 000000000000000B
P3TEXT                        :
P3                            : 0
P3RAW                         : 00
WAIT_CLASS_ID                 : 1740759767
WAIT_CLASS#                   : 8
WAIT_CLASS                    : User I/O
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 8
STATE                         : WAITING
WAIT_TIME_MICRO               : 7831445
TIME_REMAINING_MICRO          : -1
TIME_SINCE_LAST_WAIT_MICRO    : 0
SERVICE_NAME                  : SYS$BACKGROUND
SQL_TRACE                     : DISABLED
SQL_TRACE_WAITS               : FALSE
SQL_TRACE_BINDS               : FALSE
SQL_TRACE_PLAN_STATS          : FIRST EXEC
SESSION_EDITION_ID            : 0
CREATOR_ADDR                  : 000000021FB6DDA8
CREATOR_SERIAL#               : 1
ECID                          :
-----------------

PL/SQL procedure successfully completed.

关注上面一下字段：
PROGRAM                       : oracle@ekpdb (SMON)
TYPE                          : BACKGROUND
EVENT                         : local write wait
STATE                         : WAITING
WAIT_CLASS                    : User I/O
sid=957的会话也处于WAITING，等待状态，它的等待事件为local write wait，进程尽然为后台进程smon。等待类别为I/O等待。

这是一个比较少见的等待事件，搜了一把。【等待事件】User I/O类 等待事件（2.9）--local write wait

主要有几点：1、基本上'local write wait' 表示会话在等待自己的写操作。
                      2、I/O慢。
说到I/O慢，顺便看了下lgwr的trace，为什么看这个trace？这些信息提示，数据库的日志写出现较长的等待，超过500ms的写出会被记录，一般来说是I/O存在问题，写出缓慢所致。
检索Metalink，发现这是10.2.0.4中引入的，如果确认硬件没有问题则可以忽略之。

说明：这些TRACE记录说明LGWR在写出REDO BUFFER到REDO LOGFILE联机日志文件时单次的日志写时间超过了500ms: The above warning messages has been introduced in 10.2.0.4 patchset. The warning message will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file. Solution These messages are expected in a 10.2.0.4 database in case the log write is more than 500 ms. This is a warning which means that the write process is not as fast as it intented to be. So, probably you need to check if the disk is slow or not or for any potential OS causes. If everything looks fine at the hardware level or OS level then you can safely ignore these messages. The trace file can easily be deleted or truncated. Also, according to Bug:7559549 , these trace can be disabled by setting event 10468 level 4." 该TRACE信息由10.2.0.4 patchset引入，当LGWR写日志文件单次超过500ms时触发写"Warning: log write time "信息到lgwr trace，如果该信息频繁出现可能意为这潜在的OS IO问题(如果TOP等待事件中还有log file sync、gc buffer busy则值得注意该LGWR IO缓慢问题)， 如果在硬件或OS层面都没有问题、且该日志记录出现并不频繁则可以忽略该问题，也可以通过设置10468 EVENT LEVEL 4来禁止写出"Warning: log write time "警告信息。 实际控制这里500ms的是_long_log_write_warning_threshold(threshold for long log write warning messages in ms)隐藏参数，该参数可以在线设置： SQL> alter system set "_long_log_write_warning_threshold"=2000 ; System altered.

此处可以看到I/O确实有问题，经询问客户，此系统为虚拟机，IO确实很差。所以此处IO是个问题，但与本次hang没多大关系，因为之前一直是好的。

3、跟踪

现在的问题已经很清晰了，由于smon后台进程在等待local write wait，可能由于IO的原因。造成查询temp相关视图的时候hang。但是还是不知道为什么smon后台进程在异常等待。
了解某个进程到底在干什么？最好的方法就是跟踪，可以在数据库内部跟踪，也可以在os层面跟踪，

3.1先对smon进程做10046跟踪：

root@ekpdb tmp]# ps -ef|grep smon
root      2357 20606  0 19:00 pts/4    00:00:00 grep --color=auto smon
oracle    4749     1  0  2020 ?        00:10:30 ora_smon_ekp
[root@ekpdb tmp]# su - oracle
Last login: Wed Jan 13 18:54:46 CST 2021 on pts/4
SQL> oradebug setospid 4749
Oracle pid: 13, Unix process pid: 4749, image: oracle@ekpdb (SMON)
SQL> oradebug event 10046  trace name context  forever,level 12;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/diag/rdbms/ekp/ekp/trace/ekp_smon_4749.trc
SQL> exit

看不出来什么问题，没有具体的等待。

3.2strace 跟踪smon

strace -fr -p 4749 -o /tmp/smon

部分输出如下：

。。。。。。

4749       0.010008 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.009988 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.010036 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.010018 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.009964 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.009977 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.010020 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.009996 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)
4749       0.010001 semtimedop(98304, {{17, -1, 0}}, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable)

。。。。。。

报错Resource temporarily unavailable，一般都是limits.conf里面的配置不足。但是还是没有具体哪个资源不足，并且查看 了配置文件，资源都正常。

根据网上资料，smon确实会清理临时段了解你所不知道的SMON功能(一):清理临时段。此文的观点：smon会清理临时段，1）如果smon进程接到post，而且不忙，每3秒会清理临时段，临时段主要指的是永久表空间(permanent tablespace)上的临时段。2）当然临时表空间上的temporary segments也是由SMON来清理(cleanup)的，但这种清理仅发生在数据库实例启动时(instance startup)。有可能是smon此时需要重启来清理临时表空间中临时段。但不敢轻易重启。

4、一波未平一波又起

看起来原因明确了，但是不知道该怎么处理。于是想到重启大法，但是在重启前，可以先重建下这个临时表空间，尝试能否解决。

发现drop的时候，也会hang。至此，就是与temp表空间相关的操作都会hang。

4.1查看等待事件和阻塞源，是否和之前是一致的。

查看阻塞源：

SQL> @block

                                                          SQL_ID
USERNAME   sid:serial:os session               STATUS     SQL_CHILD_NUMBER     ID1:ID2              LMODE:QUEST          TYPE                           BLOCK      CTIME EVENT
---------- ----------------------------------- ---------- -------------------- -------------------- -------------------- ------------------------- ---------- ---------- --------------------
           Holder: 1:957:1:4749                ACTIVE     chsyr0gssbuqf:       6:1                  3||Row Exclus:NULL   Other type                         1    3713659 local write wait
SYS        Waiter: 1:655:13977:19545           ACTIVE     d0fszzwkyj2x1:0      6:1                  NULL:6||Exclusive    Other type                         0        370 enq: TS - contention
EKP        Waiter: 1:700:31207:23368           ACTIVE     aa3qxcsw95dzf:       6:1                  NULL:6||Exclusive    Other type                         0        668 enq: TS - contention

sid=655和sid=700都被sid=957阻塞，跟前面一样，957的等待事件也和前面一致，local write wait。

进一步确认957会话，使之闭环。

SQL> @sid
Enter value for inst_id: 1
Enter value for sid_in: 957
INST_ID                       : 1
SADDR                         : 000000021A4A7AC0
SID                           : 957
SERIAL#                       : 1
AUDSID                        : 0
PADDR                         : 000000021FB6DDA8
USER#                         : 0
USERNAME                      :
COMMAND                       : 0
OWNERID                       : 2147483644
TADDR                         :
LOCKWAIT                      :
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 0
SCHEMANAME                    : SYS
OSUSER                        : oracle
PROCESS                       : 4749
MACHINE                       : ekpdb
PORT                          : 0
TERMINAL                      : UNKNOWN
PROGRAM                       : oracle@ekpdb (SMON)
TYPE                          : BACKGROUND
SQL_ADDRESS                   : 00
SQL_HASH_VALUE                : 0
SQL_ID                        :
SQL_CHILD_NUMBER              :
SQL_EXEC_START                :
SQL_EXEC_ID                   :
PREV_SQL_ADDR                 : 000000021E800538
PREV_HASH_VALUE               : 4052085454
PREV_SQL_ID                   : chsyr0gssbuqf
PREV_CHILD_NUMBER             : 0
PREV_EXEC_START               : 2021-01-13 16:05:38
PREV_EXEC_ID                  : 16786696
PLSQL_ENTRY_OBJECT_ID         :
PLSQL_ENTRY_SUBPROGRAM_ID     :
PLSQL_OBJECT_ID               :
PLSQL_SUBPROGRAM_ID           :
MODULE                        :
MODULE_HASH                   : 0
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 0
ROW_WAIT_OBJ#                 : -1
ROW_WAIT_FILE#                : 202
ROW_WAIT_BLOCK#               : 11
ROW_WAIT_ROW#                 : 0
TOP_LEVEL_CALL#               : 59
LOGON_TIME                    : 2020-12-01 18:37:51
LAST_CALL_ET                  : 3712029
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       :
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : DISABLED
PQ_STATUS                     : DISABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :
BLOCKING_SESSION_STATUS       : VALID
BLOCKING_INSTANCE             : 1
BLOCKING_SESSION              : 384
FINAL_BLOCKING_SESSION_STATUS : VALID
FINAL_BLOCKING_INSTANCE       : 1
FINAL_BLOCKING_SESSION        : 384
SEQ#                          : 18895
EVENT#                        : 90
EVENT                         : local write wait
P1TEXT                        : file#
P1                            : 202
P1RAW                         : 00000000000000CA
P2TEXT                        : block#
P2                            : 11
P2RAW                         : 000000000000000B
P3TEXT                        :
P3                            : 0
P3RAW                         : 00
WAIT_CLASS_ID                 : 1740759767
WAIT_CLASS#                   : 8
WAIT_CLASS                    : User I/O
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 8
STATE                         : WAITING
WAIT_TIME_MICRO               : 7831445
TIME_REMAINING_MICRO          : -1
TIME_SINCE_LAST_WAIT_MICRO    : 0
SERVICE_NAME                  : SYS$BACKGROUND
SQL_TRACE                     : DISABLED
SQL_TRACE_WAITS               : FALSE
SQL_TRACE_BINDS               : FALSE
SQL_TRACE_PLAN_STATS          : FIRST EXEC
SESSION_EDITION_ID            : 0
CREATOR_ADDR                  : 000000021FB6DDA8
CREATOR_SERIAL#               : 1
ECID                          :
-----------------

PL/SQL procedure successfully completed.

同样，是smon后台进程可能在回收空间。

5、终极大招

重启数据库，让smon进程回收临时表空间。

先杀进程

ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs kill -9

再关闭数据库

shutdown immediate;

结果发现hang了10min，没有任何输出，查看告警日志：

############################33Wed Jan 13 18:43:22 2021
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Wed Jan 13 18:44:02 2021
Stopping background process CJQ0
Wed Jan 13 18:44:02 2021
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 740
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Wed Jan 13 18:49:07 2021
Waiting for smon to disable tx recovery.

一致卡在步骤Waiting for smon to disable tx recovery.

按照mos：  Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery (Doc ID 1076161.6) 的解释：

During shutdown the SMON process is cleaning up extents and updating the data 
dictionary tables with the marked free extents. As the extents are marked as 
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.

说白了就是数据库在shutdown immediate的时候，需要释放已经使用的临时表空间，所以需要时间。也就像前面说的，数据库关闭的时候，smon要进行空间回收。其余也没多余信息。

跟踪查看hang在哪里了？

[oracle@ekpdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 13 19:00:29 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
SQL> oradebug setospid 4749
Oracle pid: 13, Unix process pid: 4749, image: oracle@ekpdb (SMON)
SQL> oradebug event 10046  trace name context  forever,level 12;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/diag/rdbms/ekp/ekp/trace/ekp_smon_4749.trc

查看trace

。。。。。。

*** 2021-01-13 19:01:05.373
WAIT #0: nam='local write wait' ela= 90771 file#=202 block#=2 p3=0 obj#=1 tim=1610535665373195
WAIT #0: nam='local write wait' ela= 80701 file#=202 block#=9 p3=0 obj#=-1 tim=1610535665453985
WAIT #0: nam='local write wait' ela= 74494 file#=202 block#=2 p3=0 obj#=1 tim=1610535665528551
WAIT #0: nam='local write wait' ela= 87533 file#=202 block#=2 p3=0 obj#=1 tim=1610535665616167
WAIT #0: nam='local write wait' ela= 89925 file#=202 block#=9 p3=0 obj#=-1 tim=1610535665706165
WAIT #0: nam='local write wait' ela= 89872 file#=202 block#=2 p3=0 obj#=1 tim=1610535665796105
WAIT #0: nam='local write wait' ela= 89856 file#=202 block#=2 p3=0 obj#=1 tim=1610535665886039
WAIT #0: nam='local write wait' ela= 73088 file#=202 block#=9 p3=0 obj#=-1 tim=1610535665959192
WAIT #0: nam='local write wait' ela= 97904 file#=202 block#=2 p3=0 obj#=1 tim=1610535666057163
WAIT #0: nam='local write wait' ela= 103129 file#=202 block#=2 p3=0 obj#=1 tim=1610535666160375
WAIT #0: nam='local write wait' ela= 93838 file#=202 block#=9 p3=0 obj#=-1 tim=1610535666254287
WAIT #0: nam='local write wait' ela= 89428 file#=202 block#=2 p3=0 obj#=1 tim=1610535666343785

。。。。。

日志在不断的打印local write wait，跟前面一样，但是file#=202，往这个文件里面一直在写东西，所以等待。但是之前查询了数据库，只有6个数据文件，根本就没有202号文件，所以是在往一个不存在的文件里面不断写东西，所以会hang。

根据mos的建议，直接shutdown abort（心虚）；

shutdown  abort ;
startup

还好起来了。。。

6、后续操作

重建临时表空间

drop tablespace EKP_TEMP;
create temporary tablespace EKP_TEMP tempfile '/oracle/oradata/ekp_temp01.dbf' size 30G;
alter user ekp temporary tablespace EKP_TEMP;

7、测试是否正常

SQL> select  * from dba_temp_files;
FILE_NAME                              FILE_ID TABLESPACE_NAME                      BYTES     BLOCKS STATUS  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- -----------
/oracle/oradata/ekp_temp01.dbf         2 EKP_TEMP                        3.2212E+10    3932160 ONLINE             1 NO           0          0            0 3.2211E+10     3932032

SQL> select * from v$temp_space_header;

TABLESPACE_NAME                   FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
------------------------------ ---------- ---------- ----------- ---------- ----------- ------------
TEMP                                    1  931135488      113664          0           0            1
EKP_TEMP                                2    4194304         512 3.2208E+10     3931648            1

已恢复正常。秒出。

8、故障总结

由于误操作，导致resize临时数据文件了一部分，异常取消终止。导致smon进程一直在往一个不存在的文件里边写数据，导致阻塞 了所有与temp相关的操作。

后记

1）此系统的IO太差，resize 2G的时候还能有cancel的机会，我在测试的时候，执行很快，根本就没有cancel的机会。

2）shutdown abort之前要查询数据文件的scn信息，一面不一致，起不来。如下：

--v$datafile
select 
ts#,
file#,rfile#,
BYTES/1024/1024/1024 file_size_G,
status,
enabled,
CREATION_TIME,
checkpoint_change# "SCN",
last_change# "STOP_SCN",
offline_change#,
online_change#,
online_time,
name 
from v$datafile
order by 1,2;

--v$datafile_header

select 
ts#,
file#,rfile#,
bytes/1024/1024/1024 file_size_G,
TABLESPACE_NAME,
status,
ERROR,
FORMAT,
recover,
FUZZY,
CREATION_TIME CREATE_TIME,
checkpoint_change# checkpoint_change,
RESETLOGS_CHANGE# "RESETLOGS_CHANGE",
resetlogs_time
from v$datafile_header
order by 1,2;

3）查看阻塞的正常流程。

查询数据库的阻塞信息：SQL> @block

根据sid查询会话信息：SQL> @sid

4）操作要规范，可以等他resize完，继续在resize，关键操作还是不要异常操作。

5）下面记录下关于temp表空间相关的操作：

1、查看临时表空间 （dba_temp_files 视图）（v_$tempfile 视图）
select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看
2、查看临时表空间当前使用的大小和历史最大使用的大小
select 'max' asstatus,
tablespace_name,
sum(bytes_cached) / 1024 / 1024 as usage
from v$temp_extent_pool
group by tablespace_name
union all
select 'current' as status,
ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) / 1024 / 1024 asusage
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
3、查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER 视图必须在 sys
用户下才能查询）
GV_$TEMP_SPACE_HEADER 视图记录了临时表空间的使用大小与未使用的大小
dba_temp_files 视图的 bytes 字段记录的是临时表空间的总大小
SELECT temp_used.tablespace_name,
total - used as"Free",
total as"Total",
round(nvl(total -used, 0) * 100 / total, 3) "Free
percent"
FROM (SELECT tablespace_name, SUM(bytes_used) /1024 / 1024
used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name =temp_total.tablespace_name;
查看临时表空间对应的临时文件的使用情况
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
4、查找消耗资源比较多的 SQL 语句
Select se.username,
se.sid,
su.extents,
su.blocks *to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se,v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid;
5、查看当前临时表空间使用大小与正在占用临时表空间的 SQL 语句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;
6、扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 100m;
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100m;
7、创建临时表空间：
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf' size 10M;
8、更改系统的默认临时表空间：
--查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
alter database default temporary tablespace temp1;
所有用户的默认临时表空间都将切换为新的临时表空间：
select username,temporary_tablespace,default_ fromdba_users;
--更改某一用户的临时表空间：
alter user scott temporary tablespace temp;
9、删除临时表空间
删除临时表空间的一个数据文件：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop;
删除临时表空间(彻底删除)：
SQL> drop tablespace temp1 including contents and datafiles
cascade constraints;
10、缩小临时表空间大小
alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 100M;

6）当temp 临时表空间使用率过高告警的问题，不要选择直接添加数据文件或者直接 resize 30G 来消除告警。这样会导致临时文件很大占用很多磁盘空间，没有想到优化管理它，临时表空间过大只有重启实例使用率才会下降。

参考：

https://www.eygle.com/archives/2009/11/log_write_time.html

https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968071.html

 Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery (Doc ID 1076161.6)

P10ZHUO
关注
专栏目录
oracle中temp表空间,temp表空间的管理
weixin_36043375的博客
04-04 742
做完数据库的恢复后,临时表空间不可用,物理文件已经被删除,具体解决方法如下:1、查询dba_temp_files发现临时文件不可用：SQL> select file_name from dba_temp_files;未选定行2、重建临时表空间出错：SQL> create temporary tablespace temp tempfile2 'F:\oracle\oradata\...
DMDBA达梦数据库实践-表空间管理
hello_doudouzai的博客
12-23 1262
DMDBA达梦数据库实践-表空间管理一、表空间的概念二、表空间的属性三、表空间常用查询常用的两个视图常用查询四、基本表空间查看当前表空间基本表空间五、表空间分类六、基本操作七、表空间状态转换将表空间设置为脱机（offline）将表空间设置为联机（online） 一、表空间的概念 DM数据库为数据库中所有的对象分配逻辑空间，并存在于数据文件中。这DM数据库背部，所有的数据文件组合在一起被划分至一个或...
更换temp表空间时hang住
msdnchina的专栏@JiNan,ShanDong
11-08 847
更换temp表空间时hang住
Oracle数据库临时表（TEMP）空间的切换及数据文件的处理
lu200601051315的博客
11-07 3014
TEMP表空间的作用 临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当 oracle 里需要用到 sort 的时候， PGA 中 sort_area_size 大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间 , 正常来说，在完成 Select 语句、create index 等一...
Oracle临时表空间
wjzt7322的博客
11-27 404
同事反馈在JAVA中用JDBC连接Oracle数据库时返回ora-17410 错误。反映说可能是临时表空间满了原因。于是赶紧检查临时表空间相关内容。 一、查看临时表空间名称 SELECT PROPERTY_NAME, PROPERTY_VALUE,description FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLE...
oracle添加临时记录,oracle 临时表空间的增删改查
weixin_39779528的博客
04-04 566
1、查看临时表空间(dba_temp_files视图)(v_$tempfile视图)select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;select status,enabled, name, bytes/1024/1024 file_size from v_$t...
达梦数据库日常运维命令
A这货不是DBA 的博客
04-14 2257
国产数据库的未来！ DM达梦数据库日常运维命令 关键字：DM V8、数据库、常用SQL、日常运维命令 1.数据库自身信息 1.1 查询实例信息 SQL> select name inst_name from v$instance; 行号 INST_NAME 1 DMSERVER 已用时间: 11.211(毫秒). 执行号:15. 1.2 查询数据库当前状态 SQL> select status$ from v$instance; 行号 STATUS$ 1
Db2数据库中常见的堵塞问题分析与处理方法
06-25 947
Db2 数据库堵塞怎么办 作为一个数据库管理员，工作中经常会遇到的一个问题：当数据库出现故障的情况下，如何快速定位问题和找到解决方案。尤其是在运维非常重要系统的时候，解决问题恢复服务是分秒必争。Db2 作为广泛使用的商业数据库，内部提供了众多方法论和诊断工具等来协助分析问题。然而当问题真正发生的时候，数据库管理员还是会手忙脚乱，不知道从何处下手。如果着手分析的方向发生了错误，时间更是浪费严重，问题得不到及时解决，甚至有可能采取了错误的措施，导致更严重的后果。 导致数据库堵塞原因有很多，即便是现在总结，也仅仅
Oracle表空间的管理方式（LMT、DMT）--本地和字典管理
coco3600的博客
04-25 1227
Oracle表空间的管理方式（LMT、DMT）--本地和字典管理 字典管理：oracle 8i(不包括i），只存在一种表空间的管...
mysql等待事件类型_【等待事件】User I/O类 等待事件（2.9）--local write wait
weixin_42122838的博客
01-28 251
【等待事件】User I/O类 等待事件(2.9)--local write waitSELECTA.*FROMV$EVENT_NAME AWHERENAMEIN('local write wait'); 造成此等待事件的原因：1)磁盘损坏2)若执行TRUNCATE操作很慢，则可能由于表及其表上的索引的初始化值过大，可以通过SQL语句ALTER INDEX IND_BIG_TEMP REBUILD...
Oracle 查看/扩大临时表空间
Ncatkins的博客
06-02 2413
oracle数据库临时表空间扩容
oracle12c告警日志时间格式,【案例】Oracle警告 Warning:log write time 560ms, size 3KB解决办法...
weixin_33391446的博客
04-04 467
天萃荷净运维DBA在巡检Oracle数据库时发现alert日志中出现大量警告日志：Warning:log write time 560ms, size 3KB1、Oracle警告日志xunzhi_lgwr_27974.trc文件中*** 2011-09-14 09:30:04.947Warning: log write time 500ms, size 0KB*** 2011-09-14 09:3...
查看oracle临时表空间占用率的视图
ckykdwr884269的博客
06-28 484
select * from v$temp_space_header; ...
Temp表空间的分配与回收测试_20100410
04-24 291
Temp表空间的分配与回收机制测试 1 Temp表空间的分配与回收机制? 贪心法分配机制. 使用完后释放Extents,但仍表示已分配.下一个Session要用时,先从已分配的空间中,先使用,只有不够时,才又再重新申请分配新...
常见的wait等待事件及处理(zt)
cox11952的博客
01-06 435
常见的wait等待事件及处理 1、db file sequential read 调整SQL,增加buffer cache,加快磁盘读写速度 2、log file sync 减少事务提交，将redo文件存放于高速硬盘...
wait等待事件及其处理方法 awr top5 报告
04-01 447
wait等待事件及其处理方法： 1、db file sequential read 调整SQL,增加buffer cache,加快磁盘读写速度 2、log file sync 减少事务提交，将re...
oracle 主键 local,Oracle local write wait等待事件
weixin_39637363的博客
04-05 195
Note 1：TypicallyDBWR has to free up some buffers when you want to read something from the disk.During this process there are chances that you will be waiting for your local buffer(i.e blocks dirtied/i...
truncate table很慢之enq: RO - fast object reuse和local write wait等待分析
weixin_30375247的博客
10-17 409
使用ASSM表空间（默认模式）的时候，在dss系统中确实会出现truncate很慢的现象，但是他不会100%重现，得看概率。通过sql trace（对任何v$sysstat看起来资源消耗很低的情况，都可以通过sql trace找到根本原因，所以sql trace是个用来分析但是未必能够帮助解决问题的必备工具）可以看到内部时间如何消耗的。对于truncate，因为目前我们没有直接遇到过，就不分析了，...
oracle 编译包就锁表,DDL的锁，编译包经常hang住的场景
weixin_32103009的博客
04-05 216
参考ORACLE官方文档 ID122793.1共享池(SharedPool)用于缓存最近执行过的SQL语句、PL/SQL程序和数据字典信息，是对SQL语句、PL/SQL程序进行语法分析、编译、执行的区域，其的主要组件有librarycache和dictionarycache。librarycache 储存了最近的SQL/PLSQL代码的可执行模式(解析或编译版本)。X$KGLLK ##主要用来...
Oracle数据库操作：表空间与用户管理
本文主要介绍了Oracle数据库中的表空间创建与删除操作，以及用户管理的相关概念和命令。表空间是数据库的逻辑单位，每个Oracle数据库至少有一个SYSTEM表空间。创建表空间涉及CREATE TABLESPACE语句，删除则使用DROP ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值