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
- 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表空间