前几天刚处理了 logic standby故障，还没缓过神来 , logical standby 又出问题了。 还是那张表，那个错误 不得不让我怀疑 是不是那张表出现了问题。记录一下解决的过程：

2012-03-16 11:45:00 update “PROD_DATA2″.”PM_SUPPLIER” ORA-01403: no data found
set
“PRODUCT_CODE” = ‘0021244213’,

2012-03-16 11:44:29 update “PROD_DATA2″.”PM_SUPPLIER” ORA-01403: no data found
set
“PRODUCT_CODE” = ‘0021244213’,

依然是这个老错误，这张表的同步出现了不一致，下面我们来看看这张表的结构：

SQL> desc “PROD_DATA2″.”PM_SUPPLIER”;
Name Null? Type
—————————————– ——– —————————-
ID NOT NULL NUMBER(18)

PRODUCT_DESCRIPTION NCLOB

…..

IS_DELETED NUMBER(1)
UNIT VARCHAR2(10)
CATEGORY_SEARCH_NAME VARCHAR2(200)

这张表包含了一个NCLOB 字段，针对之前同步这张表的报错，让我们怀疑是不是NCLOB 出现了问题，让我们看看当时同步这张表的报错：

SQL> exec DBMS_LOGSTDBY.INSTANTIATE_TABLE(‘PROD_DATA2′,’PM_SUPPLIER’,’dblinkprimary’);

ERROR at line 1:
ORA-20001: ORA-31693: Table data object “PROD_DATA2″.”PM_SUPPLIER” failed to
load/unload and is being skipped due to error:
ORA-12161: TNS:internal error: partial data received
ORA-02063: preceding from
ORA-06512: at “SYS.KUPW$WORKER”, line 113
ORA-02063: preceding from
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old
ORA-02063: preceding 2 lines from DBLINKPRIMARY
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snaps
ORA-06512: at “SYS.DBMS_LOGSTDBY”, line 664
ORA-06512: at line 1

snapshot too old: rollback segment number with name “” too small 这种报错十分值得怀疑，一般情况下LOB字段默认采用pctversion=10,在不是特别频繁修改LOB字段的情况下，是不会报错的.何况当时是凌晨3点左右同步的这张表，这种报错更加值得怀疑，使用如下脚本检查lob字段的可用性：

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 prod_data2.PM_SUPPLIER) loop
begin
select PRODUCT_DESCRIPTION into v_lob from prod_data2.PM_SUPPLIER 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
AAAEKcAAMAABhYrAAB
AAAEKcAAMAABhYrAAC
AAAEKcAAMAABhYrAAD
AAAEKcAAMAABhYrAAE
AAAEKcAAMAABhYrAAF
AAAEKcAAMAABhYrAAG
AAAEKcAAMAABhYrAAH
AAAEKcAAMAABhYrAAI
AAAEKcAAMAABhYrAAJ
AAAEKcAAMAABhYrAAK
AAAEKcAAMAABhYrAAL
AAAEKcAAMAABhYrAAM
AAAEKcAAMAABhYrAAN
AAAEKcAAMAABhYrAAO
AAAEKcAAMAABhZrAAA
AAAEKcAAMAABhXSAAG

17 rows selected.

查看任意一条记录 报错：

select PRODUCT_DESCRIPTION from prod_data2.PM_SUPPLIER where rowid=’AAAEKcAAMAABhYrAAA’;

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

这17条记录的NCLOB 都出现了问题，下面修复这些错误的column 信息

update prod_data2.PM_SUPPLIER set PRODUCT_DESCRIPTION=null where rowid in (select CORRUPTED_ROWID from corrupted_data);

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

Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 [ID 787004.1]

For this reason I propose a simple PLSQL block in this new note, which runs faster and does not consume much of the system resources.

LOBDATA was used as the table which should be checked for any potential corrupted LOBs:

1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let’s call it “corrupt_lobs”

SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);

2. Make a desc on the table containing the LOB column:

DESC LOBDATA

Name Null? Type
—————————————– ——–
ID NOT NULL NUMBER
DOCUMENT BLOB

Run the following PLSQL block:

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, document from LOBDATA) loop
begin
n:=dbms_lob.instr(cursor_lob.document,hextoraw(‘889911’));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/

In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

A possible solution would then be to empty the affected LOBs using a statement like:

SQL> update LOBDATA set document = empty_blob()
where rowid in (select corrupt_rowid from corrupt_lobs);

这里我们采用set null的方法 关于LOB 字段 null or empty 参考以下定义：

You can set an internal LOB — that is, a LOB column in a table, or a LOB attribute in an object type defined by you– to be NULL or empty:

Setting an Internal LOB to NULL:

A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.

Setting an Internal LOB to Empty:

By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator which you can use to populate the LOB with data via one of the six programmatic environments, such as OCI or PL/SQL(DBMS_LOB).

这里修改完毕后恢复pctversion为10(之前我们设置为90) 重新同步这张表到3个逻辑备库,很顺利的完成，后续效果待观察。