lob data recover
构建包含lob数据的表
SQL> desc lobtab Name Null? Type ----------------------------------------- -------- ---------------------------- NUMBER_CONTENT NUMBER(10) VARCHAR2_CONTENT VARCHAR2(100) DATE_CONTENT DATE CLOB_CONTENT CLOB BLOB_CONTENT BLOB [oracle@DB01 ~]$ ls blob_01.lob blob_02.lob clob_01.lob clob_02.lob lob_data_test.txt lobtab.sql [oracle@DB01 ~]$ cat blob_01.lob luda's blob data 01 [oracle@DB01 ~]$ cat blob_02.lob luda's blob data 02 [oracle@DB01 ~]$ cat clob_02.lob luda's clob data 02 [oracle@DB01 ~]$ cat clob_01.lob luda's clob data 01 [oracle@DB01 ~]$ cat lobtab.ctl LOAD DATA INFILE 'lob_data_test.txt' INTO TABLE lobtab FIELDS TERMINATED BY ',' (number_content CHAR(10), varchar2_content CHAR(100), date_content DATE "DD-MON-YYYY" ":date_content", clob_filename FILLER CHAR(100), clob_content LOBFILE(clob_filename) TERMINATED BY EOF, blob_filename FILLER CHAR(100), blob_content LOBFILE(blob_filename) TERMINATED BY EOF) --将lob数据导入到表中 [oracle@DB01 ~]$ sqlldr luda/luda control=lobtab.ctl log=lob_tab.log bad=lob_tab.bad SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:40 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 2 [oracle@DB01 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:48 2015 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options --查询lob数据 SQL> select * from luda.lobtab 2 ; SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length 2 FROM luda.lobtab; NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH -------------- ---------------- ------------ -------------------- ----------- 1 one 01-JAN-2006 luda's clob data 01 20 2 two 02-JAN-2006 luda's clob data 02 20
模拟truncate lob数据
SQL> truncate table luda.lobtab
加载mdata
loading default config....... load config file 'config.txt' successful loading default asm disk file ...... start loading default control file ...... load control file 'control.txt' successful load BOOTSTRAP$ success. load TAB$ success. load COL$ success. load OBJ$ success. load USER$ success. load PROPS$ success. load TABPART$ success. load TABSUBPART$ success. load IND$ success.
通过scan的方式扫描该表所在tablespace
MDATA>scan extent tablespace 4 datafile 4 scan extent start: Thu Jan 15 00:54:43 CST 2015 scanning extent... scanning extent finished. scan extent completed: Thu Jan 15 00:54:43 CST 2015
通过unload恢复出被truncate的lob数据
MDATA>unload table luda.lobtab object auto Auto mode truncated table. unload schema:LUDA;tab:LOBTAB . unloading (index organized) table LOB010001e3 Preparing lob metadata from lob index Reading LOB010001e3.dat 0 entries loaded and sorted 0 entries . unloading (index organized) table LOB010001d3 Preparing lob metadata from lob index Reading LOB010001d3.dat 0 entries loaded and sorted 0 entries tabName:LUDA.LOBTAB,dic_obj:com.olm.b.H@4e94a4,getFileid:4,getBlockid:451 2 rows unloaded
切换到恢复数据的存放目录
[oracle@DB01 data]$ pwd /oracle/mdata/data
通过sqlload加载恢复出的数据
[oracle@DB01 data]$ sqlldr luda/luda control=LUDA.LOBTAB.ctl log=LUDA.LOBTAB.log SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:57:15 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Commit point reached - logical record count 2
加载完成后验证恢复后的lob数据
SQL> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length 2 FROM luda.lobtab; NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH -------------- ---------------- ------------ -------------------- ----------- 1 one 01-JAN-2006 luda's clob data 01 20 2 two 02-JAN-2006 luda's clob data 02 20
恢复lob的关键在于lob index所在的块的保存，如果lob index有坏块则会造成恢复失败，需要额外处理。
