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

目录

故障分析

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)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值