20181031-expdp ORA-01555(一)

环境信息：

DB:Oracle 11.2.0.1.0
OS:Windows Server 2012

敏感数据已替换

问题：

expdp导出一张含有BLOG字段的大表(20G)时，报错ORA-01555
expdp_log

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "CHENJCH"."T_XXX_XXXXX" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_XXXXXXXXX$" too small

alert_CJC.log

Tue Oct 30 12:05:20 2018
Archived Log entry 6560 added for thread 1 sequence 46359 ID 0x7262bbad dest 1:
Tue Oct 30 13:19:12 2018
ORA-01555 caused by SQL statement below (SQL ID: 88tquba1dj6s0, SCN: 0x0000.47dd74a2):
SELECT * FROM RELATIONAL("CHENJCH"."T_XXX_XXXXX")
Tue Oct 30 13:29:42 2018

问题分析：

ORA-01555问题一般有两个原因：

(1)UNDO表空间不足
(2)undo_retention时间太小

查看UNDO表空间还有很大剩余

SQL> select bytes / 1024 / 1024 / 1024,
            tablespace_name,
            autoextensible,
            maxbytes / 1024 / 1024 / 1024
       from dba_data_files a
      where tablespace_name = 'UNDOTBS1';
      
BYTES/1024/1024/1024 TABLESPACE_NAME                AUTOEXTENSIBLE MAXBYTES/1024/1024/1024
-------------------- ------------------------------ -------------- -----------------------
        1.4306640625 UNDOTBS1                       YES                   31.9999847412109

SQL> select segment_name,
       tablespace_name,
       r.status,
       (initial_extent / 1024) InitialExtent,
       (next_extent / 1024) NextExtent,
       max_extents,
       v.curext CurExtent
  From dba_rollback_segs r, v$rollstat v
 Where r.segment_id = v.usn(+)
   and segment_name = '_SYSSMU10_XXXXXXXXX$';

SEGMENT_NAME                   TABLESPACE_NAME                STATUS           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT
------------------------------ ------------------------------ ---------------- ------------- ---------- ----------- ----------
_SYSSMU10_XXXXXXXXX$           UNDOTBS1                       ONLINE                     128         64       32765          3

查看undo_retention为默认值900秒

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

查看retention为默认值900秒，PCTVERSION为空

SQL> select column_name, pctversion, retention
       from dba_lobs
      where table_name = 'T_XXX_XXXXX'
        and owner = 'CHENJCH';
        
COLUMN_NAME      PCTVERSION  RETENTION
---------------- ---------- ----------
FFILE                           900

查看当前使用retention还是PCTVERSION

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 ('T_XXX_XXXXX')
                                  and owner = 'CHENJCH'));

DECODE(BITAND(FLAGS,32),32,'RE
------------------------------
Retention policy used

SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
            1939  

解决方案：

调大 RETENTION

SQL> ALTER SYSTEM SET UNDO_RETENTION = 3600 scope=both;   

SQL> SHow parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1

lob字段使用的retention还是900

SQL> select column_name, pctversion, retention
       from dba_lobs
      where table_name = 'T_XXX_XXXXX'
        and owner = 'CHENJCH';

COLUMN_NAME      PCTVERSION  RETENTION
---------------- ---------- ----------
FFILE                           900

lob字段使用的retention需要在执行一次

SQL> alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (retention);  

ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

等一会在执行

alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (pctversion 5);

SQL> select column_name, pctversion, retention
       from dba_lobs
      where table_name = 'T_XXX_XXXXX'
        and owner = 'CHENJCH';  

COLUMN_NAME      PCTVERSION  RETENTION
---------------- ---------- ----------
FFILE                           3600

回退操作

ALTER SYSTEM SET UNDO_RETENTION = 900 scope=both;
alter table CHENJCH.T_XXX_XXXXX modify lob(FFILE) (retention);

参考：

Data Pump Export Fails With ORA-31693 ORA-02354 and ORA-01555 Errors And No LOB Corruption (文档 ID 1507116.1)
https://support.oracle.com/epmos/faces/SearchDocDisplay?_afrLoop=337135896307291&_afrWindowMode=0&_adf.ctrl-state=1bqt29sg65_4 

欢迎关注我的微信公众号"IT小Chen"