ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于clob字段的snapshot too old

关于clob字段的snapshot too old

原创 Linux操作系统 作者:aaqwsh 时间:2012-03-27 13:37:39 0 删除 编辑

一般情况下当我们遇到ORA-01555: snapshot too old的时候,我们第一反应就是undo不够,但是对于lob类型来说除了有可能是old version不够外,我们还要注意有可能是LOB segment corruption。我就遇到过这么一个情况,在一个没有操作的库上expdp一个含NCLOB的表时报ORA-01555: snapshot too old,让人很纳闷,问题就出在LOB segment corruption。有两点建议:
一是LOB字段最好别用,oraclelob字段的支持不好,导出来也奇慢无比。
二是如果你在查询含LOB字段的表时出现
ORA-01555: snapshot too old,需要有多种视角。

一导出该表时遇到ORA-01555: snapshot too old

[oracle@db-15 pump]$ expdp  \'\/ as sysdba \'  tables=test_sch.nclob_tab directory=pump  DUMPFILE=nclob_tab.dmp

 

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 16 March, 2012 10:28:56

 

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=test_sch.nclob_tab directory=pump DUMPFILE=nclob_tab.dmp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 15.21 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "TEST_SCH"."NCLOB_TAB" 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

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /data/oracle/pump/nclob_tab.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 10:31:53

 

二因为当时数据库没人操作,是clean shutdownstartup的,排除old version不够的原因,怀疑有LOB segment corruption通过如下方式来检测该字段:

create table corrupted_data (corrupted_rowid rowid);
 
set concat off

declare
 
error_1555 exception;
 
pragma exception_init(error_1555,-1555)
;
 
v_lob NCLOB
;
 
n number
;
begin

  
for cursor_lob in (select rowid r  from test_sch.NCLOB_TAB) loop
  
begin
    
select  PT_DESC into v_lob  from   test_sch.NCLOB_TAB  where rowid=cursor_lob.r;
    
n:=dbms_lob.instr(v_lob,hextoraw('889911'))
;
  
exception

    
when error_1555 then
      
insert into corrupted_data values (cursor_lob.r);
      
commit
;
  
end
;
 
end loop
;
end
;
/
undefine lob_column

 
 
之后查看 corrupted_data 发现17条记录存在问题: 
 
sys@item> select * from corrupted_data;
 
CORRUPTED_ROWID

----------------
--
AAAEKcAAMAABhYrAAA


AAAEKcAAMAABhZrAAA
AAAEKcAAMAABhXSAAG
 
17 rows selected.
 
 
查看任意一条记录 报错: 
select  PT_DESC  from  test_sch.NCLOB_TAB where rowid='AAAEKcAAMAABhYrAAA'
ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old
 
 
17条记录的NCLOB 都出现了问题,下面修复这些错误的column 信息  
 
update test_sch.NCLOB_TAB set  PT_DESC=null where rowid in (select CORRUPTED_ROWID from  corrupted_data);

 

三 可以参考这个文章:

Export Fails With ORA-2354 ORA-1555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? [ID 833635.1]


 

修改时间 25-AUG-2011     类型 PROBLEM     状态 PUBLISHED

 

In this Document
  Symptoms
  Cause
  Solution
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2   [Release: 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Following errors encountered during export:

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old

Cause

LOB segment corruption.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-719618/,如需转载,请注明出处,否则将追究法律责任。

下一篇: mysql HA 方案(1)
请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    261210