ORA-01555故障解析

我会带着你远行 2022-06-18 04:46 759阅读 0赞

今天生产库进行FLASHBACK query闪回查询的时候遇到一个问题， **ORA-01555**: snapshot  **too old** .......数据已经不能被闪回了...吸取教训了，在安装文档里就要对此项参数装完数据库后即刻作出调整。

我的环境是数据库版本是Oracle10g Release 2的，查看的数据库参数undo\_retention设置，发现这个参数被在10g中缺省的被设置为900秒（15分钟），这个时间长度是太短了，再安装的时候忘记修改了，马上将这个参数修改为10800，3个小时：  
ALTER SYSTEM SET undo\_retention=10800 SCOPE=BOTH;

为什么是3个小时？

以前一度这个参数的缺省值被设为10800，可是随之而来的是UNDO表空间的过分扩展，难以回收，Oracle在不同版本中，也在进行不停的加权和折中。

Oracle也许会这样想:**如果很少有人使用Flashback Query,而过大的undo\_retention又会带来麻烦，那么干脆，设小点。**

**如何理解这个参数的作用呢？**

链接：[http://blog.csdn.net/seagal890/article/details/3044226][http_blog.csdn.net_seagal890_article_details_3044226]

每一中数据库都需要有一种管理回滚或者撤销数据的方法。当一个DML发生以后，在用户还没有提交（COMMIT）改变，用户不希望这种改变继续保持，需要撤销所做的修改，将数据回退到没有发生改变以前，这时就需要使用一种被称为 *撤销记录*的数据。

使用撤销记录，我们可以：

1、  当使用ROLLBACK语句时回滚事务，撤销DML操作改变的数据

2、  恢复数据库

3、  提供读取的一致性

4、  使用Oracle Flashback Query分析基于先前时间点的数据

5、  使用Oracle Flashback特性从逻辑故障中恢复数据库

### Oracle10g中的自动撤销管理（AUM） ###

在Oracle10g中对于回滚段的管理可以通过配置参数而实现自动管理。为启用撤销空间的自动管理，首先必须在init.ora中或者SPFILE文件中指定自动撤销模式。其次需要创建一个专用的表空间来存放撤销信息，这保证用户不会在SYSTEM表空间中保存撤销信息。此外还需要为撤销选择一个保留时间。

如果需要实现AUM，需要配置以下3个参数：

UNDO\_MAMAGEMENT

`UNDO_TABLESPACE`

UNDO\_RETENTION

查看初始化参数的设置：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      SQL&gt; show parameter undo_tablespace; 
    </div> 
    <div>
      NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE 
    </div> 
    <div>
      ------------------------------------ ----------- ----------------------- 
    </div> 
    <div>
      undo_tablespace&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNDOTBS1 
    </div> 
    <div>
      SQL&gt; show parameter undo_management; 
    </div> 
    <div>
      NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE 
    </div> 
    <div>
      ------------------------------------ ----------- ----------------------- 
    </div> 
    <div>
      undo_management&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AUTO 
    </div> 
    <div>
      SQL&gt; show parameter undo_retention; 
    </div> 
    <div>
      NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE 
    </div> 
    <div>
      ------------------------------------ ----------- ----------------------- 
    </div> 
    <div>
      undo_retention&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; integer&nbsp;&nbsp;&nbsp;&nbsp; 900 
    </div> 
    <div>
      SQL&gt; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

初始化参数的描述：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <strong>Initialization Parameter</strong> 
    </div> </td> 
   <td> 
    <div> 
     <strong>Description</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      UNDO_MANAGEMENT 
    </div> </td> 
   <td> 
    <div>
      If AUTO, use automatic undo management. The default is MANUAL 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      UNDO_TABLESPACE 
    </div> </td> 
   <td> 
    <div>
      An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace. 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      UNDO_RETENTION 
    </div> </td> 
   <td> 
    <div>
      The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low. 
    </div> 
    <div>
      For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**如果将初始化参数UNDO\_MANAGEMENT设置为AUTO，则Oracle10g将启用AUM。**

可以在初始化参数UNDO\_RETENTION中设置撤销保留时间的大小：

UNDO\_RETENTION＝1800         设置保留时间为30分钟（1800秒）

UNDO\_RETENTION参数默认设置为900秒。

### UNDO\_RETENTION的值应该设置为多少才合理？ ###

不存在理想的UNDO\_RETENTION的时间间隔。保留时间间隔依赖于估计最长的事务可能运行的时间长度。根据数据库中最长事务长度的信息，可以给UNDO\_RETENTION分配一个大致的时间。

可以通过v$undostat视图的maxquerylen列查询在过去的一段时间内，最长的查询执行的时间（以秒为单位）。UNDO\_RETENTION参数中的时间设置应该至少与maxquerylen列中给出的时间一样长。

Oracle提供如下为新数据库设置撤销保留时间间隔的指导：

1、  OLTP系统：15分钟

2、  混合： 1小时

3、  DSS系统：3小时

4、  闪回查询：24小时

UNDO\_RETENTION参数的较高值并不保证撤销数据保留UNDO\_RETENTION参数指定的时间。为保证撤销保留指定的时间，必须使用RETENTION GRARANTEE子句。

例如：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      CREATE UNDO TABLESPACE UNDOTBS01 
    </div> 
    <div>
      DATAFILE 
    </div> 
    <div>
      ‘E:/oracle/product/10.2.0/oradata/keymen/UNDOTBS01.DBF’ 
    </div> 
    <div>
      SIZE 500M AUTOEXTEND ON 
    </div> 
    <div>
      RETENTION GUARANTEE 
    </div> 
    <div>
      &nbsp; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

也可以使用ALTER DATABASE命令保证数据库中的撤销保留

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      ALTER DATABASE UNDOTBS01 RETENTION GUARANTEE 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

关闭撤销信息的保证保留

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      ALTER DATABASE UNDOTBS01 RETENTION NOGUARANTEE 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

### 设置撤销表空间的尺寸 ###

Oracle建议使用Undo Advisor的帮助下设置撤销表空间的尺寸。可以创建一个小尺寸（大约500M）的撤销表空间，AUTOEXTEND数据文件属性设置为ON，从而允许表空间自动扩展。此表空间将自动增长以支持数据库中活动事务数目的增长以及事务长度的增长。

在数据库运行适当的一段时间后，可以使用UNDO Advisor来得出关于设置撤销表空间尺寸的建议。应该使用Analysis Time Period字段中允许的最大时间。出于此目的，可以使用OEM UNDO Management页面中给出的Longest——Runing Query长度。还必须根据闪回需求指定New UNDO Retention字段的值。例如：如果希望表能闪回24小时，应该使用24小时作为这个字段的值。

假如数据库中用RETENTION GUARANTEE子句配置了保证保留撤销。如果撤销表空间太小不能满足使用它的所有活动事务，那么会发生以下情况：

1、  如果撤销表空间用完85％，Oracle将发布一个自动表空间警告

2、  当撤销表空间用完97％时，Oracle将发布一个自动表空间严重警告

3、  所有DML语句将不允许，并且会接收到一个空间超出错误

4、  DDL语句允许继续执行

关于UNDO\_RETENTION的保留策略，EYGLE里的文章更详细的阐述了Guarantee的含义

链接： [http://www.eygle.com/archives/2005/03/oracle10gaeundo.html][http_www.eygle.com_archives_2005_03_oracle10gaeundo.html]

NO Guaranteed：  
也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。  
很多时候这是不希望看到的。  
  
从Oracle10g开始，如果你设置UNDO\_RETENTION为0，那么Oracle启用自动调整以满足最长运行查询的需要。当然如果空间不足，那么Oracle满足最大允许的长时间查询。而不再需要用户手工调整。  
  
同时Oracle增加了Guarantee控制，也就是说，你可以指定UNDO表空间必须满足UNDO\_RETENTION的限制。

<table> 
 <tbody> 
  <tr> 
   <td> <pre>SQL&gt; alter tablespace undotbs1 retention guarantee;
</pre> <pre>Tablespace altered
</pre> <pre>SQL&gt; alter tablespace undotbs1 retention noguarantee;
</pre> <pre>Tablespace altered</pre> <pre>在DBA_TABLESPACES视图中增加了RETENTION字段用以描述该选项:</pre> <pre>SQL&gt; select tablespace_name,contents,retention from dba_tablespaces;
</pre> <pre>TABLESPACE_NAME                CONTENTS  RETENTION</pre> <pre>------------------------------ --------- -----------</pre> <pre>SYSTEM                         PERMANENT NOT APPLY
</pre> <pre>UNDOTBS1                       UNDO      NOGUARANTEE
</pre> <pre>SYSAUX                         PERMANENT NOT APPLY
</pre> <pre>TEMP                           TEMPORARY NOT APPLY
</pre> <pre>USERS                          PERMANENT NOT APPLY
</pre> <pre>14 rows selected
</pre> </td> 
  </tr> 
 </tbody> 
</table>

在Oracle官方文档上这样解释:  
**RETENTION** Undo tablespace retention:  
**GUARANTEE** \- Tablespace is an undo tablespace with RETENTION specified as GUARANTEE  
  
A RETENTION value of GUARANTEE indicates that unexpired undo in all undo segments in the undo tablespace should be retained even if it means that forward going operations that need to generate undo in those segments fail.  
  
**NOGUARANTEE** \- Tablespace is an undo tablespace with RETENTION specified as NOGUARANTEE  
  
**NOT APPLY** \- Tablespace is not an undo tablespace.

[http_blog.csdn.net_seagal890_article_details_3044226]: http://blog.csdn.net/seagal890/article/details/3044226
[http_www.eygle.com_archives_2005_03_oracle10gaeundo.html]: http://www.eygle.com/archives/2005/03/oracle10gaeundo.html