解决ORA-01555报错

OS环境：windows2008

数据库版本：oracle 11.2.0

 

今天同事需要执行一个拥有大批量运算的存储过程，当执行的时候报错，报错信息如下：

 

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 18 with name
"_SYSSMU18_671080725$" too small
ORA-06512: at "TRANUSER.TRAN_ETL_LOAD_J2S_MAIN", line 22
ORA-06512: at "TRANUSER.TRAN_ETL_LOAD_JST_PRE", line 5
ORA-06512: at line 2

 

--精彩解释

不知道是从哪里转的了, 假设有张表，叫table1，里面有5000万行数据，假设预计全表扫描1次需要1个小时，我们从过程来看： 

1、在1点钟，有个用户A发出了select * from table1;此时不管将来table1怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻的内容。这个是没有疑问的。 
2、在1点30分，有个用户B执行了update命令，更新了table1表中的第4000万行的这条记录，这时，用户A的全表扫描还没有到达第4000万条。毫无疑问，这个时候，第4000万行的这条记录是被写到了回滚段里去了的，我假设是回滚段RBS1，如果用户A的全表扫描到达了第4000万行，是应该会正确的从回滚段RBS1中读取出1点钟时刻的内容的。 
3、这时，用户B将他刚才做的操作commit了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4000万行记录的内容仍然还在回滚段RBS1里，系统可以根据SCN来到回滚段里找到正确的数据，但是大家注意到，这时记录在RBS1里的第4000万行记录已经发生了一点重大的改变：就是这个第4000万行的在回滚段RBS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！！！ 
4、由于用户A的查询时间漫长，而业务在一直不断的进行，RBS1回滚段在被多个不同的tracnsaction使用着，这个回滚段里的extent循环到了第4000万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！ 
5、到了1点40分，用户A的查询终于到了第4000万行，而这时已经出现了第4条说的情况，需要到回滚段RBS1去找数据，但是已经被覆盖掉了，于是01555就出现了。

--错误提示

数据库报错 ORA-01555 什么回滚段 '_SYSSMU168' is too small.很明显 是可用的回滚段太小了 满足不了那个大事物的需要 具体的sql我就不提供了

还有一种可能,一般伴随着ORA-22924出现就是LOB上的问题

辨别ORA-01555是不是发生在LOB上的,一般来说,普通的01555错误会指明发生01555的rollback segment,而LOB的则没有,而是伴随着ORA-22924出现

http://www.dbafan.com/blog/?p=11

辨别ORA-01555是不是发生在LOB上的,一般来说,普通的01555错误会指明发生01555的rollback segment,而LOB的则没有,而是伴随着ORA-22924出现http://www.dbafan.com/blog/?p=11

 

--回滚原理

回退段中存放的信息被称为“前照”(pre-image)，也就是说当一个进程对某个表进行了DML操作以后，
更改前的纪录信息被存放于回滚段，其作用有两个：

1、当进程要求回滚（ROLLBACK）的时候，使用回滚段中信息是纪录复原；

2、保持数据读的一致性，当一个进程从某个表中读纪录的时候，ORACLE返回的是当读开始或者进程开始时的纪录，如果在读取过程中有其他进程更改了表纪录，ORACLE就会从回滚段中读取当读操作开始时的数据。回滚段中信息并不是持久有效的，当进程提交（COMMIT）或者回滚（ROLLBACK）的时候，回滚段就被释放了。当一个进程在执行一个大查询的时候，如果在查询的过程中所读取得的表被更改而且更改COMMIT太久，那回滚段中的“前照”就有可能会被其他的进程覆盖，从而导致ORA-01555错误。

 

--解决方法

1、增加回滚段的大小，因为ORACLE总是覆盖最旧的回滚段，所以大的回滚段能有效的降低数据被覆盖的可能性。
2、检查你的程序，避免在一个大查询的过程中对所查询的表执行太多更新操作。

下面回顾下关于ora-01555的解决方法 10g默认是使用AUM 这里就不说了. 下面是几个解决方式来自hellodba 总结的很不错 大家可用参考下：

1、扩大回滚段: 因为回滚段是循环使用的，如果回滚段足够大，那么那些被提交的数据信息就能保存足够长的时间是那些大事务完成一致性读取。

2、增加undo_retention时间:在undo_retention规定的时间内，任何其他事务都不能覆盖这些数据。

3、优化相关查询语句，减少一致性读:减少查询语句的一致性读，就降低读取不到回滚段数据的风险。这一点非常重要！

4、减少不必要的事务提交:提交的事务越少，产生的回滚段信息就越少。

5、对大事务指定回滚段,通过以下语句可以指定事务的回滚段：SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment; 给大事务指定回滚段，即降低大事务回滚信息覆盖其他事务的回滚信息的几率，又降低了他自身的回滚信息被覆盖的几率。大事务的存在，往往是1555错误产生的诱因。

6、使用游标时尽量使用显式游标，并且只在需要的时候打开游标，同时将所有可以在游标外做的操作从游标循环中拿出。当游标打开时，查询就开始了，直到游标关闭。减少游标的打开时间，就减少了1555错误发生的几率。http://hi.baidu.com/xu521huan/blog/item/0903ec9b62d85ebec8eaf442.html

 

--一些实例

我的回答是先看看到底是哪个SQL有这个问题，再确定不是因为SQL本身太糟糕导致SNAPSHOT TOO OLD。再跟他们说我不相信把UNDO_RETENTION加大会有效地解决问题。最后给几个CASES来支持我的观点。
(1)reduce the frequency of commit
(2)set initialization paramter undo_retention(9i)
(3)alter system set retention guarrantee (10g)
(4)increase the size of the undo tablespace
(5)assign a large rollback segment for the large transaction
(6)tuning the long run sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
the root cause of the error ora-01555:the long run query can not find a consistent image, because the undo blocks that used to construct the consistent image were wrapped by other active transaction.

遇到这个问题，首先可以看是维护需要执行的SQL或者应用执行的SQL报的

1、如果平时不报，只是维护人员执行的SQL报的，一般是SQL写得不好，运行执行过长，超过了参数 redo_retention所设置的时间造成的。这种情况可以协助他们进行SQL分析和优化，减少运行时间，这个情况下系统不需要对系统进行调整

2、如果是应用程序报的，比如批量程序，则需要通知相关人员进行重做，否则批量运行失败，业务可能会因为数据遗漏出现问题。如果出现的频率较多，则需要在优化应用程序（优化的手段有SQL优化、适当增加commit的次数等）。在应用新版本上线前，可通过调整系统配置临时解决问题方法如：

1）增大undo表空间

2）增大redo_retention

3)为此大事物指定专门的undo 段

http://www.itpub.net/viewthread.php?tid=1021888&extra=&highlight=DBA%C3%E6%CA%D4&page=3

新鲜出炉的案例：APPS的人下午回馈说今天一个DB的JOB一直报SNAPSHOT TOO OLD。这是过去几个月这个数据库第一次有这种回馈。到ALERT LOG中看看，有好多这种ERROR：Wed Jul 16 10:30:44 2008 ORA-01555 caused by SQL statement below (Query Duration=884 sec, SCN: 0x0018.bef62785):Wed Jul 16 10:30:44 2008

Wed Jul 16 10:57:29 2008 ORA-01555 caused by SQL statement below (Query Duration=149 sec, SCN: 0x0018.bf0d3e47):Wed Jul 16 10:57:29 2008

嗯，884S，149S，不可能吧？看看UNDO SETTINGS，很大啊：

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files;
    GBYTES
----------
300.654297

SQL> select sum(bytes)/(1024*1024*1024) as gbytes from dba_data_files where tablespace_name='UNDOTBS2';
    GBYTES
----------
  9.765625

自己试试：
create table mytab as <the select statement> where 1=0
16:12:14 SQL> insert into mytab <the select statement>
insert into mytab
            *

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 27 with name "_SYSSMU27$"
too small
Elapsed: 00:10:08.83
奇怪了。看看今天这个UNTOTBS2 UTILIZATION怎样。

SQL> select snap_time, free_mb from tbs_usage_hist where database='<DB Name>' and tbs='UNDOTBS2' and snap_time>sysdate-1 order by snap_time;
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-15 18:00:00    9172.56
2008-07-15 19:00:00    9172.56
2008-07-15 20:00:00    9156.56
2008-07-15 21:00:00    9188.56
2008-07-15 22:00:00    9204.56
2008-07-15 23:00:00    9212.56
2008-07-16 00:00:00    9228.56
2008-07-16 01:00:00    9228.56
2008-07-16 02:00:00    9236.56
2008-07-16 03:00:00    9228.56
2008-07-16 04:00:00    9252.56
2008-07-16 05:00:00    9252.56
2008-07-16 06:00:00    9252.56
2008-07-16 07:00:00    9260.56
2008-07-16 08:00:00    9244.56
2008-07-16 09:00:00    8486.56
2008-07-16 10:00:00    1683.56
2008-07-16 11:00:00       2.31
2008-07-16 12:00:00       1.94
2008-07-16 13:00:00       2.44
2008-07-16 14:00:00       2.44
2008-07-16 15:00:00       1.25
2008-07-16 16:00:00      17.75

那 问题应当是很明了了，自今天十点多UNDOTBS2一直是HIGHLY UTILIZED。打个电话给APP OWNER，原来他今天早上十点左右做了一个很大的DELETE。即然这个报错的APP只要在二十四小时内能再执行完就可以，而OLTP APP没报错，那就再等等吧。在四点半时，UNDOTBS2就差不多是85% FREE。再试试：
16:37:49 SQL> insert into mytab <the select statement>
182 rows created.
Elapsed: 00:34:47.39
17:12:37 SQL>
现在的UNDOTBS2 UTILIZATION：
SNAP_TIME              FREE_MB
------------------- ----------
2008-07-16 17:00:00    8523.63
问题解决。SNAPSHOT TOO OLD从来就不是一个过时的题目,也没有一个简单的答案。