Use ODU to recover lob data when the associated lob index is corrupted

Posted: November 21st, 2011

This article introduces how to use ODU to recover lob data when the associated lob index is corrupted.

 

1. Building the test environment:

The following test using the Oracle 10.2.0.5 for Linux x86.

 

Create a test tablespace and a test table, the CLOB column of the table using disable storage in row, in that situation, Oracle must use the lob index to access the lob column.

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$$

 

Find the extent information of the above lob index:

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

 

Shutdown the database:

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

 

Backup the datafile of the tablespace tbs_test, and then use dd to clear all the blocks of the above lob index to zero:

[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

 

 

After the above dd operation, we use dbv to check the datafile and found that it is corrupted.

[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]

 

 

We restart the database and query the table t1, we found that we can not see the right result, Oracle reports ORA-08103.

 

SQL> select * from test.t1;

ERROR:

ORA-08103: object no longer exists

 

Now the test environment is ready.

 

2. Use ODU to recover the lob data:

Modify the ODU configuration file config.txt, please pay attention to these parameters:

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

 

Note the relevant parameters of database character set. If the database character set is multi-byte character set, then the CLOB column data in the database will be stored as UNICODE character set, but for a single-byte character set, CLOB column data will be stored with the same single-byte database character set. It means that if your database character set is multi-byte character set, the parameter CONVERT_CLOB_CHARSET should be set to yes, otherwise should be no.

 

Config the ODU control file control.txt, you just need to config system tablespace and the tablespace where the lob data resides:

         0          0          0 /oradata/xty_stb/system.260.745630773

         0          0          0 /oradata/xty_stb/tbs_test01.dbf

 

After you get the ODU software license, you can enter into the ODU:

cd /odu

./odu

 

Unload the data dictionary:

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

 

Scan the tablespace where the lob column resides:

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

 

After the above scan operation, you can recover the lob data:

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

 

You can see that the unload operation operates successfully and the number of unloaded rows is exactly 1000.

 

3. Import the recovered data:

SQL> conn test/test

Connected.

 

Rename the original table t1 to t2:

SQL> rename t1 to t2;

 

Table renamed.

 

 

Use imp to import the recovered data:

[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.

 

Now we can query the table t1 normally again:

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

Many contents, omitting part content of the output.

 

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.

 

You can see that the lob data has been completely recovered.