oracle clob压缩,Oracle 10.2.0.3上压缩CLOB时的Corruption及其对应

服务器上有一Application，保存有30G的CLOB数据。

虽然每周定时将一些CLOB退避到OS，还是发生了Tablespace不够用的现象(使用率约100%)。

经过反复调查发现，CLOB使用的LOBSegment在删除CLOB信息后，并不自动向Tablespace返回空白空间，这就造成其他数据需要空间时出现错误。

所以需要通过SQL整理，使其返回空白的空间。

从10.2开始，Oracle提供了压缩LOBSegment的功能(Shrink Space)。可以执行以下语句。

ALTER TABLE mi.FILE_ITEM_DATA MODIFY LOB (ITEM_DATA)(SHRINK SPACE);

中途出现了ArchiveLog领域不够用等时间(这个整理发生Redolog的)后，经过6个多小时的处理，终于结束了。

可是，很不幸，在进行例行的Expdp(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

经过support.oracle的查证，发现这是10.2.0.3.0的一个Bug。(Bug. 5636728,  参考Oracle文件，编号为833635.1)

SHRINK SPACE会引起LOB数据破损(Corruption)。

如何解决呢？ 如果你保存有破损前的备份，根据833635.1文件，可采取以下对策。

(幸好，我每天都做EXPORT。“Export不是备份的好办法，你应该用RMAN。”好的，那是另外一个问题。)

1. 首先查出破损的数据所在的RowID。

create table corrupted_lob_data (corrupted_rowidrowid);

set concat off

declare

error_1555exception;

pragmaexception_init(error_1555,-1555);

num number;

begin

forcursor_lob in (select rowid r, ITEM_DATA from mi.FILE_ITEM_DATA) loop

begin

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

exception

whenerror_1555 then

insertinto corrupted_lob_data values (cursor_lob.r);

commit;

end;

end loop;

end;

/

2. 做个临时表放置破损的记录

Create table MI.FILE_ITEM_DATA_ANX as

Select * from MI.FILE_ITEM_DATA

where rowid in ( select * from corrupted_lob_data );

3. Export以备后用

bash-3.00$ expdp \"/ as sysdba\"tables=MI.FILE_ITEM_DATA_ANX dumpfile=MI-clob-anx.dmp directory=dpump_dir

4. 传送所需文件到测试机

把破损前的EXPORT文件传送到测试机。

把刚才(步骤3)生成的Export文件传送到测试机。

———————— 在另一台测试机上-----------------

5. 准备测试机上的环境

如果在测试机上没有该用户的空间，那你就首先做Tablespace，然后定义用户。

麻烦？不要紧。

你可以在生产机上通过以下SQL语句获取这些信息。

set long 5000

select dbms_metadata.get_ddl('TABLESPACE',  'MI_SPC') from dual；

这里，MI_SPC是Tablespace名称。

select dbms_metadata.get_ddl('USER',  'MI') from dual；

这里，MI是USER名称。

6. Import步骤4的两个文件。

bash-3.00$ impdp userid=\'/ as sysdba\'dumpfile=MI-1030.dmp directory=dpump_dir schemass=MI

bash-3.00$ impdp userid=\'/ as sysdba\'dumpfile=MI-clob-anx.dmp directory=dpump_dir tables=MI.FILE_ITEM_DATA_ANXtable_exists_action=REPLACE

7. 从破损前的表中恢复CLOB数据

UPDATE MI.FILE_ITEM_DATA_ANX A

SET item_data = (SELECT item_data fromMI.FILE_ITEM_DATA B WHERE B. FILE_ITEM_DATA_ID = A. FILE_ITEM_DATA_ID)

where exists (SELECT 1 from MI.FILE_ITEM_DATA B1WHERE B1. FILE_ITEM_DATA_ID = A. FILE_ITEM_DATA_ID);

可以使用以下SQL语句检查

select * from MI.FILE_ITEM_DATA_ANX where rownum=1；

8. Export以备后用

bash-3.00$ expdp \"/ as sysdba\" tables=MI.FILE_ITEM_DATA_ANXdumpfile=MI-clob-anx.dmp directory=dpump_dir

9. 把刚才(步骤8)生成的Export文件传送(ftp)到测试机。

———————————— 回到生产机-------------------

10. import

bash-3.00$ impdp userid=\'/ as sysdba\'dumpfile=MI-clob-anx.dmp directory=dpump_dir tables=MI.FILE_ITEM_DATA_ANXtable_exists_action=REPLACE

11. 恢复原来表中的CLOB数据

UPDATE MI.FILE_ITEM_DATA B

SET item_data = (SELECT item_data fromMI.FILE_ITEM_DATA_ANX A   WHEREA.FILE_ITEM_DATA_ID = B.FILE_ITEM_DATA_ID)

WHERE EXISTS (Select 1  from MI.FILE_ITEM_DATA_ANX A1 WHEREA1.FILE_ITEM_DATA_ID = B.FILE_ITEM_DATA_ID);

12. 有必要的话，检查是否恢复。

set concat off

set serveroutput on

declare

error_1555exception;

pragmaexception_init(error_1555,-1555);

num number;

begin

forcursor_lob in (select rowid r, ITEM_DATA from mi.FILE_ITEM_DATA) loop

begin

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

exception

whenerror_1555 then

--insert into corrupted_lob_data values (cursor_lob.r);

--commit;

dbms_output.put_line(cursor_lob.r);

end;

end loop;

end;

/

如果没有ROWID输出，那就大功告成了。

后记

这个Shrink Space既然有Bug，如何解决呢？

你可以升级到10.2.0.4。

可是升级是很麻烦的事情。即使是版本号码第4位的变动(比如这里，从3到4)，也要进行周密的用户程序测试。

别忘了墨菲定律：再重要的软件，也至少会有一个bug。

再说了，Oracle也这么建议。

版本升级也那么麻烦？

没有更简单的办法了？

Oracle的Helpdesk说，

可以用Move代替Shrink Space。

(休止)ALTERTABLE mi.FILE_ITEM_DATA MODIFY LOB (ITEM_DATA) (SHRINK SPACE);

(启用) alter table mi.file_item_data movelob(ITEM_DATA) store as (tablespace mi_spc);

这里，tablespace的名称不变，就是说，在相同的表领域移动也可以有压缩效果(测试完毕)。

这样还是不能一劳永逸。还有一个办法就是

给LOB专门做一个Tablespace。

这又是另一个话题。且听下回分解。