使用ODU恢复LOB INDEX损坏的数据

Posted: November 21st, 2011

本文简单介绍如何使用ODU来恢复lob index损坏的数据。

一、测试环境搭建:

下面的测试使用 Oracle 10.2.0.5 for Linux x86版本。

创建测试表空间和表,表的CLOB列采用disable storage in row的方式,在这种情况下,访问lob列必须使用lob index。

create tablespace tbs_test datafile ‘/oradata/xty_stb/tbs_test01.dbf’ size 50m;

conn test/test

SQL>> create table t1 (a number, b clob) lob(b) store as storage (disable storage in row);

Table created.

SQL>> insert into t1 select rownum,rownum || ‘_’ || rpad(object_name,3000,’x’) from dba_objects where rownum<=1000;

1000 rows created.

SQL>> commit;

Commit complete.

SQL>> select segment_name,tablespace_name,index_name from user_lobs where table_name=’T1′;

SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
—————————— —————————— ——————————
STORAGE TBS_TEST SYS_IL0000042149C00002$$

查出lob index的extent信息:

SQL>> select extent_id,file_id,block_id,blocks from dba_extents where owner=’TEST’ and segment_name=’SYS_IL0000042149C00002$$’ order by 1;

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
———- ———- ———- ———-
0 5 25 8
1 5 153 8

关闭数据库:

SQL>> conn / as sysdba
Connected.
SQL>> shutdown immediate

备份好tbs_test表空间的数据文件,然后使用dd将lob index中的块清为全0.

[oracle@xty-standby xty_stb]$ dd if=/dev/zero of=tbs_test01.dbf bs=8192 seek=25 count=8 conv=notrunc
8+0 records in
8+0 records out
65536 bytes (66 kB) copied, 0.00134495 seconds, 48.7 MB/s
[oracle@xty-standby xty_stb]$ dd if=/dev/zero of=tbs_test01.dbf bs=8192 seek=153 count=8 conv=notrunc
8+0 records in
8+0 records out
65536 bytes (66 kB) copied, 0.00134029 seconds, 48.9 MB/s

使用dbv检查数据文件,发现文件已经损坏。

[oracle@xty-standby xty_stb]$ dbv file=tbs_test01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.5.0 – Production on Wed Nov 2 19:48:13 2011

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

DBV-00600: Fatal Error – [21] [2] [0] [0]

重启数据库,查询测试表,已经不能查询出数据。

SQL>> select * from test.t1;
ERROR:
ORA-08103: object no longer exists

至此环境准备完成。

二、使用ODU恢复数据:
修改config.txt配置文件,注意以下几个参数:

use_scanned_lob yes
trim_scanned_blob yes
clob_byte_order big
convert_clob_charset yes
lob_path /odu/data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format dmp

这里需要注意字符集相关的参数,如果charset_name是多字节字符,将convert_clob_charset设为yes,否则应该为no。

配置ODU中的control.txt,只需要系统表空间和要恢复的表及LOB所在的表空间文件;

0 0 0 /oradata/xty_stb/system.260.745630773
0 0 0 /oradata/xty_stb/tbs_test01.dbf

获得license之后,进入ODU:

cd /odu
./odu

获取数据字典

ODU>> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$’s obj# 266
found TABPART$’s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$’s obj# 271
found INDPART$’s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$’s obj# 278
found TABSUBPART$’s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$’s obj# 283
found INDSUBPART$’s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$’s obj# 151
found LOB$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$’s obj# 299
found LOBFRAG$’s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0

扫描LOB列所有的表空间。

ODU>> scan extent tablespace 6

scan extent start: 2011-11-02 19:53:37
scanning extent…
scanning extent finished.
scan extent completed: 2011-11-02 19:53:37

抽取数据:

ODU>> unload table test.t1

Unloading table: T1,object ID: 42149 at 2011-11-02 19:53:49
Unloading segment,storage(Obj#=42149 DataObj#=42149 TS#=6 File#=5 Block#=11 Cluster=0)
1000 rows unloaded
At 2011-11-02 19:53:49

数据抽取成功。与测试时的条数一样,都是1000条。

三、导入数据:

SQL>> conn test/test
Connected.
将原表改名:

SQL>> rename t1 to t2;

Table renamed.

使用imp导入数据:

[oracle@xty-standby data]$ imp test/test file=TEST_T1.dmp full=y

Import: Release 10.2.0.5.0 – Production on Wed Nov 2 19:55:15 2011

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V08.01.07 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST’s objects into TEST
. importing TEST’s objects into TEST
. . importing table “T1” 1000 rows imported
Import terminated successfully without warnings.

在数据库中查询数据:

SQL>> set linesize 170 pagesize 10000
SQL>> set long 1000000
SQL>> select * from t1 where a<=10 order by a;

A B
———- ——————————————————————————–
1 1_ICOL$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

2 2_I_USER1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

内容很多,省略部分输出内容。

SQL>> select a,dbms_lob.getLength(b) from t1 where a<=100 order by a;

A DBMS_LOB.GETLENGTH(B)
———- ———————
1 3002
2 3002
3 3002
4 3002
5 3002
6 3002
7 3002
8 3002
9 3002
10 3003
11 3003
12 3003
13 3003
14 3003
15 3003
16 3003
17 3003
18 3003
19 3003
20 3003
21 3003
22 3003
23 3003
24 3003
25 3003
26 3003
27 3003
28 3003
29 3003
30 3003
31 3003
32 3003
33 3003
34 3003
35 3003
36 3003
37 3003
38 3003
39 3003
40 3003
41 3003
42 3003
43 3003
44 3003
45 3003
46 3003
47 3003
48 3003
49 3003
50 3003
51 3003
52 3003
53 3003
54 3003
55 3003
56 3003
57 3003
58 3003
59 3003
60 3003
61 3003
62 3003
63 3003
64 3003
65 3003
66 3003
67 3003
68 3003
69 3003
70 3003
71 3003
72 3003
73 3003
74 3003
75 3003
76 3003
77 3003
78 3003
79 3003
80 3003
81 3003
82 3003
83 3003
84 3003
85 3003
86 3003
87 3003
88 3003
89 3003
90 3003
91 3003
92 3003
93 3003
94 3003
95 3003
96 3003
97 3003
98 3003
99 3003
100 3004

100 rows selected.

可以看到数据已经全部恢复。