oracle 11g rac ORA-01555 快照过旧报错处理

iyufygfd

ORA-01555 快照过旧，是数据库中很常见的一个错误，比如当我们的事务需要使用undo来构建CR块的时候，
而此时对应的undo 已经不存在了， 这个时候就会报ORA-01555的错误。
环境是Oracle 11g RAC 由于客户执行一个比较复杂的SQL，使用PLSQL运行了88分钟后出现报错,这是一个要查看报表的SQL。

临时的处理方法如下:
以下为虚拟机模拟操作，建议数据库安装的时候这个参数一定要提前调整优化一下，不要使用默认值。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

[iyunv@ysdb1 ~]# su - oracle [oracle@ysdb1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 23 10:39:44 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@ysdb1>show parameter undo; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_management                      string      AUTO undo_retention                       integer     900     --默认15分钟 undo_tablespace                      string      UNDOTBS1 SYS@ysdb1> SYS@ysdb1>alter system set undo_retention=10800 scope=both; System altered.

--查看表空间容量

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SYS@ysdb1>col tablespace_name for a15 SYS@ysdb1>col free_rate for a15 SYS@ysdb1>SELECT a.tablespace_name,              ROUND (a.total_size) "total_size(MB)",              ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",              ROUND (b.free_size, 3) "free_size(MB)",              ROUND (b.free_size / total_size * 100, 2) || '%' free_rate       FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size                   FROM dba_data_files               GROUP BY tablespace_name) a,              ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size                  FROM dba_free_space              GROUP BY tablespace_name) b     WHERE a.tablespace_name = b.tablespace_name(+); TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE --------------- -------------- ------------- ------------- --------------- SYSAUX                     600       178.625       421.375 70.23% UNDOTBS1                   200        56.062       143.938 71.97% USERS                        5             1             4 80% SYSTEM                     700         287.5         412.5 58.93% UNDOTBS2                   200         6.687       193.313 96.66%

1 2 3 4 5 6 7 8 9 10

SYS@ysdb1> SELECT   tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"         FROM   dba_undo_extents     GROUP BY   tablespace_name, status; TABLESPACE_NAME      STATUS      Bytes(M) TABLESPACE_NAME STATUS      Bytes(M) --------------- --------- ---------- UNDOTBS1        UNEXPIRED         11 UNDOTBS2        UNEXPIRED      2.125 UNDOTBS1        EXPIRED      44.0625 UNDOTBS2        EXPIRED       3.5625

1.出现ORA-01555错误，通常有2种情况：
    1)SQL语句执行时间太长，或者UNDO表空间过小，或者事务量过大，或者过于频繁的提交，导致执行SQL过程中进行一致性读时，SQL执行后修改的前镜像（即UNDO数据）在UNDO表空间中已经被覆盖，不能构造一致性读块（CR blocks）。  这种情况最多。
    2)SQL语句执行过程中，访问到的块，在进行延迟块清除时，不能确定该块的事务提交时间与SQL执行开始时间的先后次序。 这种情况很少。


2.第1种情况解决的办法：
  1)增加UNDO表空间大小
  2)增加undo_retention 时间，默认只有15分钟
  3)优化出错的SQL，减少查询的时间，首选方法
  4)避免频繁的提交