替换临时表空间，都是很简单的操作，这个也是操作过很多次了，结果昨天客户端报临时表空间不够报错，发现还有90G空间，应该不会报错，没有办法，加了一个新的文件，问题消失，但想到这么多的空间用不上，于是打算通过增加一个临时表空间，并删除原来的临时表空间，再建回以前的临时表空间来解决，结果，删除临时表空间时，给挂起了。下面记录一下操作过程：
1.问题现象
使用DROP TABLESPACE TEMP 语句删除临时表空间后，发现长时间的没有删除完成，而且删除语句一直挂在那里
2.系统环境
ORACLE 11.2.0.4 201020 RAC+DG
LINUX 7.9
3.问题分析
1.在删除TEMP表空间前，已经增加了另外的表空间，并调整了系统的默认临时表空间，以及所有USER的默认临时表空间，这个是确定的，否则，直接删除TEMP表空间会报错。
2.查看当前系统在等待什么
SQL> select sid,event,blocking_session from v$session where wait_class<>'Idle';
SID EVENT BLOCKING_SESSION
---------- ---------------------------------------------------------------- ----------------
69 enq: TS - contention 1345
1096 gc cr request
2531 SQL*Net message to client
3685 LNS wait on SENDREQ
4296 SQL*Net more data from client
4326 SQL*Net more data from client
6 rows selected.
SQL>
3.看看1345是什么
SQL> select sid,event,program from v$session where sid=1345;
SID EVENT PROGRAM
---------- ---------------------------------------------------------------- ------------------------------------------------
1345 smon timer oracle@hisdb1 (SMON)
看来SMON在回收空间，等了1个小时，还是这样，反正是晚上，就等一下吧
第二天早上起来，心里有事，想到这个还没有删除，前端也么有人打电话，不应该呀，马上再去看，发现还是这个等待及SMON会话还在，但没有其他等待事件，看来还好。
这个时候，有前端来电话了，说有部分客户端有报失败，马上看看：
门诊费-第三方业务系统-确认缴费明确失败，银行交易批量回写状态失败ORA-00376: ???????? 203 ORA-01110: ????
203:+DATA/orcl/tempfile/temp.489.1116294481ORA-06512:?HLWYYYQWY HLWYYline 6753，调用单插件确认失败
这个看到了，ORA-00376，无法读取文件，关于这个问题，应该是这个临时文件有问题，做了删除alter tablespace temp drop tempfile 3，命令成功了，但一直数据字典中还存在。
看来这个问题必须处理。
4.搜了一下MOS，发现还真有类似的问题，具体可以参见 DDL Operations (Alter and Drop) on Temporary Tablespace Hang (Doc ID 1911167.1)
但有两种情况：
一种是BUG: 1- Bug 13028161: DROP TEMP TABLESPACE HANGS DUE TO TS ENQUEUE CONTENTION which was closed as duplicate of unpublished Bug 15913577 : MAIN_LINUX.X64 LRG9H8 RUNS WILL BE SUSPENDED 12/02/12; TOO MANY HANGS
Symptoms:
- Known to hit 11.2.0.3 but could hit any version lower that 12.1.0.2 and 12.2
- Tracing the session using 10046 shows that the session is waiting for event "enq: TS - contention'"
- System state dump shows that the session is blocked by SMON
- SMON is waiting for event "SMON TIMER"
一看，和我这里真对得上，有两个可以通过补丁 P15913577 解决，看了一下,158K,比较小,这个补丁是一个在线补丁,但考虑到现在是生产,不太合适.
另一个是:被删除的临时表空间被死连接占用：Drop temporary tablespace hangs due to dead connections leaving entries in v$session (status inactive) and in v$tempseg_usage.
- There exist few entries in the V$TEMPSEG_USAGE (formerly v$sort_usage) for this tablespace
- 10046 trace shows that the session is waiting for "Enqueue", something like:
Drop tablespace will wait on the TS enqueue in mode 6:
WAIT #1: nam='enqueue' ela= 0 p1=1414725638 p2=3 p3=1
TS "Temporary Segment (also TableSpace)"
解决办法：
-Change temporary tablespace of all the users to different temporary tablespace (If not yet done)
-Find out the all the sessions that are not active and have an entry in V$sort_usage:
SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et
FROM v$session s, v$tempseg_usage t
WHERE s.saddr = t.session_addr;
-Kill those sessions:
alter system kill session '< sid >, < Serial# >';
( Make sure that you are killing only those sessions that are not being used actually but have entries in v$session -- Use LAST_CALL_ET in last query)
-Drop the tablespace
查了一下：
SQL> r
1 SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,logon_time
2 FROM v$session s, v$tempseg_usage t
3 WHERE s.saddr = t.session_addr
4 and tablespace='TEMP'
5* order by 2,3
USERNAME SID SERIAL# CONTENTS SQL_ADDRESS EXTENTS LAST_CALL_ET TABLESPACE LOGON_TIM
---------- ---------- ---------- --------- ---------------- ---------- ------------ ------------------------------- ---------
HLWYY 457 1823 TEMPORARY 00 1 946 TEMP 28-APR-23
HLWYY 679 50801 TEMPORARY 00 1 946 TEMP 28-APR-23
HIS2XHLIS 748 55699 TEMPORARY 00 1 2415317 TEMP 26-SEP-23
XCCARD 1669 2759 TEMPORARY 00 1 57788 TEMP 15-SEP-23
XCCARD 2664 30917 TEMPORARY 00 1 0 TEMP 23-OCT-23
XCCARD 2914 7 TEMPORARY 00 1 279 TEMP 25-SEP-22
XCCARD 2946 5 TEMPORARY 000000131D855310 1 159 TEMP 25-SEP-22
IPHARMA 3203 22943 TEMPORARY 00 1 22267715 TEMP 29-JAN-23
XCCARD 3363 3 TEMPORARY 00 1 279 TEMP 25-SEP-22
HLWYY 3497 24753 TEMPORARY 00 1 234 TEMP 23-OCT-23
10 rows selected.
SQL> SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,logon_time
2 FROM v$session s, v$tempseg_usage t
3 WHERE s.saddr = t.session_addr
4 and tablespace='TEMP'
5 order by 2,3;
USERNAME SID SERIAL# CONTENTS SQL_ADDRESS EXTENTS LAST_CALL_ET TABLESPACE LOGON_TIM
---------- ---------- ---------- --------- ---------------- ---------- ------------ ------------------------------- ---------
HLWYY 485 37869 TEMPORARY 00 1 1633 TEMP 14-AUG-23
HLC 526 4535 TEMPORARY 00 1 1035083 TEMP 11-OCT-23
ICCARD 1037 27013 TEMPORARY 00 1 252263 TEMP 12-OCT-23
HLWYY 1321 7511 TEMPORARY 00 1 1633 TEMP 28-APR-23
SYS 1574 7157 TEMPORARY 0000001329EC6250 1 8 TEMP 24-AUG-23
EXAM 1674 62367 TEMPORARY 00 98441 16 TEMP 07-OCT-23
HLWYY 3564 59679 TEMPORARY 00 1 4750132 TEMP 01-AUG-23
HIS2XHLIS 3939 62315 TEMPORARY 00000012C697E7E0 1 2416400 TEMP 26-SEP-23
这个还与前面报错联系起来了。看来前面这个TEMP使用时间还真有很长的，见 LAST_CALL_ET = 22267715 ，看来是一直没有ACTIVE，至此，基本可以确定是这个死链接的问题，明确了方向，就好处理了。
4.问题处理
4.1.分别杀掉 使用 temp 表空间的会话
比如：
SQL> alter system kill session '3363,3' immediate;
4.2. 再次去删除临时表空间
DROP TABLESPACE "TEMP" ;
很快，就看到删除成功的信息：
。。。
Tue Oct 24 10:14:06 2023
Deleted Oracle managed file +DATA/orcl/tempfile/temp.505.1151020159
Deleted Oracle managed file +DATA/orcl/tempfile/temp.504.1151020125
Deleted Oracle managed file +DATA/orcl/tempfile/temp.503.1151020089
Deleted Oracle managed file +DATA/orcl/tempfile/temp.502.1151017631
Deleted Oracle managed file +DATA/orcl/tempfile/temp.489.1116294481
Deleted Oracle managed file +DATA/orcl/tempfile/temp.490.1116294487
Deleted Oracle managed file +DATA/orcl/tempfile/temp.491.1116294491
Completed: DROP TABLESPACE "TEMP" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
4.3.习惯了使用TEMP这个名字，重新创建这个TEMP
SQL> create temporary tablespace temp tempfile '+data' size 30g;
SQL> alter tablespace temp add tempfile '+data' size 30g;
SQL> alter tablespace temp add tempfile '+data' size 30g;
--修改默认临时表空间到TEMP
SQL> alter database default temporary tablespace temp;
SQL> select name,value$ from props$ where name like '%DEFAULT_TEMP%';
NAME VALUE$
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL> select 'alter user ' || username || ' temporary tablespace temp_im;' from dba_users where temporary_tablespace='TEMP_IM';
no rows selected
看来没有了，说明以前创建用户，没有明确指定默认临时表空间
再次查看有链接在使用需要删除的临时表空间，手工删除，或者等待他们自动断开
SQL> SELECT s.inst_id,s.username, sid, serial#, contents, sql_address , extents,last_call_et,tablespace,
2 to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') as logdate
3 FROM gv$session s, gv$tempseg_usage t
4 WHERE s.saddr = t.session_addr and tablespace='TEMP_IM'
5 and s.inst_id=t.inst_id
6 order by s.inst_id,logdate;
INST_ID USERNAME SID SERIAL# CONTENTS SQL_ADDRESS EXTENTS LAST_CALL_ET TABLESPACE LOGDATE
---------- ---------- ---------- ---------- --------- ---------------- ---------- ------------ ---------- -------------------
1 XCCARD 711 3509 TEMPORARY 00 1 98 TEMP_IM 2023-10-24 07:13:18
1 XCCARD 933 51791 TEMPORARY 00 1 1281 TEMP_IM 2023-10-24 07:44:14
1 XCCARD 3844 43521 TEMPORARY 00 1 1281 TEMP_IM 2023-10-24 08:20:44
1 SYS 643 24407 TEMPORARY 00 1 23 TEMP_IM 2023-10-24 08:22:53
1 BAZK 4164 61365 TEMPORARY 00 1 18 TEMP_IM 2023-10-24 08:25:59
1 XCCARD 203 36802 TEMPORARY 00 1 3 TEMP_IM 2023-10-24 10:05:30
1 XCCARD 42 58043 TEMPORARY 00 1 1 TEMP_IM 2023-10-24 10:21:19
2 XCCARD 3075 21561 TEMPORARY 00000012DB602918 1 1 TEMP_IM 2023-10-24 06:11:47
2 XCCARD 2852 33737 TEMPORARY 00000012DE4C3348 1 394 TEMP_IM 2023-10-24 06:31:03
2 HLWYY 3660 43103 TEMPORARY 00000012C7AA9528 1 2 TEMP_IM 2023-10-24 08:20:35
2 XCCARD 2574 4261 TEMPORARY 0000001319B53090 5 1 TEMP_IM 2023-10-24 08:50:59
INST_ID USERNAME SID SERIAL# CONTENTS SQL_ADDRESS EXTENTS LAST_CALL_ET TABLESPACE LOGDATE
---------- ---------- ---------- ---------- --------- ---------------- ---------- ------------ ---------- -------------------
2 XCCARD 1730 28073 TEMPORARY 00 1 1 TEMP_IM 2023-10-24 09:05:52
2 BAZK 1537 13581 TEMPORARY 00 1 18 TEMP_IM 2023-10-24 09:39:24
2 XCCARD 1643 47257 TEMPORARY 00 4 0 TEMP_IM 2023-10-24 09:48:04
2 XCCARD 4235 25203 TEMPORARY 00 1 20 TEMP_IM 2023-10-24 09:48:50
2 XCCARD 2825 42785 TEMPORARY 00 3 0 TEMP_IM 2023-10-24 09:56:42
2 EXAM 1198 56595 TEMPORARY 00 178 1 TEMP_IM 2023-10-24 10:09:15
2 XCCARD 4008 52915 TEMPORARY 00000012F1894CD0 1 1 TEMP_IM 2023-10-24 10:21:17
2 XCCARD 4131 3601 TEMPORARY 00 1 2 TEMP_IM 2023-10-24 10:21:18
2 XCCARD 4232 58623 TEMPORARY 00 2 3 TEMP_IM 2023-10-24 10:21:18
2 XCCARD 4551 27835 TEMPORARY 00 1 2 TEMP_IM 2023-10-24 10:21:19
2 XCCARD 4390 24909 TEMPORARY 00000012DE7A8CA8 1 284 TEMP_IM 2023-10-24 10:21:19
INST_ID USERNAME SID SERIAL# CONTENTS SQL_ADDRESS EXTENTS LAST_CALL_ET TABLESPACE LOGDATE
---------- ---------- ---------- ---------- --------- ---------------- ---------- ------------ ---------- -------------------
2 XCCARD 4487 16655 TEMPORARY 00 2 2 TEMP_IM 2023-10-24 10:21:19
2 XCCARD 2149 5855 TEMPORARY 00 1 2 TEMP_IM 2023-10-24 10:28:49
24 rows selected.
等处理后，删除即可
DROP TABLESPACE TEMP_IM ;
4.4.处理DG备库TEMP 表空间没有临时文件的问题
--dg
。。。
Tue Oct 24 09:04:20 2023
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_ora_52297.trc:
ORA-25153: Temporary Tablespace is Empty
。。。
--确认一下：
SYS@orclDG> select name from v$tempfile;
no rows selected
SYS@orclDG>
SYS@orclDG> alter tablespace temp add tempfile size 30g;
SYS@orclDG> alter tablespace temp_im add tempfile size 30g;
SYS@orcl> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/hisdg/oradata/ORCLDG/datafile/o1_mf_temp_lmgdc4dj_.tmp
/hisdg/oradata/ORCLDG/datafile/o1_mf_temp_im_lmgdcp6l_.tmp
SYS@orcl>
不再报错。
|
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2023-10-24 12:30:37
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对