共计 11363 个字符，预计需要花费 29 分钟才能阅读完成。
故障现象为某省结算库应用方在跑存储过程发现报错
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
后进行了下面的一系列的排查分析：
查看 undo 表空间使用率为 100%，查看 alert 日志中发现了大量的事物已经 ORA-01555 的报错。
Wed Jan 30 04:32:01 GMT+08:00 2013ORA-01555 caused by SQL statement below (SQL ID: 4ds6qq0mfac2t, Query Duration=5 sec, SCN: 0x0c00.d915c977):
Wed Jan 30 04:32:01 GMT+08:00 2013SELECT distinct FILE_NAME,to_char(file_name_time,:”SYS_B_00″),A.operation_type_grade FROM T_FILE_INFO_81 A,T_FILE_CLASS B ,T_FILE_STAT
E C WHERE ((A.RAT_FIRST_STARTTIME >=:”SYS_B_01″ and A.RAT_FIRST_STARTTIME <=:”SYS_B_02″)
or (A.RAT_LAST_ENDTIME >=:”SYS_B_03″ and A.RAT_LAST_ENDTIME <=:”SYS_B_04″)
or (A.RAT_FIRST_STARTTIME <=:”SYS_B_05″ and A.RAT_LAST_ENDTIME >= :”SYS_B_06″) )
and ((A.RAT_FIRST_CALLING_NBR >=:”SYS_B_07″ and A.RAT_FIRST_CALLING_NBR <=:”SYS_B_08″)
or (A.RAT_LAST_CALLING_NBR >= :”SYS_B_09″ and A.RAT_LAST_CALLING_NBR <= :”SYS_B_10″)
or (A.RAT_FIRST_CALLING_NBR <=:”SYS_B_11″ and A.RAT_LAST_CALLING_NBR >=:”SYS_B_12″))
and A.FILE_NAME <>:”SYS_B_13″ and A.File_Class_Id = B.File_Class_Id and B.operati
on_type_id=:”SYS_B_14″ and (A.FILE_NAME_TIME + interval :”SYS_B_15″ day) > TO_DATE(:”SYS_B_16″,:”SYS_B_17″)
and A.city_id =:”SYS_B_18″ and a.state in (:”SYS
Wed Jan 30 08:49:12 GMT+08:00 2013insert into STL_GX.T_all_file_TOTAL
select aa.province_id,
dd.name,
aa.BILL_DATE,
aa.OPERATION_TYPE_GRADE,
:”SYS_B_00″,
aa.cj_files,
bb.pj_files,
bb.org_counts,
bb.rate_counts,
bb.inp_counts,
bb.inpc_counts,
cc.jf_files,
cc.jf_counts,
:”SYS_B_01″,
:”SYS_B_02″,
aa.is_rate,
aa.is_billtag,
aa.is_insert,
bb.ERR_COUNTS
from (SELECT a.province_code as province_id,
substr(A.ORG_FILENAME, :”SYS_B_03″, :”SYS_B_04″) as BILL_DATE,
b.operation_type_grade,
COUNT(*) as cj_files,
b.is_rate as is_rate,
b.is_billtag as is_billtag,
b.is_insert as is_insert
FROM STL_PARA.T_LOG_COLLECT_76@pub_PARA A,
stl_gx.tmp_cj_info b
WHERE substr(A.ORG_FILENAME, :”SYS_B_05″, :”SYS_B_06″) = :”SYS_B_07″
and b.province_id = :”S
Wed Jan 30 09:44:56 GMT+08:00 2013Thread 1 advanced to log sequence 149384 (LGWR switch)
Current log# 6 seq# 149384 mem# 0: /dev/rjs_redolog06
Wed Jan 30 09:50:00 GMT+08:00 2013ORA-01555 caused by SQL statement below (SQL ID: 7t0bjnwxt9ufv, Query Duration=180 sec, SCN: 0x0c00.e294a117):
这个很明显，是因为 undo 中存在大量的 insert 操作，导致数据库 undo 没有 commit，由于本库的实际环境，之前做过 undo_retention 的调整。下面看此设定值。
SQL> show parameter undo
NAME TYPE VALUE
———————————— ——————————– ——————————
undo_management string AUTO
undo_retention integer 0
undo_tablespace string UNDOTBS1
undo_management 为 auto、retention 时间为 0 让 Oracle 自动调整保留提交后 undo 信息的时间。Oracle 10g 之前, 在自动 Undo 管理的模式下，我们都知道 undo_retention 参数的作用是用来控制当 transaction 被 commit 之后，undo 信息的保留时间。这些 undo 信息可以用来构造 consistent read 以及用于一系列的闪回恢复，而且足够的 undo 信息还可以减少 ORA-01555 错误的发生，在 Oracle 9R1 中呢，这个 value 的默认值是 900 秒，Oracle 9R2 以后这个 value 提高到了 10800 秒。即使我们设置了 undo_retention 这个参数，那么在默认情况下，这是一个 noguarantee 的限制。也就是说我将 undo_retention=10800, 那么原本以为在一个 transaction commit 之后，之前的 undo 还可以保存 10800 秒，才可以被别的 transaction DML 覆盖，孰不知当有其他的 transaction DML 处理过程中需要 undo 空间的时候，恰恰这个时候 not enough space for undo，也就说我并没有允许 undo tablespace 自动扩展。由于我们的 retention 是 noguarantee 的，所以 transaction DML 就会忽略这种 retention 的时间限制直接回绕覆盖我们的 undo 信息，这种结果下其实在很多情况下是不希望得到的。
Oracle 10g 之后，oracle 提出了一个特性就是 undo 的 guarantee，可以强制 oracle 来 guarantee 的 undo 信息，也就说如果一个 session 的 transaction DML 需要 undo 空间的时候，即使 undo 的空间不足，这个 session 也不会强制覆盖由 undo_retention 所保护的 undo 信息，那么这个 transaction DML 会因为 undo 空间的不足会而 report 一个 error 并自动退出。
SQL> select tablespace_name,block_size,extent_management
2 segment_space_management,contents,retention
3 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE SEGMENT_SP CONTENTS RETENTION
—————————— ———- ———- ——— ———–
SYSTEM 8192 LOCAL PERMANENT NOT APPLY
UNDOTBS1 8192 LOCAL UNDO NOGUARANTEE
SYSAUX 8192 LOCAL PERMANENT NOT APPLY
TEMP 8192 LOCAL TEMPORARY NOT APPLY
USERS 8192 LOCAL PERMANENT NOT APPLY
COMMDATA 8192 LOCAL PERMANENT NOT APPLY
SETTLEINDEX 8192 LOCAL PERMANENT NOT APPLY
SETTLEDATA 8192 LOCAL PERMANENT NOT APPLY
STATDATA 8192 LOCAL PERMANENT NOT APPLY
STATINDEX 8192 LOCAL PERMANENT NOT APPLY
COMMINDEX 8192 LOCAL PERMANENT NOT APPLY
TABLESPACE_NAME BLOCK_SIZE SEGMENT_SP CONTENTS RETENTION
—————————— ———- ———- ——— ———–
RMAN_TBS 8192 LOCAL PERMANENT NOT APPLY
12 rows selected.
之后想的既然资源无法 commit 是否可以重启数据库达到资源释放，所以 15 点 20 开始重启数据库，打算重新找一个数据文件，然后重新创建一个 undo 表空间，将 undotbs1 切换到 undotbs2 并把 tbs1 offline，drop 后，在切回到 tbs1 上面进行资源释放。
create undo tablespace UNDOTBS2 datafile ‘/dev/untb03.dbf’ size 32700M
alter system set undo_tablespace=UNDOTBS2 scope=both;
将原来的 UNDO 表空间, 置为脱机:
alter tablespace UNDOTBS1 offline;
删除原来的 UNDO 表空间:
drop tablespace UNDOTBS1 including contents AND DATAFILES CASCADE CONSTRAINTS ;
Wed Jan 30 15:33:39 GMT+08:00 2013ALTER DATABASE OPEN
重启发现，undo 的利用率还是 100%、也就是说 undo_retention= 0 没有生效
Wed Jan 30 17:27:06 GMT+08:00 2013ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH;
设定 retention 时间为 15 分钟，那么看看数据库中 undo active 使用率居然高达 62GB（总共 undo 表空间为 64GB）
发现此刻数据库中存在两个死事物
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL=’DEAD’;
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
—————- ———- ———- ———- ———-
00000001108C63F0 75 27 2368514 795545
00000001108C5D10 644 7 57597 0
由于已经报 Oracle ACS 服务，oracle 工程师到来后，原 75-27 死事物已经不存在（18：35 左右应用方停止了相关应用）
再次查看数据库中 UNEXPIRED 利用率 63GB、ACTIVE 利用率 1GB。
那么应该是死事物得到了释放，再次查看
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL=’DEAD’;
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
—————- ———- ———- ———- ———-
0000000110845CF0 644 7 57597 0
那么绝对就是 75-27 得到了释放，查看现有的交易
SQL> alter session set nls_date_format=’mm/dd/yy hh24:mi:ss’;
Session altered.
SQL> select begin_time,end_time,UNXPSTEALCNT from v$undostat;
BEGIN_TIME END_TIME UNXPSTEALCNT
—————– —————– ————
01/30/13 20:53:28 01/31/13 11:09:42 71794
01/30/13 20:43:28 01/30/13 20:53:28 110035
01/30/13 20:33:28 01/30/13 20:43:28 15240
01/30/13 20:23:28 01/30/13 20:33:28 25489
01/30/13 20:13:28 01/30/13 20:23:28 11936
01/30/13 20:03:28 01/30/13 20:13:28 2950
01/30/13 19:53:28 01/30/13 20:03:28 707
01/30/13 19:43:28 01/30/13 19:53:28 0
01/30/13 19:33:28 01/30/13 19:43:28 0
01/30/13 19:23:28 01/30/13 19:33:28 1271
01/30/13 19:13:28 01/30/13 19:23:28 29187
BEGIN_TIME END_TIME UNXPSTEALCNT
—————– —————– ————
01/30/13 19:03:28 01/30/13 19:13:28 19976
01/30/13 18:53:28 01/30/13 19:03:28 1365
01/30/13 18:43:28 01/30/13 18:53:28 6235
01/30/13 18:33:28 01/30/13 18:43:28 24651
01/30/13 18:23:28 01/30/13 18:33:28 38220
01/30/13 18:13:28 01/30/13 18:23:28 49888
01/30/13 18:03:28 01/30/13 18:13:28 29815
01/30/13 17:53:28 01/30/13 18:03:28 43678
01/30/13 17:43:28 01/30/13 17:53:28 104834
01/30/13 17:33:28 01/30/13 17:43:28 101518
01/30/13 17:23:28 01/30/13 17:33:28 45838
BEGIN_TIME END_TIME UNXPSTEALCNT
—————– —————– ————
01/30/13 17:13:28 01/30/13 17:23:28 30964
01/30/13 17:03:28 01/30/13 17:13:28 43876
01/30/13 16:53:28 01/30/13 17:03:28 15455
01/30/13 16:43:28 01/30/13 16:53:28 7839
01/30/13 16:33:28 01/30/13 16:43:28 24606
01/30/13 16:23:28 01/30/13 16:33:28 40497
01/30/13 16:13:28 01/30/13 16:23:28 34759
01/30/13 16:03:28 01/30/13 16:13:28 118142
01/30/13 15:53:28 01/30/13 16:03:28 107958
01/30/13 15:43:28 01/30/13 15:53:28 20249
01/30/13 15:33:28 01/30/13 15:43:28 0
33 rows selected.
把疑问交给 ORACLE 工程师了，为什么 undo_retention 设定了 900s 未即时生效？
通过查看 metalink 得知一个 bug 问题 ps：Bug 5387030 – Automatic tuning of undo_retention causes unusual extra space allocation [ID 5387030.8]
Product (Component)
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions >= 10.2.0.1 but BELOW 11.1
Versions confirmed as being affected
10.2.0.3
10.2.0.2
10.2.0.1
Description
When undo tablespace is using NON-AUTOEXTEND datafiles,
V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing
undo block from being expired and reused. In extreme cases the undo
tablespace could be filled to capacity by these unexpired blocks.
An alert may be posted on DBA_ALERT_HISTORY that advises to increase
the space when it is not really necessary if this fix is applied.
If the user sets their own alert thresholds for undo tablespaces the
bug may prevent alerts from being produced.
Workaround
alter system set “_smu_debug_mode” = 33554432;
This causes the v$undostat.tuned_undoretention to be calculated as
the maximum of:
maxquerylen secs + 300
undo_retention specified in init.ora
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References
Bug:5387030 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article
我们的数据库版本为 10.2.0.5.0 满足其 Versions >= 10.2.0.1 but BELOW 11.1 的条件。对于固定 UNDO 表空间，将会通过表空间的剩余空间来最大限度保留 UNDO 信息。如果 FIXED UNDO 表空间没有对保留时间作 GUARANTEE（alter tablespace xxx retention guarantee;），则 undo_retention 参数将不会起作用。（警告：如果设置 UNDO 表空间为 retention guarantee，则未过期的数据不会被复写，如果表空间不够则会导致 DML 操作失败或者 transation 挂起）
oracle 10g 有自动 Automatic Undo Retention Tuning 这个特性。设置的 undo_retention 参数只是一个指导值,，Oracle 会自动调整 Undo (会跨过 undo_retention 设定的时间) 来保证不会出现 Ora-1555 错误.。通过查询 V$UNDOSTAT（该视图记录 4 天以内的 UNDO 表空间使用情况，超过 4 天可以查询 DBA_HIST_UNDOSTAT 视图）的 tuned_undoretention（该字段在 10G 版本才有，9I 是没有的）字段可以得到 Oracle 根据事务量（如果是文件不可扩展，则会考虑剩余空间）采样后的自动计算出最佳的 retenton 时间。这样对于一个事务量分布不均匀的 数据库 来说,，就会引发潜在的问题 – 在批处理的时候可能 Undo 会用光，而且这个状态将一直持续，不会释放。
如何取消 10g 的 auto UNDO Retention Tuning，有如下三种方法：
PS：Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1]：Automatic Tuning of Undo_retention Causes Space Problems
1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend:
SQL> alter database datafile ‘<datafile_flename>’
autoextend on maxsize <current_size>;
With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.
2.) Set the following hidden parameter in init.ora file:
_smu_debug_mode=33554432
or
SQL> Alter system set “_smu_debug_mode” = 33554432;
With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.
3.) Set the following hidden parameter in init.ora:
_undo_autotune = false
or
SQL> Alter system set “_undo_autotune” = false; 可动态调整无需重启数据库。
Autotune of undo retention is turned off.
Wed Jan 30 20:59:26 GMT+08:00 2013ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=BOTH;
SQL> show parameter undo
NAME TYPE VALUE
——————- —————– ———-
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
在设定完_undo_autotune 后，并且结果应用方跑存储过程，未发现 ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’ 错误（此错误并没有在 alert 日志中体现。）
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134488.htm
