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='vis_outsyn' and table_name='VEH_IS_VEH_PICTURE';
SQL>select column_name, pctversion, retention from user_lobs where table_name='VEH_IS_VEH_PICTURE';

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 ('VEH_IS_VEH_PICTURE') and owner ='VIS_OUTSYN'));

---经过查询此lob对象使用的是pctversion而不是retention
DECODE(BITAND(FLAGS,32),32,'RETENTION','PCTVERSION')||'POLICYUSED'
------------------------------------------------------------------
Retention policy used

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

2.修改表的retention值

SQL>alter table VIS_OUTSYN.VEH_IS_VEH_PICTURE modify lob(ZP) (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)
1
查看表的retention值

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

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

言归正传，开始解决问题，首先我们查看当前实例使用的Undo表空间：

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

可以看到，使用的Undo表空间为“UNDOTBS1”，与报错内容中的名字一致。接下来查看数据库可用的Undo表空间：

SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO';


------查看UNDO表空间使用情况
select b.tablespace_name as "表空间",
b.file_name as "物理文件名",
b.bytes / 1024 / 1024 as "当前大小(M)",
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as "已使用(M)",
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as "使用率(%)",
case b.autoextensible
when 'YES' then '是'
else '否'
end as "是否自增",
b.maxbytes / 1024 / 1024 as "自增最大容量(M)"
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
and a.tablespace_name in ('UNDOTBS1', 'UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4')
group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
order by b.tablespace_name;

为表空间增加数据文件

alter tablespace UNDOTBS1
add datafile '+DATADG/esbmssdb/datafile/undotbs1.4.dbf' --数据文件名
size 100M --初始大小
autoextend on next 1M maxsize 8192M; --自增，每次增加1M，最大为8192M

2.设置文件自动扩展

alter database
datafile '+DATADG/esbmssdb/datafile/undotbs1.3.dbf'
autoextend on next 1M maxsize 8192M;
3.切换Undo表空间

alter system set undo_tablespace = UNDOTBS2;
注：如果数据库中只有一个可用的Undo表空间，则使用前两种方法。



--增加回滚表空间数据文件
alter tablespace UNDOTBS1 add datafile '+SYSTEM01' size 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M;

select * from dba_data_files t where t.TABLESPACE_NAME='UNDOTBS2';
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES /1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'UNDOTBS1';
select b.tablespace_name as "表空间",
b.file_name as "物理文件名",
b.bytes / 1024 / 1024 as "当前大小(M)",
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as "已使用(M)",
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as "使用率(%)",
case b.autoextensible
when 'YES' then '是'
else '否'
end as "是否自增",
b.maxbytes / 1024 / 1024 as "自增最大容量(M)"
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
and a.tablespace_name in ('UNDOTBS1', 'UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4')
group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
order by b.tablespace_name;

SELECT * FROM dba_tablespaces WHERE contents='UNDO';

1.CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE 'D:\oracle\data\UNDOTBS02.DBF' SIZE 300M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE 65535M
2.alter system set undo_tablespace = 'UNDOTBS2';
3.drop tablespace undotbs1 including contents and datafiles;
--查看Undo表空间
select value from v$spparameter where name = 'undo_tablespace';
show parameter undo

查看系统参数

SQL> show parameter undo_retention;

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

修改系统的参数到1800

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

再次查询表的retention

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

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

5
关闭数据库

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.
1
2
最后查询下，表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;