ORA-31693 ORA-02354 ORA-01555导出快照过旧报错案例分析

ORA-31693 ORA-02354 ORA-01555导出快照过旧报错案例分析

一、环境背景

windows server 2012 R2 + oracle 11.2.0.4 rac集群

故障现象：在导出用户模式时有如下报错
ORA-31693: 表数据对象 “EPOINT_JSCL”.“FRAME_ATTACHSTORAGE” 无法加载/卸载并且被跳过, 错误如下:
ORA-02354: 导出/导入数据时出错
ORA-01555: 快照过旧: 回退段号 19 (名称为 “_SYSSMU19_1978236651$”) 过小

二、排查过程

1.根据报错信息，只有FRAME_ATTACHSTORAGE表导出有问题。
查询mos发现，是因为导出表中含有lob对象，并且retention没有正确的设置

2.查询下FRAME_ATTACHSTORAGE表属性，content列包含lob对象

3.查看系统参数undo retention，此数值是默认的900

4.查看dba_lobs视图（此视图可以获得包含lob字段的表），FRAME_ATTACHSTORAGE的content字段，retention值为空

SQL>select retention,t.* from dba_lobs t where  owner='EPOINT_JSCL' and table_name='FRAME_ATTACHSTORAGE';
SQL>select column_name, pctversion, retention from user_lobs where table_name='FRAME_ATTACHSTORAGE' 

5.用sysdba账户查询content的lob字段是使用pctversion还是retention

SQL> select decode(bitand(flags, 32), 32, 'Retention', 'Pctversion') ||' policy used' from lob$ where lobj# in (select object_id
                      from dba_objects
                     where object_name in
                           (select segment_name
                              from dba_lobs
                             where table_name in ('FRAME_ATTACHSTORAGE') and owner ='EPOINT_JSCL'));

经过查询此lob对象使用的是pctversion而不是retention

处理过程：
1.由于默认值900太小，修改undo retention

2.修改表的retention值

SQL>alter table EPOINT_JSCL.FRAME_ATTACHSTORAGE modify lob(CONTENT) (retention); 

3.再次查询发现已经使用retention

4.最后执行数据泵导出操作，成功导出

总结：分析产生该错误的主要原因为回滚段设置太小，通常在UNDO回滚段中会保留数据库在某个时间点的数据，用来保证数据的一致性读。而在用户利用数据泵工具执行导出数据表操作时，又有其它用户对该表进行了修改，如果修改提交后UNDO中无足够空间，之前保存在UNDO中的数据资料就会被覆盖，从而依赖于这些数据资料的操作就无法获得一致性读，导致数据迁移过程产生以上报错。而且Oracle由参数undo_retention指定时间去释放UNDO回滚段，所以如果数据迁移时长超过undo_retention指定时间也会导致快照过旧的问题产生。

三、相关解析

1.如果在parameter修改了undo retention，但是并没有修改表属性的lob retention，此时业务表并不会采用parameter内的值，仍旧是表属性内retention，需要手动执行命令
alter table tablename modify lob(lob字段类型) (retention);
下面是测试过程：
创建测试表lobretention

 SQL> CREATE TABLE lobretention (LOBLOC blob,id NUMBER)

查看表的retention值

SQL> select retention from dba_lobs where table_name='LOBRETENTION';

RETENTION
----------
1400

查看系统参数

SQL> show parameter undo_retention;

NAME                                  TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_retention                    integer   1400

修改系统的参数到1800

SQL> alter system set undo_retention= 1800 scope=both;
System altered.

再次查询表的retention

SQL> select retention from dba_lobs where table_name='LOBRETENTION';

RETENTION
----------
1400

关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

重启库之后再次查询

SQL> connect test/test
Connected.
SQL> select retention from dba_lobs where table_name='LOBRETENTION';

RETENTION
----------
1400

此时表的rention并不是系统参数值

SQL> show parameter undo_retention;

NAME                                   TYPE    VALUE
------------------------------------ ----------- ------------------------------
undo_retention                      integer 1800

SQL> alter table lobretention modify lob(lobLoc) (pctversion 5);
Table altered.

此时需要手动修改表的lob类型列lobloc

SQL> alter table lobretention modify lob(lobLoc) (retention);
Table altered.

最后查询下，表LOBRETENTION的值更系统参数值已经一致

SQL> select retention from dba_lobs where table_name='LOBRETENTION';

RETENTION
----------
1800

2.pctversion与retention不能共存（两者只能选定其中一种）
查询表lob表，此时为retention

SQL>  select retention,PCTVERSION  from dba_lobs t where  owner='CS1' and table_name='LOBRETENTION';

 RETENTION PCTVERSION
---------- ----------
  1800                 

修改表lobloc列为pctversion

SQL> alter table lobretention modify lob(lobloc)(pctversion 10);

Table altered.

查询表lob表，此时为pctversion

SQL>  select retention,PCTVERSION  from dba_lobs t where  owner='CS1' and table_name='LOBRETENTION';

 RETENTION PCTVERSION
----------     ----------
                            10

3. Automatic UNDO Retention是10g的新特性，在10g和之后的版本的数据库，这个特性是默认启用的。
   在Oracle Database 10g中当自动undo管理被启用，总是存在一个当前的undo retention，Oracle Database尝试至少保留旧的undo信息到该时间。数据库收集使用情况统计信息，基于这些统计信息和UNDO表空间大小来调整undo retention的时间。
   Oracle Database基于undo表空间大小和系统活动自动调整undo retention，通过设置UNDO_RETENTION初始化参数指定undo retention的最小值。
   但是需要注意，自动调整undo retention不支持LOB，因为不能在undo表空间中存储任何有关LOBs事务的UNDO信息。LOB 列的撤销信息保存周期由UNDO_RETENTION 参数决定。
   PCTVERSIOIN与RETENTION
   1）老的方式：PCTVERSIOIN

这个参数关系到LOB数据的一致读，指的是表lob字段所在的表空间需要预留给lob的前映象使用的最大百分比，默认值是10。也就是说，只要使用不超过10%，LOB字段的前映像的数据是不会被覆盖的。

2） 新的方式（自动还原段管理使用）：RETENTION

Oracle用UNDO_RETENTION参数来决定在数据库中保留多少已经提交的UNDO数据。这种方式LOB段跟普通段使用相同的过期策略。

计算业务高峰期每秒产生undo数据块的个数

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;  

四、参考文献

MOS ID 563470.1
MOS ID 1507116.1

https://blog.csdn.net/xqzhang8/article/details/78731298
http://blog.itpub.net/15480802/viewspace-708843/
https://blog.csdn.net/ghost241/article/details/49962611
http://blog.itpub.net/23135684/viewspace-1065601/
http://dbaplus.cn/news-10-262-1.html
http://www.fx361.com/page/2016/0425/657272.shtml一种数据迁移过程中产生快照过旧问题的解决方法
[Oracle]理解undo表空间