没有非空索引情况下段头块损坏的恢复

前面一篇我们介绍了“ 存在非空索引情况下的段头损坏的恢复” 

今天我们来测试一下，如果没有非空索引的情况下，段头坏了，该怎么去恢复数据

建立测试环境：
SQL> create table goolen as select * from dba_objects;
Table created.

+++goolen表的总行数为72181行，一会恢复的时候需要对比行数
SQL> select count(*) from goolen;
  COUNT(*)
----------
     72181

+++这里我们创建一个非空索引（注：unique索引是可以存储null值的）
SQL> create unique index ind_objd on goolen(object_id);
Index created.

SQL> select count(*) from goolen where object_id is not null;
  COUNT(*)
----------
     72181

SQL> update goolen set object_id='' where rownum <=200;
200 rows updated.

SQL> commit;
Commit complete.

SQL> select count(*) from goolen where object_id is not null;
  COUNT(*)
----------
     71981

+++查看goolen表的段头块为file 1 block 85960
SQL> select header_file,header_block from dba_segments where segment_name='GOOLEN';
HEADER_FILE HEADER_BLOCK
----------- ------------
          1        85960

SQL> alter system flush buffer_cache;
System altered.

+++手工破坏段头块
RMAN> recover datafile 1 block 85960 clear;
Starting recover at 27-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
Finished recover at 27-DEC-13

+++查询报错，有坏块
SQL> select count(*) from goolen;
select count(*) from goolen
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 85960)
ORA-01110: data file 1: '/opt/app/oracle/oradata/goolen/system01.dbf'

SQL> select count(*) from goolen where object_id is not null;
  COUNT(*)
----------
     71981

++++在这里，我们也可以通过索引来抽取出一部分数据，但是索引键值为null的数据都会丢失
+++当然，你也可以通过其他手段来恢复索引键值为null的数据，因为这里只是段头块损坏，数据块还是好的，并没有损坏
SQL> create table  goolen_bak as 
  2      select * from goolen where rowid in 
  3      （select /*+ index(goolen ind_objd)*/ rowid from goolen );
    select * from goolen where rowid in
                  *
ERROR at line 2:
ORA-01578: ORACLE data block corrupted (file # 1, block # 85960)
ORA-01110: data file 1: '/opt/app/oracle/oradata/goolen/system01.dbf'

SQL> create table  goolen_bak as 
  2      select * from goolen where rowid in 
  3      （select /*+ index(goolen ind_objd)*/ rowid from goolen where object_id is not null);

Table created.

+++查看用非空索引恢复出来的数据，只有71981条，goolen表的总数为72181，丢失了200条数据
SQL> select count(*) from goolen_bak;
  COUNT(*)
----------
     71981

+++下面我们使用一个第三方恢复工具odu来恢复
odu的使用介绍详见http://www.oracleodu.com/cn/，这里不在介绍

[oracle@localhost odu]$ ./odu
Oracle Data Unloader trial version 4.1.3
Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.
Web: http://www.oracleodu.com
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size  8192
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path   /asmfile
data_path   data
lob_path    /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charset yes
use_scanned_lob  yes
trim_scanned_blob yes
lob_switch_dir_rows 20000
db_block_checksum yes
db_block_checking yes
rdba_file_bits 10
compatible 10
load config file 'config.txt' successful
loading default asm disk file ......
grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------
load asm disk file 'asmdisk.txt' successful
loading default control file ......
 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   0    1    1  8192    90880 N       0 /opt/app/oracle/oradata/goolen/system01.dbf
   1    2    2  8192    78080 N       0 /opt/app/oracle/oradata/goolen/sysaux01.dbf
   2    3    3  8192     9600 N       0 /opt/app/oracle/oradata/goolen/undotbs01.dbf
   6    6    6  8192     1328 N       0 /opt/app/oracle/oradata/goolen/goolen01.dbf
load control file 'control.txt' successful
loading dictionary data......done
loading scanned data......done
ODU> 
ODU> 
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$'s obj# 576
found TABPART$'s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0
found INDPART$'s obj# 581
found INDPART$'s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0
found TABSUBPART$'s obj# 588
found TABSUBPART$'s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0
found INDSUBPART$'s obj# 593
found INDSUBPART$'s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$'s obj# 80
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$'s obj# 609
found LOBFRAG$'s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0

ODU> scan extent
scan extent start: 2013-12-27 19:23:20
scanning extent...
scanning extent finished.
scan extent completed: 2013-12-27 19:23:22

ODU> unload table sys.goolen object scanned
Using scanned extent.

Unloading table: GOOLEN,object ID: 74198
Unloading segment,storage(Obj#=74198 DataObj#=74198 TS#=0 File#=1 Block#=85960 Cluster=0)
72181 rows unloaded

ODU> exit
ODU> [oracle@localhost odu]$ 

[oracle@localhost odu]$ cd data/
[oracle@localhost data]$ ls
SYS_GOOLEN.ctl  SYS_GOOLEN.sql  SYS_GOOLEN.txt

+++更改一下信息，把数据恢复到scott下面
[oracle@localhost data]$ vim SYS_GOOLEN.ctl 
--
--Generated by ODU,for table "SYS"."GOOLEN"
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'SYS_GOOLEN.txt' "STR X'0a'"
APPEND INTO TABLE "SCOTT"."GOOLEN_ODU"
FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS
(
    "OWNER" CHAR(30),
    "OBJECT_NAME" CHAR(128),
    "SUBOBJECT_NAME" CHAR(30),
    "OBJECT_ID" ,
    "DATA_OBJECT_ID" ,
    "OBJECT_TYPE" CHAR(19),
    "CREATED" DATE "yyyy-mm-dd hh24:mi:ss",
    "LAST_DDL_TIME" DATE "yyyy-mm-dd hh24:mi:ss",
    "TIMESTAMP" CHAR(19),
    "STATUS" CHAR(7),
    "TEMPORARY" CHAR(1),
    "GENERATED" CHAR(1),
    "SECONDARY" CHAR(1),
    "NAMESPACE" ,
    "EDITION_NAME" CHAR(30)
)
                                                                                                                                                                                                
[oracle@localhost data]$ vim SYS_GOOLEN.sql 
CREATE TABLE "SCOTT"."GOOLEN_ODU"
(
    "OWNER" VARCHAR2(30) ,
    "OBJECT_NAME" VARCHAR2(128) ,
    "SUBOBJECT_NAME" VARCHAR2(30) ,
    "OBJECT_ID" NUMBER ,
    "DATA_OBJECT_ID" NUMBER ,
    "OBJECT_TYPE" VARCHAR2(19) ,
    "CREATED" DATE ,
    "LAST_DDL_TIME" DATE ,
    "TIMESTAMP" VARCHAR2(19) ,
    "STATUS" VARCHAR2(7) ,
    "TEMPORARY" VARCHAR2(1) ,
    "GENERATED" VARCHAR2(1) ,
    "SECONDARY" VARCHAR2(1) ,
    "NAMESPACE" NUMBER ,
    "EDITION_NAME" VARCHAR2(30)
);
                                                                      
[oracle@localhost data]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 19:30:37 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @SYS_GOOLEN.sql 
Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

+++sqlldr加载数据
[oracle@localhost data]$ sqlldr scott/tiger control=SYS_GOOLEN.ctl 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Dec 27 19:30:49 2013

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

Commit point reached - logical record count 5302
Commit point reached - logical record count 10604
Commit point reached - logical record count 15906
Commit point reached - logical record count 21208
Commit point reached - logical record count 26510
Commit point reached - logical record count 31812
Commit point reached - logical record count 37114
Commit point reached - logical record count 42416
Commit point reached - logical record count 47718
Commit point reached - logical record count 53020
Commit point reached - logical record count 58322
Commit point reached - logical record count 63624
Commit point reached - logical record count 68926
Commit point reached - logical record count 69271
Commit point reached - logical record count 72181

+++查看数据恢复情况
[oracle@localhost data]$ sqlplus scott/tiger                       
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 19:30:55 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from goolen_odu;

  COUNT(*)
----------
     72181
 
+++一共恢复出72181条数据，数据没有丢失

+++当然，除了odu，还有其他的恢复软件也能达到这个目的