Know more about Temporary Tablespaces ORA-1652

原创 Anbob 2018-01-18

766

众所周知, oracle临时表空间是用于存储数据库的排序数据和全局临时表数据，tempfile不同于datafile因为不存在控制文件中，在恢复数据库后需要重建tempfile,也可以在standby环境中配置不同的temp表空间。用监控数据表空间间的脚本也许看到临时表空间使用率100%，但并且有时并非真实，DBA_FREE_SPACE 不会记录临时表空间的可用空间，使用v$sort_segments,V$TEMP_SPACE_HEADER和v$tempseg_usage监控临时表空间的使用，如果临时表空间不足会提示ORA-1652错误。对于临时表空间组特性是没有用的，为不同的用户分配不同的temp tablespace并且以用户分配到不同的实例可以减少temp空间争用。
临时表空间是被分成不同的extent,在11g r2中每个extent为1MB，extent maps是cache在本地的SGA中，并在再重启时不会保存extent map所以在实例重启后可以完全释放临时表空间的扩展，临时表空间(cache extent map)是按需扩展的，用完后会标记为free但不会回收(如果不重启)，可以使用v$tempseg_usege查看当前实际使用。本实例cache的extent即使为free,对于其他实例也不可以立即使用，另一个实例需要申请新的uncache的extent, free extent只重用于本实例请求。当一个实例需要更多的extent时可以从另一个实例偷窃，另一个实例会uncache extent map，可以从gv$temp_extent_pool中观察,在11g中为一次请求100 extents,当本实例窃取够了足够的extent就可以继续使用temp extent，临时段的清理或合并是有每5分钟smon进程完成的。

SQL> select * from gv$temp_extent_pool;
   INST_ID TABLESPACE_NAME      FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
---------- ----------------- ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
         1 TEMP                       1          11916            8       1525248        1024   1.2495E+10    8388608            1
         1 TEMP                       3           2884            1        369152         128   3024093184    1048576            3
         1 TEMP                       4           5990            3        766720         384   6280970240    3145728            4
         1 TEMP                       5           9422            1       1206016         128   9879683072    1048576            5
         1 TEMP                       6          10659            3       1364352         384   1.1177E+10    3145728            6
         2 TEMP                       1          18347            0       2348416           0   1.9238E+10          0            1
         2 TEMP                       3          12474            2       1596672         256   1.3080E+10    2097152            3
         2 TEMP                       4           9369            0       1199232           0   9824108544          0            4
         2 TEMP                       5           5937            3        759936         384   6225395712    3145728            5
复制

近期有一套数据库出现了ORA-1652,extent map都已cache，显示使用率为100%，但当前真实使用的并不多，重启数据库不现实，我想通过drop tempfile再填加的方式释放已扩展的temp空间，结果发现了一个有意思的事情。
1, Drop Tempfile

SQL> @df
TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
...
SYSAUX                              24318      17012       7306    70% NO  |##############      |
SYSTEM                              20478       2674      17804    14% NO  |###                 |
TEMP                               107517     107474         43   100% YES |####################|
UNDOTBS1                            61438      35678      25760    59% NO  |############        |
UNDOTBS2                            61438      13743      47695    23% NO  |#####               |
USERS                              330226     283772      46454    86% NO  |##################  |
SQL> @ls temp
TABLESPACE_NAME                   FILE_ID FILE_NAME                                EXT         MB      MAXSZ
------------------------------ ---------- ---------------------------------------- --- ---------- ----------
TEMP                                    3 /dev/yyc_oravg02/ryyc_lv15_128           NO       15359
TEMP                                    1 /dev/yyc_oravg02/ryyc_temp               YES      30719   32767.98
TEMP                                    2 /dev/yyc_oravg03/ryyc_lv15_240
TEMP                                    4 /dev/yyc_oravg04/ryyc_lv15_281           NO    15359.99
TEMP                                    5 /dev/yyc_oravg04/ryyc_lv15_282           NO    15359.99
TEMP                                    6 /dev/yyc_oravg04/ryyc_lv15_283           NO    15359.99
TEMP                                    7 /dev/yyc_oravg04/ryyc_lv15_284           NO    15359.99
SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_284';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_283';
Tablespace altered.
SQL> alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284';
alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284'
*
ERROR at line 1:
ORA-01537: cannot add file '/dev/yyc_oravg04/ryyc_lv15_284' - file already part of database
SQL> @ls temp
TABLESPACE_NAME                   FILE_ID FILE_NAME                                                                        EXT         MB      MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
TEMP                                    3 /dev/yyc_oravg02/ryyc_lv15_128                                                   NO       15359
TEMP                                    1 /dev/yyc_oravg02/ryyc_temp                                                       YES      30719   32767.98
TEMP                                    2 /dev/yyc_oravg03/ryyc_lv15_240
TEMP                                    4 /dev/yyc_oravg04/ryyc_lv15_281                                                   NO    15359.99
TEMP                                    5 /dev/yyc_oravg04/ryyc_lv15_282                                                   NO    15359.99
TEMP                                    6 /dev/yyc_oravg04/ryyc_lv15_283
TEMP                                    7 /dev/yyc_oravg04/ryyc_lv15_284
SQL> @df
TABLESPACE_NAME                   TotalMB     UsedMB     FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
...
SYSAUX                              24318      17014       7304    70% NO  |##############      |
SYSTEM                              20478       2674      17804    14% NO  |###                 |
TEMP                                76797      76754         43   100% YES |####################|
UNDOTBS1                            61438      35678      25760    59% NO  |############        |
UNDOTBS2                            61438      13726      47712    23% NO  |#####               |
USERS                              330226     283772      46454    86% NO  |##################  |
复制

NOTE:
使用alter tablespace无法drop tempfile(从数据库释放tempfile),但是可用空间被收回。正确的方法是使用alter database方式如下：

SQL> alter database tempfile '/dev/yyc_oravg03/ryyc_lv15_240'  drop including datafiles;
Database altered.
SQL> @ls temp
TABLESPACE_NAME                   FILE_ID FILE_NAME                                                                        EXT         MB      MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
TEMP                                    3 /dev/yyc_oravg02/ryyc_lv15_128                                                   NO       15359
TEMP                                    1 /dev/yyc_oravg02/ryyc_temp                                                       YES      30719   32767.98
TEMP                                    4 /dev/yyc_oravg04/ryyc_lv15_281                                                   NO    15359.99
TEMP                                    5 /dev/yyc_oravg04/ryyc_lv15_282                                                   NO    15359.99
TEMP                                    6 /dev/yyc_oravg04/ryyc_lv15_283
TEMP                                    7 /dev/yyc_oravg04/ryyc_lv15_284
复制

Note:
drop tempfile或drop temporary tablespace前提是当前无会话使用该文件和表空间，如果删除了所有的tempfile,在使用时会提示ora-25153错误，如果有会话使用该tempfile将报ORA-25152错误。

SQL> alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284'  drop including datafiles;
alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284'  drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,segfile#-p.value rfile 
from gv$tempseg_usage u,v$parameter p,gv$session s 
where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
   INST_ID        SID STATUS   TABLESPACE                      SQL_ID            SEGFILE#      RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ----------
         1      17813 INACTIVE TEMP                            cbpmfx12jzj4r         2025          1
         1      17530 INACTIVE TEMP                            64tcqftuuwaan         2028          4
         1      17285 INACTIVE TEMP                            cbpmfx12jzj4r         2025          1
...
         2       4814 INACTIVE TEMP                            cbpmfx12jzj4r         2025          1
         2       4806 INACTIVE TEMP                            6h214g82rxb7x         2027          3
         1       4319 INACTIVE TEMP                            64tcqftuuwaan         2030          6
         1       1776 INACTIVE TEMP                            a6kb0pf8x0hqc         2029          5
         2         51 INACTIVE TEMP                            cbpmfx12jzj4r         2025          1
         1        285 INACTIVE TEMP                            6h214g82rxb7x         2031          7
         1          4 INACTIVE TEMP                            a6kb0pf8x0hqc         2028          4

SQL> select inst_id,tablespace,segtype,sum(blocks),count(*),sum(blocks)/count(*) from gv$tempseg_usage group by inst_id,tablespace,segtype;
   INST_ID TABLESPACE                      SEGTYPE   SUM(BLOCKS)   COUNT(*) SUM(BLOCKS)/COUNT(*)
---------- ------------------------------- --------- ----------- ---------- --------------------
         1 TEMP                            LOB_DATA         2176         17                  128
         2 TEMP                            LOB_DATA         1536         12                  128
复制

Note:
上面持用tempfile rfile #7的会话是connect pool的长连接，在当前的数据库版本11.2.0.3(其实在9i-12c的版本都存在)，持有的temp直到该session退出才会释放。如删除本案例中的tempfile rfile 7#,Rfile# 7的tempfile正在被实例1的sid=285的会话使用，kill 掉实例1的285会话即可删除7# tempfile。
并且当前2个实例上一共29个会话，占用的都是temp类型都是temp lob data.每个session占用1M(128*8k). 注意该视图中的SQL_ID并不是产生temp seg的SQL，而是该会话最后一次执行的SQL, 在12C版本中v$tempseg_usage新增加列"SQL_ID_TEMPSEG"可以确认产生temp segment的SQLID.如下：
-- 12.2

SQL> select username,session_num,sql_id,tablespace,CONTENTS,SEGTYPE,SEGRFNO#,BLOCKS,SQL_ID_TEMPSEG from v$tempseg_usage;
USERNAME SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE SEGRFNO# BLOCKS SQL_ID_TEMPSE
-------------------- ----------- ------------- ------------------------------ --------- --------- ---------- ---------- -------------
18783 g44b5rqw7xwq7 TEMP TEMPORARY LOB_DATA 1 128 0w26sk6t6gq98
SQL> select sql_text from v$sqlarea where sql_id='0w26sk6t6gq98';
SQL_TEXT
--------------------------
SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1 )) FROM DUAL
复制

Note: SQL_ID_TEMPSEG 是产生temp的SQL，XML 也是LOB一种形式。

According to Bug 5723140,since 9i onwards we have been using only one temp segment for temp lobs in a session and all these temp lobs share the same temp segment. When there is a request to cleanup those temp lobs we do free space from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions. The actual cleanup happens only on session exit thru the callback that was registered for this purpose. Hence multiple sessions using temp lobs will have these temp segments active though unused, thus leading to temp segment growth.

正如note中描述的从9i以后，session使用一个临时段存在所有temp lobs,但是用完后free空间，同一会话可以reuse, 临时段只有到exit时才会释放，否则一直占用。如果session 使用了connect pool的长连接，并且会话数增长，temp segment会逐渐增长，不会让其它会话使用。从10.2.0.4提供了一个event 60025,当系统级或会话级启用了该event,会在session中的temp lob不活动时(cache temp和nocache temp lobs都为0时)，即使不退出会话也会自己释放空间供其它会话使用。如果启用了60025 event像下面这引起temp lob 会被释放掉:

SQL> select * from gv$temporary_lobs;
   INST_ID        SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
         2       4806          0            0             0
         2       5159          0            0             0
         2       6843          0            0             0
         2       7975          0            0             0
         2       8192          0            0             0
         2      10728          0            0             0
         1          4          0            0             0
         1       1776          0            0             0
         1       3460          0            0             0
         1       4319          0            0             0
         1       4799          0            0             0
         1       4887          0            0             0
         1       8220          0            0             0
         1       8544          0            0             0
         1      10478          0            0             0
         1      11031          0            0             0
         1      11110          0            0             0
         1      11369          0            0             0
         1      12983          0            0             0
         1      14120          0            0             0
         1      17285          0            0             0
         1      17530          0            0             0
         1      17813          0            0             0
         1      17872          0            0             0
复制

60025 event不可以动态在系统级修改生效，在spfile级修改重启生效；session级启用60025后，也不会立即释放之前的temp lobs.下面做个测试:

[oracle@weejar1:/home/oracle] ora
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 17 14:27:24 2018
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  anbob1               weejar1                    17872 50597    11.2.0.3.0 20171114 19239           3327  19235           C0000016152D86D8 C000001652FD99F0
-ne 
SQL> declare
  2  b blob;
  3  begin
  4  dbms_lob.CREATETEMPORARY(b,true);
  5  dbms_lob.freeTEMPORARY(b);
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
   INST_ID        SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
...
         1      14120          0            0             0
         1      17285          0            0             0
         1      17530          0            0             0
         1      17813          0            0             0
         1      17872          0            0             0 
24 rows selected.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
   INST_ID        SID STATUS   TABLESPACE                      SQL_ID            SEGFILE#     BLOCKS      RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
         1      17872 INACTIVE TEMP1                           g0wf0fkkqwaya         2033        128          9
         1      17813 INACTIVE TEMP                            cbpmfx12jzj4r         2025        128          1
...
SQL> alter session set events '60025 trace name context forever';
Session altered.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
   INST_ID        SID STATUS   TABLESPACE                      SQL_ID            SEGFILE#     BLOCKS      RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
         1      17872 INACTIVE TEMP1                           g0wf0fkkqwaya         2033        128          9
         1      17813 INACTIVE TEMP                            cbpmfx12jzj4r         2025        128          1
...
SQL> declare
  2  b blob;
  3  begin
  4  dbms_lob.CREATETEMPORARY(b,true);
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
-- sid 17872 released
复制

Note:
因为temp seg同会话可以reuse, 再配置了60025 event后，再次执行lob操作后， temp lob自动清理。
系统级修改

alter system set event='60025 trace name context forever' scope=spfile;
复制

对于已存在的会话可以使用oradebug 启用指定sid

oradebug setorapid <oracle pid>
oradebug event 60025 trace name context forever
-- wait the session next call lob to clean temp lob by itself
复制

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Know more about Temporary Tablespaces ORA-1652

评论

相关阅读