数据泵expdp导出遇到ORA-01555和ORA-22924问题的解决

使用数据泵导出数据库数据时，发现如下错误提示：

ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

个人认为报快照过旧绝大部分可能原因是undo表空间不足或保留时间设置问题。本次着重讨论表数据损毁引起的报错。

 

1.先查看表空间的使用率

SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名",
　　D.TOT_GROOTTE_MB AS "表空间大小(M)",
　　D.TOT_GROOTTE_MB-F.TOTAL_BYTES AS "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES AS "空闲空间(M)",
　　F.MAX_BYTES AS "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

 

2.看到ORA-01555错误，还以为是经典错误，尝试调整undo_retention参数 

alter system set undo_retention=10800 scope=both;

show parameter undo_tablespace

 

 必要时需要更换undo表空间 步骤如下

 

select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';

 创建undo表空间

SQL>  create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 20000M;
Tablespace created.

 

更换默认undo表空间

SQL> alter system set undo_tablespace = undotbs2 scope=both;
System altered.

 

查看配置是否生效 

SQL> select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
TABLESPACE_NAME                STATUS             COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1                       OFFLINE                  10
SYSTEM                         ONLINE                    1
UNDOTBS2                       ONLINE                   10

 

检查确认UNDOTBS1中没有ONLINE的segment
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
no rows selected

 

删除旧的UNDOTBS1
SQL> Drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

 

 

猜测是表空间有问题，这里尝试对 CAMS_CORE下的索引和LOB 进行表空间迁移。

（1）新建新的表空间

（2）拼接表空间迁移语句，前面已有文章写到了表空间迁移方案

（3）执行表空间迁移语句

alter table CAMS_CORE.BP_EXCEPTION_LOG move lob(EX_STACK) store as (tablespace cams_core_lob);

执行到该语句和复制表的时候都提示错误： 

ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小

ORA-22924: 快照太旧

 

这里，问题应该比较明显了，有部分 LOB数据有问题。

 

寻找问题解决方案（MOS）

使用关键字 “expdp ORA-01555 ORA-22924  LOB”进行查找：

Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (文档 ID 833635.1)

 

 

5.参考MOS给出的解决方案，动手处理问题

 

1.创建表存放查询出有问题的rowid

 

set concat off

  

create table corrupted_lob_data (corrupted_rowid rowid);   --存放损毁数据的rowid

set concat off

 

 

2.收集有问题字段中存在问题值的rowid

declare  

  error_1555 exception;  

  pragma exception_init(error_1555,-1555);  

  num number;  

begin  

  for cursor_lob in (select rowid r, &lob_column from &table_owner.&table_with_lob) loop  

    begin  

      num := dbms_lob.instr (cursor_lob.&lob_column, hextoraw ('889911')) ;  

    exception  

      when error_1555 then  

        insert into corrupted_lob_data values (cursor_lob.r);  

        commit;  

    end;  

  end loop;  

end;  

/  

 

 

--lob_column  表的字段名（一般扫描blob、clob字段）

--table_owner  表的所有者

--table_with_lob 表名 

  

Enter value for table_owner: EX_STACK

Enter value for table_owner: CAMS_CORE

Enter value for table_with_lob: BP_EXCEPTION_LOG

old   6:   for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop

new   6:   for cursor_lob in (select rowid r, EX_STACK from CAMS_CORE.BP_EXCEPTION_LOG) loop

old   8:       num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;

new   8:       num := dbms_lob.instr (cursor_lob.EX_STACK, hextoraw ('889911')) ;

  

PL/SQL procedure successfully completed.

 

3.通过查询出的rowid查看存在问题的数据记录：

select * from test.test1

where rowid in ( select * from test.corrupted_lob_data );

 或

 

 

 

 

 

确实存在 3条数据， CLOB 字段数据大小为 ，显然有问题。 

MOS上给出的导出方案是将问题数据exclude掉，这里为了彻底解决问题，将3条数据导出为csv文件，然后删除。然后再次导出数据库数据，不再提示报错。

 

补充：或用如下方式过滤问题数据expdp备份 

expdp test/test123 parfile=his.par directory=DMPDIR

 

vim his.par

dumpfile = 208306.dmp
logfile = 208306.log
tables = test1,test2,test3 
query = (
test1:"where rowid not in ('AAAameAAHAAM5c6AAJ')",
test2:"where rowid not in ('AAAamhAAIAAHTa9AAG','AAAamhAAJAAHhSRAAF','AAAamhAAKAAL3g3AAD')",
test3:"where rowid not in ('AAAa3oAALAAMirTAAF','AAAa3oAAKAANm7/AAB')"
)

注：query中的特殊字符不在参数文件中，前面需要加转义字符。

 

 4.或者可以对问题数据进行删除或置空

 

update "WF_PROCESS_RUNNING"

 

     set PROCESS_XML = empty_clob()

 

     where rowid in (select corrupted_rowid from test123_PROCESS_XML); 

 

     commit;

注：清理前请先与业务部门确认影响，二进制数据一般是很难被修复的。