Oracle中如何解决ORA-01555错误

这篇文章给大家分享的是有关Oracle中如何解决ORA-01555错误的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

11.6 ORA-01555: 快照太旧

什么是ORA-01555错误?

ORA-01555是Oracle数据库运行过程中常见的一个错误。以下就是Oracle关于ORA-01555错误的经典描述：

ORA-01555: snapshot too old (rollback segment too small)

Cause: rollback records needed by a reader for consistent read are

overwritten by other writers

简单而言，就是为保证一致性读的回退段数据被其它写进程所覆盖了。以下就是一个典型例子：

• 时间点1：会话1对 A表开始进行查询操作。

• 时间点2：会话2修改 A表的记录X。

• 时间点3：当会话1的查询语句查询到记录X时，通过SCN号发现记录X被修改了，而且修改的时间（时间点2）晚于时间点1。这样，Oracle将通过保存在UNDO中记录X修改前的数据（简称Before Image），来进行一致性读取。

• 时间点4：会话2修改 A表的记录Y，并且进行了commit操作。这样，该事务的slot数据就可以被Oracle覆盖了。

• 时间点5：会话2修改 A表的记录Z，并且进行了commit操作。此时，由于UNDO表空间的空间不足，记录Y的修改前的数据被Oracle覆盖了。

• 时间点6：当会话1的查询语句查询到记录Y时，通过SCN号发现记录Y被修改了，而且修改的时间（时间点6）晚于时间点1。于是，Oracle将通过保存在UNDO中记录Y修改前的数据（简称Before Image），来进行一致性读取。但此时记录Y修改前的数据已经在时间点5被覆盖了。因此，系统将报ORA-01555错误！

ORA-01555错误的原因和解决方案比较复杂，Oracle有关该错误处理的文章也比较多。本书我们一方面主要针对9i之后的自动UNDO管理技术（Automatic UNDO Management），另一方面也只针对普通表的ORA-01555错误处理，而不关注LOB等特殊对象的ORA-01555错误处理。

如何获取ORA-01555错误相关信息？

首先，分别从应用会话窗口和alert.log中分别获取相关信息。

例如，应用会话窗口显示错误信息：

ORA-01555: snapshot too old: rollback segment number 9 with name “_SYSSMU1$” too small

Alert.log中显示：

ORA-01555 caused by SQL statement below (Query Duration=9999 sec, SCN:0x000.008a7c2d)

其次，通过alert.log确定QUERY DURATION。上例中为9999秒。

第三，从应用会话信息中确定undo segment名称。例如：_SYSSMU1$。

最后，确定UNDO表空间的UNDO_RETENTION值。

SQL> show parameter undo_retention

如何解决ORA-01555错误？

1. 如果QUERY DURATION > UNDO_RETENTION

此时，Oracle无法保证当提交的事务过期，也就是超过UNDO_RETENTION时间之后，还能确保数据的一致性读取。

这种情况下，最有效的解决办法是优化查询语句，降低语句的QUERY DURATION时间。如果无法优化了，则只能参考QUERY DURATION时间值来扩大UNDO_RETENTION值，确保Oracle保存更长时间的UNDO信息。

扩大UNDO_RETENTION值，意味着需要更多的UNDO表空间，下面还将介绍UNDO表空间的计算方法。

2. 如果QUERY DURATION <= UNDO_RETENTION

在这种情况下，通常而言是UNDO表空间满了。如何进一步确定UNDO表空间是否满了呢？执行如下脚本：

set pagesize 25
set linesize 120

select inst_id,
to_char(begin_time,’MM/DD/YYYY HH24:MI’) begin_time,
UNXPSTEALCNT “# Unexpired|Stolen”,
EXPSTEALCNT “# Expired|Reused”,
SSOLDERRCNT “ORA-1555|Error”,
NOSPACEERRCNT “Out-Of-space|Error”,
MAXQUERYLEN “Max Query|Length”
from gv$undostat
where begin_time between
to_date(‘<start time of the ORA-1555 query>’,’MM/DD/YYYY HH24:MI:SS’)
and
to_date(‘<time when ORA-1555 occured>’,’MM/DD/YYYY HH24:MI:SS’)
order by inst_id, begin_time;

其中：

• UNXPSTEALCNT字段表示提交的Transaction Slots没有超出UNDO_RETENTION时间，也就是处于unexpired状态，但由于UNDO表空间满了，而被 Oracle覆盖了的次数。

• <start time of the ORA-1555 query>时间可通过ORA-1555发生时间减去QUERY DURATION而得到。例如：

Tue May 26 16:16:57 2009
ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

922秒为15分22秒。这样 ORA-1555开始发生的时间为 May 26 16:01:35 2009（16:01:35 = 16:16:57 – 15:22）。

如何计算UNDO表空间大小？

UNDO表空间的计算公式如下：

UndoSpace = UR * (UPS * DBS)

其中：

• UR = UNDO_RETENTION参数，单位为秒。

• UPS = 每秒产生的UNDO数据块数量。

• DBS = DB_BLOCK_SIZE参数。

上述UNDO_RETENTION、DB_BLOCK_SIZE可通过初始化参数文件获取，而UPS则可以通过查询v$undostat视图而获得。Oracle建议查询业务高峰时段产生的UNDO数据块数量。为此，执行如下查询：

SELECT undoblks / ((end_time – begin_time) * 86400) “Peak Undo Block Generation”

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat);

最终，计算高峰时段所需UNDO表空间大小的语句如下：

SELECT (UR * (UPS * DBS)) AS “Bytes”

FROM (SELECT value AS UR FROM v$parameter WHERE name = ‘undo_retention’),

(SELECT undoblks / ((end_time – begin_time) * 86400) AS UPS

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),

(SELECT block_size AS DBS

FROM dba_tablespaces

WHERE tablespace_name =

(SELECT UPPER(value)

FROM v$parameter

WHERE name = ‘undo_tablespace’));

11.7 ORA-30036: UNDO表空间无法扩展

什么叫ORA-30036错误?

ORA-30036也是Oracle数据库运行过程中常见的一个错误。以下就是Oracle关于ORA-30036错误的经典描述：

Error: ORA-30036 (ORA-30036)

Text: unable to extend segment by %s in undo tablespace ‘%s’

—————————————————————————

Cause: the specified undo tablespace has no more space available.

Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

该错误表示就是UNDO表空间不够了，简单解决办法就是对UNDO表空间进行扩容。但如同Oracle其它空间不够的类似错误一样，扩容并非唯一解决办法。

UNDO表空间分配算法

欲深入了解ORA-30036错误原因和解决办法，其实应从深入了解UNDO表空间分配算法开始。以下就是该算法主要思路：

1. 如果当前的UNDO extent还有空间，则从中分配新的数据块。

2. 否则，假设下一个extent过期（expired）了，则跳到（wrap）下一个extent，并且返回其第一个数据块。

3. 假设下一个extent为非过期（unexpired）的，则尝试从UNDO表空间分配新的空间。假设 UNDO表空间足够，则分配新的extent给Undo segment，并且返回新extent的第一个数据块。

4. 如果UNDO表空间不够了，则从offline状态的Undo Segment中去偷取过期（expired）的extent，分配给Undo segment，并且返回该extent的第一个数据块。

5. 如果offline状态的Undo Segment中没有过期（expired）的extent，则从Online状态的Undo Segment中偷取过期（expired）的extent，分配给Undo segment，并且返回该extent的第一个数据块。

6. 如果Undo表空间的数据文件是可扩展的，则扩展Undo表空间的数据文件，并且从中分配新的extent给Undo segment，以及返回该extent的第一个数据块。

7. 降低Undo保存期限参数（undo_retention）10%，并从释放的空间中偷取extent。

8. 从offline状态的Undo Segment中偷取非过期（unexpired）的extent。

9. 重复使用现有Undo Segment中非过期（unexpired）的extent。如果所有extent都处于忙碌状态，即都包含了未提交的信息，则跳到第10步。否则，跳到(wrap)下一个extent。

10. 从online状态的Undo Segment中偷取非过期（unexpired）的extent。

11. 如果上述所有尝试都失败了，则Oracle报错：ORA-30036！

诊断和解决办法

1. 首先，查询UNDO表空间的空间使用状况：

select sum(bytes) from dba_free_space where tablespace_name=’UNDOTBS1′;

select sum(bytes) from dba_data_files where tablespace_name=’UNDOTBS1′;

2. 确定UNDO表空间的数据文件是否为可扩展的：

select autoextensible from dba_data_files where tablespace_name=’UNDOTBS1′;

3. 按状态统计Undo Extents：

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

如果没有过期（expired）而只有非过期（unexpired）的Undo Extent，以及Active Extents，则Undo表空间的确太小，需要对Undo表空间大小进行重新规划并扩容。关于Undo表空间大小的计算方法，请见本章前述内容。在10g中还可以通过OEM中的Undo Advisor特性来进行Undo表空间的规划。

假设Undo表空间不够，则Oracle会尝试偷取非过期（unexpired）的Undo Extent，此时可能会导致ORA-1555错误。如果也没有非过期（unexpired）的Undo Extent，则的确需要对Undo表空间进行扩容。

10g中可以为Undo表空间指定Guaranteed Undo Retention特性。例如：

create undo tablespace undotbs1 datafile ‘undotbs1.dbf’size 1000M autoextend on

retention guarantee;

这样，Oracle就不会重复使用非过期（unexpired）的Undo Extent。因此，此时只能对Undo表空间扩容了。

关于Bug 5442919

如果有过期（expired）的Undo Extent，意味着这些extent是可以被重用的。但系统却报出ORA-30036，则很有可能是撞上Oracle Bug 5442919了。以下就是满足该Bug的所有条件：

1. undo_management=auto。

2. Undo表空间包含的数据文件均不能自动扩展。

3. DML操作失败并报ORA-30036错误，同时被写入log日志文件。而且alert.log中重复显示”Failure to extend rollback segment <us#>”，其中<us#>为相同值。

4. 实例运行时间达到1小时以上。

5. 系统存在大量offline的Undo Segment，例如1000个以上。

select count(*) from dba_rollback_segs where status=’OFFLINE’;

6. Undo表空间满了。

7. 存在大量过期（expired）或者非过期（unexpired）的Undo Extent。

select sum(bytes) “UNEXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’UNEXPIRED’;

select sum(bytes) “EXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’EXPIRED’;

该Bug在10.2.0.4以及11g中就已经修复了。在之前的版本，例如9i和10.2.0.1/2/3中，在某些平台可以向Oracle服务部门申请补丁回退（Backport），但这些版本早已经过了Oracle产品服务期，估计已经很难得到Oracle服务部门支持了。

感谢各位的阅读！关于“Oracle中如何解决ORA-01555错误”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！