合 Oracle数据泵expdp导出报ORA-01555和ORA-22924
Tags: Oracle数据泵expdpCLOBORA-01555ORA-22924
- 现象
- 原因
- 解决
- 可能原因一
- 可能原因二
- LOB字段大小查询
- LOB字段的retention和PCTVERSION说明
- Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)
- SOLUTION
- REFERENCES
- Lob retention not changing when undo_retention is changed (Doc ID 563470.1)
- APPLIES TO:
- SYMPTOMS
- CAUSE
- SOLUTION
- 参考
现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [oracle@oadata bk]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=oa_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_oa_20221124.log \ > SCHEMAS=RMAN,ABOLOGY,C CLUSTER=N COMPRESSION=ALL parallel=12 FILESIZE=10g Export: Release 11.2.0.4.0 - Production on Thu Nov 24 04:31:38 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=D1 dumpfile=oa_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_oa_20221124.log SCHEMAS=RMAN,AB,C CLUSTER=N COMPRESSION=ALL parallel=12 FILESIZE=10g Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 166.6 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-31693: Table data object "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" 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 with name "" too small ORA-22924: snapshot too old |
原因
1、undo_retention 参数配置过小,表本身很大或含有CLOB字段
2、表存在大的CLOB字段,而lob字段有坏块导致。
解决
可能原因一
表中存在lob字段,而lob字段有坏块导致。报错如下:
1 2 | ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old |
首先应该查询坏块的rowid,并且记录到sys.corrupted_lob_data表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | drop table sys.corrupted_lob_data; create table sys.corrupted_lob_data (owner varchar(255),tbname varchar(255),col_name varchar(255),corrupted_rowid rowid); truncate table sys.corrupted_lob_data; set concat off declare error_1555 exception; pragma exception_init(error_1555,-1555); num number; begin for cur in (SELECT L.TABLE_NAME,L.COLUMN_NAME FROM DBA_LOBS L WHERE L.OWNER = UPPER('&&table_owner') and L.TABLE_NAME=UPPER('&&table_name') ) loop for cursor_lob in (select rowid r, cur.COLUMN_NAME from &&table_owner.&&table_name) loop begin num := dbms_lob.instr (cur.COLUMN_NAME, hextoraw ('889911')) ; exception when error_1555 then insert into sys.corrupted_lob_data values ('&&table_owner','&&table_name',cur.COLUMN_NAME,cursor_lob.r); commit; end; end loop; end loop; end; / |
然后查询表sys.corrupted_lob_data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | select * from sys.corrupted_lob_data; -- 发现报错 select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" where rowid in ( select corrupted_rowid from sys.corrupted_lob_data ); SQL> select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" 2 where rowid in ( select * from sys.corrupted_lob_data ); ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old SQL> select * from "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" where rowid='AABOzeAAIAAHxKWAAB'; ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old |
MOS上给出的导出方案是将问题数据exclude掉,这里为了临时解决问题,将这几条数据的COLB字段置空。然后再次导出数据库数据,不再提示报错。
1 2 3 4 | update "ABOLOGY"."WF_ABCDEFGHIJKLMNOPQLOG" t set t.ABMIDS='' where rowid in ( select corrupted_rowid from sys.corrupted_lob_data ); commit; |
可能原因二
原因二是undo_retention 参数配置过小,表本身很大或含有CLOB字段,报错:
1 2 3 4 5 | ORA-31693: Table data object "LHR"."ABC_RLOG" 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 25 with name "_SYSSMU25_1063943794$" too small |
首先修改undo_retention=10800
,若含有大的LOB字段,则重新导出可能问题依旧存在:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@oadata bk]$ oerr ora 22924 22924, 00000, "snapshot too old" // *Cause: The version of the LOB value needed for the consistent read was // already overwritten by another writer. // *Action: Use a larger version pool. SQL> show parameter retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 undo_retention integer 900 SQL> alter system set undo_retention =10800; System altered. SQL> show parameter retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 undo_retention integer 10800 SQL> |
这个表本身不大,但是lob字段占用的空间很大,可以发现即使改动了参数undo_retention=10800,表lob的属性RETENTION依旧900,必须增加该参数,或者使用PCTVERSION参数。