合 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参数。
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 900 ABSPONSEJSON 900 |
修改以PCTVERSION为准：
1 2 3 4 5 6 7 | alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(retention ); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(retention); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(PCTVERSION 20); |
查询：
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 20 ABSPONSEJSON 20 |
若是以undo_retention为准，则前后顺序需要修改：
1 2 3 4 5 6 7 8 | alter system set undo_retention =10800 sid='*'; alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABQUESTJSON)(retention ); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(PCTVERSION 20); alter table ABOLOGY.WF_ABCDEFGHIJKLMNOPQLOG modify lob(ABSPONSEJSON)(retention); |
查询：
1 2 3 4 5 6 7 8 | SQL> col COLUMN_NAME for a30 SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'WF_ABCDEFGHIJKLMNOPQLOG'; COLUMN_NAME PCTVERSION RETENTION ------------------------------ ---------- ---------- ABQUESTJSON 10800 ABSPONSEJSON 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 25 26 27 28 29 30 31 32 | -- 某个含lob字段的表大小 SELECT (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&&TABNAME'))) as tbsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&&TABNAME') AND L.OWNER = UPPER('&&SCHEMA'))) as lobsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&&SCHEMA'))) as lobindexsize_g , (select count(*) from &&SCHEMA..&&TABNAME) FROM DUAL; -- 含LOB字段的表总大小 SELECT (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Table Segment size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') and L.TABLE_NAME = S.SEGMENT_NAME ) as tbsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.OWNER = UPPER('&&SCHEMA'))) as lobsize_g, (SELECT trunc(SUM(S.BYTES)/1024/1024/1024) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&&SCHEMA'))) as lobindexsize_g FROM DUAL; |
LOB字段的retention和PCTVERSION说明
pctversion n /retention:这两个属性用来解决lob段的一致性读问题。lob的特殊性决定它不能使用undo/rollback segment来管理自己的更新的old version,通常lob会在自己所在的表空间中划分一部分空间来管理自己的undo，保证read consistent.lob中更新原理是在lob segment中分配新的chunk插入新的数据,保留旧的镜像,如果一个数据有多个更新存在的话, 那么就会存在多个版本.pctversion用来定义lob segment中undo区域的大小,pctverision 是一个百分比,定义所有lob空间用来存放前镜像的百分比,如果前镜像使用空间超过这个百分比了,oracle不自动扩展这部分的大小, 会重用这些前镜像的空间.如果一个lob segment段的更新很频繁的情况下，那么该lob段的增长可能会很快.retention是9i的新参数, 只能用在tablespace采用ASSM的情况,在lob更新的时候,前镜像会保留一段时间, 具体的时间由undo_retention参数决定.决定采用乃种undo 方式,必须对应用测试后在决定.
Lob字段默认是pctversion等到于10,意思是表lob字段所在的表空间需要预留10%的空间给lob的前映象使用,当表空间比较小时,用pctversion应该不会有大的问题,当表空间超过100g时,就需要预留10g,可能存在浪费空间的问题,比如每小时更新lob记录的频率增长不多,还要减去(当lob的内容小于4000字节时,lob的前映象保存在undo表空间)这部分,但如果该表空间增长到300g,就有30g的空间预留，导致较多的空间被浪费.所以,当您的LOB字段所在的表空间已经很大时,要注意pctversion参数要适当的调小,否则空间的浪费是相当的严重...
关于lob的retention参数,这个与undo表空间的undo_retention是一致的,如果你的lob更新较小,就可以通过修改undo_retention来调整lob前映象的失效时间,由于lob的retention参数是与undo_retention保持一致,所以修改undo_retention来说,一定要慎重,毕竟它将会影响库中所有lob字段的retention参数...
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.
