在LOB索引丢失/损坏且无备份的情况下恢复ORACLE LOB大对象

某系统由于存储损坏导致一个大对象LOB的LOB INDEX存在坏块corruption，且该系统无任何有效之备份。此时若要恢复对BLOB/CLOB中的数据则无法使用常规的方法，这里可以使用PRM-DUL 工具(http://www.parnassusdata.com/)来恢复其中数据。

场景重现为如下：

select * from BASE_CUSTOMERS_PHOTO where outid='XXXXXX'

select count(*) from BASE_CUSTOMERS_PHOTO 

28050

此时对LOB INDEX 做彻底破坏

SQL> select INDEX_NAME from dba_lobs where table_name='BASE_CUSTOMERS_PHOTO';

INDEX_NAME
------------------------------
SYS_IL0000094755C00003$$



SQL> select header_file,header_block from dba_segments where segment_name='SYS_IL0000094755C00003$$';

HEADER_FILE HEADER_BLOCK
----------- ------------
 6 879546



[oracle@ocp ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 13 15:14:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: GBK2 (DBID=717109662)

RMAN> recover datafile 6 block 879546 clear;

Starting recover at 13-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
Finished recover at 13-JUL-16


[oracle@ocp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 15:14:40 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system flush buffer_cache;

System altered.



SQL> select data_object_id from dba_objects where object_name='SYS_IL0000094755C00003$$';

DATA_OBJECT_ID
--------------
 94757

SQL> desc ind$
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OBJ# NOT NULL NUMBER
 DATAOBJ# NUMBER
 TS# NOT NULL NUMBER
 FILE# NOT NULL NUMBER
 BLOCK# NOT NULL NUMBER
 BO# NOT NULL NUMBER
 INDMETHOD# NOT NULL NUMBER
 COLS NOT NULL NUMBER
 PCTFREE$ NOT NULL NUMBER
 INITRANS NOT NULL NUMBER
 MAXTRANS NOT NULL NUMBER
 PCTTHRES$ NUMBER
 TYPE# NOT NULL NUMBER
 FLAGS NOT NULL NUMBER
 PROPERTY NOT NULL NUMBER
 BLEVEL NUMBER
 LEAFCNT NUMBER
 DISTKEY NUMBER
 LBLKKEY NUMBER
 DBLKKEY NUMBER
 CLUFAC NUMBER
 ANALYZETIME DATE
 SAMPLESIZE NUMBER
 ROWCNT NUMBER
 INTCOLS NOT NULL NUMBER
 DEGREE NUMBER
 INSTANCES NUMBER
 TRUNCCNT NUMBER
 SPARE1 NUMBER
 SPARE2 NUMBER
 SPARE3 NUMBER
 SPARE4 VARCHAR2(1000)
 SPARE5 VARCHAR2(1000)
 SPARE6 DATE





SQL> select flags from ind$ where dataobj#=94757;

FLAGS
----------
 0

SQL> update ind$ set flags=1 where dataobj#=94757;

1 row updated.

SQL> commit;

Commit complete.

SQL> select status from dba_indexes where index_name='SYS_IL0000094755C00003$$';

STATUS
--------
UNUSABLE

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

此时启动PRM-DUL ，字典模式=》选择LOB所在表，选择Data Bridge，选择Base on LOB SCAN

这里使用based on lob scan的含义是基于PRM扫描LOb数据的信息去恢复数据，而不依赖于LOB INDEX的索引结构信息。