oracle数据块损坏后的修复方法

关于oracle数据块损坏的解决方法，相关链接网址在：

http://blog.csdn.net/dlinger/archive/2004/08/24/83911.aspx

http://www.itpub.net/showthread.php?threadid=201766&pagenumber


我的实验步骤：
方法1：
D:/>sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Sep 29 13:51:48 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select name from v$datafile;

NAME
-------------------------------------------------

D:/ORACLE/ORADATA/ORCL/SYSTEM01.DBF
D:/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF
D:/ORACLE/ORADATA/ORCL/INDX01.DBF
D:/ORACLE/ORADATA/ORCL/TOOLS01.DBF
D:/ORACLE/ORADATA/ORCL/USERS01.DBF
D:/ORACLE/ORADATA/ORCL/OEM_REPOSITORY.DBF
D:/ORACLE/ORADATA/ORCL/TEST.DBF
D:/ORACLE/ORADATA/ORCL/TEST1.DBF
D:/ORACLE/ORADATA/ORCL/RMANTS.DBF
D:/ORACLE/ORADATA/ORCL/EXPERT.DBF
D:/ORACLE/ORADATA/ORCL/DBO.DBF
D:/ORACLE/ORADATA/ORCL/NMS.DBF
D:/ORACLE/ORADATA/ORCL/WACOS.DBF
D:/ORACLE/ORADATA/ORCL/RBS.DBF
D:/ORACLE/ORADATA/ORCL/IPAS_USG_DATA.DBF
D:/ORACLE/ORADATA/ORCL/IPAS_USG_IDX.DBF
D:/ORACLE/ORADATA/ORCL/DSF.DBF

17 rows selected.

SQL> create tablespace block datafile 'D:/ORACLE/ORADATA/ORCL/block.dbf' size 2M;

Tablespace created.

SQL> create user chenfeng identified by chenfeng default tablespace block
temporary tablespace temp;

User created.

SQL> alter user chenfeng quota unlimited on block;

User altered.

SQL> grant dba to chenfeng;

Grant succeeded.

SQL> connect chenfeng/chenfeng
Connected.

SQL> create table t as select * from dba_users;

Table created.

SQL> insert into t select * from t;

24 rows created.

SQL> /

48 rows created.

SQL> /

96 rows created.

SQL> /

192 rows created.

SQL> /

384 rows created.

SQL> /

768 rows created.

SQL> /

1536 rows created.

SQL> /

3072 rows created.

SQL> /
insert into t select * from t
*
ERROR at line 1:
ORA-01653: unable to extend table CHENFENG.T by 256 in tablespace BLOCK

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t;

  COUNT(*)
----------
      6144

SQL> create index i_username on t(username);

Index created.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

关闭数据库后用Ultredit编辑block.dbf文件，随便更改几个字符来模拟块损坏。

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  114061244 bytes
Fixed Size                   282556 bytes
Variable Size              79691776 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

SQL> connect chenfeng/chenfeng
Connected.
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 18)
ORA-01110: data file 18: 'D:/ORACLE/ORADATA/ORCL/BLOCK.DBF'

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_block_size                        integer     4096

SQL>host
用dbv命令来检验坏块：

D:/>dbv file=D:/ORACLE/ORADATA/ORCL/BLOCK.DBF blocksize=4096

DBVERIFY: Release 9.0.1.1.1 - Production on Wed Sep 29 15:30:51 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = D:/ORACLE/ORADATA/ORCL/BLOCK.DBF
Page 16 is marked corrupt
***
Corrupt block relative dba: 0x04800010 (file 18, block 16)
Bad check value found during dbv:
Data in bad block -
 type: 30 format: 2 rdba: 0x04800010
 last change scn: 0x0000.00227604 seq: 0x1 flg: 0x04
 consistency value in tail: 0x76041e01
 check value in block header: 0xe4ce, computed block checksum: 0x1000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 18 is marked corrupt
***
Corrupt block relative dba: 0x04800012 (file 18, block 18)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x04800012
 last change scn: 0x0000.00227646 seq: 0x2 flg: 0x04
 consistency value in tail: 0x76460602
 check value in block header: 0xad38, computed block checksum: 0x4870
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 45 is marked corrupt
***
Corrupt block relative dba: 0x0480002d (file 18, block 45)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x0480002d
 last change scn: 0x0000.0022769f seq: 0x1 flg: 0x06
 consistency value in tail: 0x769f0601
 check value in block header: 0x4af9, computed block checksum: 0x2005
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 95 is marked corrupt
***
Corrupt block relative dba: 0x0480005f (file 18, block 95)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x0480005f
 last change scn: 0x0000.0022769f seq: 0x1 flg: 0x06
 consistency value in tail: 0x769f0601
 check value in block header: 0xb609, computed block checksum: 0xc75e
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 97 is marked corrupt
***
Corrupt block relative dba: 0x04800061 (file 18, block 97)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x04800061
 last change scn: 0x0000.0022769f seq: 0x1 flg: 0x06
 consistency value in tail: 0x769f0601
 check value in block header: 0x6e6a, computed block checksum: 0x9b20
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 203 is marked corrupt
***
Corrupt block relative dba: 0x048000cb (file 18, block 203)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x048000cb
 last change scn: 0x0000.0022769b seq: 0x28 flg: 0x04
 consistency value in tail: 0x769b0628
 check value in block header: 0xeb1d, computed block checksum: 0x7520
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 243 is marked corrupt
***
Corrupt block relative dba: 0x048000f3 (file 18, block 243)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x048000f3
 last change scn: 0x0000.0022769b seq: 0x28 flg: 0x04
 consistency value in tail: 0x769b0628
 check value in block header: 0x1174, computed block checksum: 0x110
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 469 is marked corrupt
***
Corrupt block relative dba: 0x048001d5 (file 18, block 469)
Bad check value found during dbv:
Data in bad block -
 type: 0 format: 2 rdba: 0x000001d5
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 consistency value in tail: 0x00000001
 check value in block header: 0x6d5, computed block checksum: 0x1000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

DBVERIFY - Verification complete

Total Pages Examined         : 512
Total Pages Processed (Data) : 249
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 35
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 203
Total Pages Marked Corrupt   : 8
Total Pages Influx           : 0

此时导出数据是不允许的：
D:/>exp chenfeng/chenfeng file=t.dmp tables=t

Export: Release 9.0.1.1.1 - Production on Wed Sep 29 15:34:15 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 18, block # 18)
ORA-01110: data file 18: 'D:/ORACLE/ORADATA/ORCL/BLOCK.DBF'
Export terminated successfully with warnings.

检查损坏的对象，使用以下SQL：
SQL>SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents where file_id = 18 and 18 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ---------------------------

SEGMENT_NAME
------------------------------
BLOCK                          TABLE              CHENFENG
T

设置内部事件，使exp时能跳过这些损坏的block：

SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;

System altered.

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

再导出表试一下：

D:/>exp chenfeng/chenfeng file=t.dmp tables=t

Export: Release 9.0.1.1.1 - Production on Wed Sep 29 15:47:17 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T       6004 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

结果导出了6004行数据。

D:/>exit

SQL> alter system set events='10231 trace name context off';   （关闭设置的event）

 System altered.

SQL> connect  chenfeng/chenfeng
Connected.

SQL> drop table t;

Table dropped.

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

D:/>imp chenfeng/chenfeng file=t.dmp tables=t

Import: Release 9.0.1.1.1 - Production on Wed Sep 29 15:50:53 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

Export file created by EXPORT:V09.00.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing CHENFENG's objects into CHENFENG
. . importing table                            "T"       6004 rows imported
Import terminated successfully without warnings.

SQL> select count(*) from t;

  COUNT(*)
----------
      6004

结果表明丢失了6144 - 6004 = 110 行数据。

方法2(dbms_repair):

D:/>sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Sep 29 16:06:37 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL>connect chenfeng/chenfeng

Connected.

SQL> select count(*) from t;

  COUNT(*)
----------
      6004

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

使用UltraEdit编辑block.dbf，修改几个字符模拟块损坏

SQL> startup
ORACLE instance started.

Total System Global Area  114061244 bytes
Fixed Size                   282556 bytes
Variable Size              79691776 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
Database mounted.
Database opened.

SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 152)
ORA-01110: data file 18: 'D:/ORACLE/ORADATA/ORCL/BLOCK.DBF'

D:/>dbv file=D:/ORACLE/ORADATA/ORCL/BLOCK.DBF blocksize=4096

DBVERIFY: Release 9.0.1.1.1 - Production on Wed Sep 29 16:41:40 2004

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = D:/ORACLE/ORADATA/ORCL/BLOCK.DBF
Page 2 is marked corrupt
***
Corrupt block relative dba: 0x04800002 (file 18, block 2)
Bad check value found during dbv:
Data in bad block -
 type: 29 format: 2 rdba: 0x04800002
 last change scn: 0x0000.0022819b seq: 0x2 flg: 0x04
 consistency value in tail: 0x819b1d02
 check value in block header: 0x1f08, computed block checksum: 0x7000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 4 is marked corrupt
***
Corrupt block relative dba: 0x04800004 (file 18, block 4)
Bad check value found during dbv:
Data in bad block -
 type: 30 format: 2 rdba: 0x04800004
 last change scn: 0x0000.002275f8 seq: 0x1 flg: 0x04
 consistency value in tail: 0x75f81e01
 check value in block header: 0xe4bc, computed block checksum: 0x8000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 16 is marked corrupt
***
Corrupt block relative dba: 0x04800010 (file 18, block 16)
Bad check value found during dbv:
Data in bad block -
 type: 30 format: 2 rdba: 0x04800010
 last change scn: 0x0000.00227604 seq: 0x1 flg: 0x04
 consistency value in tail: 0x76041e01
 check value in block header: 0xe4ce, computed block checksum: 0x1000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 152 is marked corrupt
***
Corrupt block relative dba: 0x04800098 (file 18, block 152)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x04800098
 last change scn: 0x0000.00227fae seq: 0x1 flg: 0x06
 consistency value in tail: 0x7fae0601
 check value in block header: 0xc082, computed block checksum: 0x2f
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 154 is marked corrupt
***
Corrupt block relative dba: 0x0480009a (file 18, block 154)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x0480009a
 last change scn: 0x0000.00227fae seq: 0x1 flg: 0x06
 consistency value in tail: 0x7fae0601
 check value in block header: 0x507c, computed block checksum: 0x7000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 261 is marked corrupt
***
Corrupt block relative dba: 0x04800105 (file 18, block 261)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0x04800105
 last change scn: 0x0000.0022769b seq: 0x28 flg: 0x04
 consistency value in tail: 0x769b0628
 check value in block header: 0x117c, computed block checksum: 0x4050
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 396 is marked corrupt
***
Corrupt block relative dba: 0x0480018c (file 18, block 396)
Bad check value found during dbv:
Data in bad block -
 type: 0 format: 2 rdba: 0x0000018c
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 consistency value in tail: 0x00000001
 check value in block header: 0x68c, computed block checksum: 0x400
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 469 is marked corrupt
***
Corrupt block relative dba: 0x048001d5 (file 18, block 469)
Bad check value found during dbv:
Data in bad block -
 type: 0 format: 2 rdba: 0x000001d5
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 consistency value in tail: 0x00000001
 check value in block header: 0x6d5, computed block checksum: 0x1000
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

 

DBVERIFY - Verification complete

Total Pages Examined         : 512
Total Pages Processed (Data) : 182
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 103
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 202
Total Pages Marked Corrupt   : 8
Total Pages Influx           : 0

使用dbms_repair包
1.创建管理表：
SQL> connect / as sysdba
Connected.

创建一个Repair 表：

SQL>BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/

PL/SQL procedure successfully completed.

SQL> DESC REPAIR_TABLE
 Name                         Null?    Type
 ---------------------------- -------- --------------
 OBJECT_ID                    NOT NULL NUMBER
 TABLESPACE_ID                NOT NULL NUMBER
 RELATIVE_FILE_ID             NOT NULL NUMBER
 BLOCK_ID                     NOT NULL NUMBER
 CORRUPT_TYPE                 NOT NULL NUMBER
 SCHEMA_NAME                  NOT NULL VARCHAR2(30)
 OBJECT_NAME                  NOT NULL VARCHAR2(30)
 BASEOBJECT_NAME                       VARCHAR2(30)
 PARTITION_NAME                        VARCHAR2(30)
 CORRUPT_DESCRIPTION                   VARCHAR2(2000)
 REPAIR_DESCRIPTION                    VARCHAR2(200)
 MARKED_CORRUPT               NOT NULL VARCHAR2(10)
 CHECK_TIMESTAMP              NOT NULL DATE
 FIX_TIMESTAMP                         DATE
 REFORMAT_TIMESTAMP                    DATE

创建一个Orphan Key 表：

SQL>BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'ORPHAN_KEY_TABLE',
     TABLE_TYPE => dbms_repair.orphan_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/
PL/SQL procedure successfully completed.

SQL> DESC ORPHAN_KEY_TABLE

 Name                         Null?    Type
 ---------------------------- -------- -----------------
 SCHEMA_NAME                  NOT NULL VARCHAR2(30)
 INDEX_NAME                   NOT NULL VARCHAR2(30)
 IPART_NAME                            VARCHAR2(30)
 INDEX_ID                     NOT NULL NUMBER
 TABLE_NAME                   NOT NULL VARCHAR2(30)
 PART_NAME                             VARCHAR2(30)
 TABLE_ID                     NOT NULL NUMBER
 KEYROWID                     NOT NULL ROWID
 KEY                          NOT NULL ROWID
 DUMP_TIMESTAMP               NOT NULL DATE

2.检查坏块：dbms_repair.check_object

SQL> SET SERVEROUTPUT ON
     DECLARE num_corrupt INT;
     BEGIN
     num_corrupt :=0;
     DBMS_REPAIR.CHECK_OBJECT(SCHEMA_NAME =>'CHENFENG',OBJECT_NAME =>'T',REPAIR_TABLE_NAME =>'REPAIR_TABLE',CORRUPT_COUNT =>num_corrupt);
     DBMS_OUTPUT.PUT_LINE('number corrupt:' || TO_CHAR(num_corrupt));
     END;
     /

PL/SQL procedure successfully completed.

用创建的REPAIR_TABLE中查看块损坏信息： 
SQL> SELECT object_name, relative_file_id, block_id,marked_corrupt, corrupt_description,repair_description,CHECK_TIMESTAMP from repair_table;

结果可以看到损坏的block的信息，这里得到的信息和我们用dbv看到的一致。
            
3.定位坏块：dbms_repair.fix_corrupt_blocks 

SQL> declare
  cc number;
  begin
  dbms_repair.fix_corrupt_blocks(schema_name => 'CHENFENG',object_name => 'T',fix_count => cc);
  dbms_output.put_line(a => to_char(cc));
  end;
   /

PL/SQL procedure successfully completed.

4.跳过坏块：
我们前面虽然定位了坏块，但是，如果我们访问table：

SQL> select count(*) from chenfeng.t;
select count(*) from chenfeng.t
                              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 152)
ORA-01110: data file 18: 'D:/ORACLE/ORADATA/ORCL/BLOCK.DBF'

还是会得到错误信息。   
这里需要用skip_corrupt_blocks来跳过坏块：

SQL> exec dbms_repair.skip_corrupt_blocks(schema_name =>'CHENFENG',object_name => 'T',flags => 1);

PL/SQL procedure successfully completed.

SQL> select count(*) from chenfeng.t;

  COUNT(*)
----------
      5926

丢失了6004-5926=78行数据。

5.处理index上的无效键值：dump_orphan_keys

SQL>declare
cc number;
begin
dbms_repair.dump_orphan_keys(schema_name =>'CHENFENG',object_name => 'I_USERNAME',object_type => 2,
repair_table_name => 'REPAIR_TABLE',orphan_table_name => 'ORPHAN_TABLE',key_count => CC);
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from ORPHAN_TABLE;

  COUNT(*)
----------
        78

和上面看到的损失的数据行数一致。

根据这个结果考虑是否需要rebuild index.

6.重建freelist：rebuild_freelists

SQL> exec dbms_repair.rebuild_freelists(schema_name =>'CHENFENG',object_name =>'T');

PL/SQL procedure successfully completed.


附录(提供一篇经典案例):
在ALPHA服务器中使用ORACLE8i修复数据块的方法

通常使用ALPHA服务器的共享盘安装数据库的方式有两种：一种是将共享盘作为裸设备方式挂靠，数据库以OPS方式安装；另一种方式是将共享盘做成文件系统挂靠在一台服务器上，将数据库装在共享盘上，进行双机切换时，首先关闭数据库，从这台服务器卸下共享盘，再将其挂靠到另一台服务器上，再次启动数据库。在进行双机切换、意外断电或其它情况下，有时会发生共享盘脏，MOUNT不上的情况，需要使用FSCK对共享盘进行修复。修复完成后，在数据库启动过程中，却又出现"数据块损坏，无法启动数据库"的现象，此时，可以根据不同的数据块损坏类型，检测并修复错误。在此介绍三种使用Oracle8i修复损坏数据块的方法。

一、数据块损坏，错误代码为ORA-01578

ORA-1115 I/O ERROR READING BLOCK

通常后跟ORA-737X错误与操作系统错误（如UNIX中的错误号5）

产生原因：

1． 硬件问题（磁盘控制器问题或磁盘问题）

2． 物理级的数据块损坏（通常由前一原因造成）

3． 处理巨型文件时，后跟错误代码ORA-7371

确定故障原因与恢复的方法：

1． 查看alert.log文件中其它ORA-1115错误的发生情况：

1） 如果指向不同磁盘的文件，则是磁盘控制器的问题，查看V$DATAFILE，有哪些文件位于该控制器下，转到第二步。

2） 如果指向相同磁盘的不同文件，则是磁盘的问题，转到第二步。

3） 如果指向同一个文件，执行以下语句查找文件名：

SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE

FILE_ID=<文件号> AND <块号> BETWEEN BLOCK_ID AND

BLOCK_ID+BLOCKS-1；

其中，文件号与块号是ORA-1115中指出的，如果该查询持续指向某表或索引，

则重建它们即可。

2． 如果文件是SYSTEM表空间，或处于NOARCHIVELOG模式，关闭数据库，转到第四步。

3． 如果数据库处于ARCHIVELOG模式，仍应关闭数据库，如果不能关闭数据库，则将相应的数据文件脱机：ALTER DATABASE DATAFILE '文件名' OFFLINE；

4． 试着将数据文件拷贝到别的磁盘。

5． 如果拷贝失败，则文件将丢失。

6． STARTUP MOUNT；

7． 将数据文件重命名为成功拷贝到别的磁盘的文件名：

ALTER DATABASE RENAME FILE '老路径文件名' TO '新路径文件名'

8． ALTER DATABASE OPEN；

9． RECOVER DATAFILE 文件名；

ALTER DATABASE DATAFILE '文件名' ONLINE；

二、回滚段需要恢复

如果回滚段处于NEED RECOVERY状态，需要执行以下步骤进行恢复：

1． 查看所有联机的表空间与数据文件

2． 在init.ora文件中加入event = "10015 trace name context forever,level 10"，这将生成一个追踪文件，其中含有事务与回滚的信息。

3． 关闭并重新打开数据库。

4． 查看TRACE文件，应有error recovery tx(#,#) object #.TX(#,#)，指出事务信息，其中object #与sys.dba_objects中的object_id相同。

5． 使用以下查询找出正在进行恢复的对象：

SELECT owner,object_name,object_type,status FROM dba_objects WHERE

object_id=<object #>;

6． 必须删除该对象以释放回滚块。

三、检测与修复损坏块的常用方法

（一）使用初始化参数DB_BLOCK_CHECKING与DB_BLOCK_CHECKSUM。

当块改变时，DB_BLOCK_CHECKING对块进行逻辑校验。将防止发生10210 与10211错误。

（二）使用DBMS_REPAIR包，由dbmsrpr.sql与prvtrpr.plb生成该包在特定表中生成损坏块的信息。

1．DBMS_REPAIR.ADMIN_TABLES用于创建与删除存储损坏块的表。其中TABLE_TYPE为：REPAIR_TABLE（表），ORPHAN_TABLE（索引）；ACTION为：CREATE_ACTION（创建表），PURGE_ACTION（删除无关数据），DROP_ACTION（删除表）。例：

dbms_repair.admin_tables('REPAIR_TABLE',DBMS_REPAIR.REPAIR_TABLE,DBMS_REPAIR.CREATE_ACTION,'temp_data');

2．DBMS_REPAIR.CHECK_OBJECT检查表、索引、分区中的块损坏。其中OBJECT_TYPE为：TABLE_OBJECT（表），INDEX_OBJECT（索引）， REPAIR_TABLE_NAME（用于存储损坏块信息的表）。例：

dbms_repair.check_object('ORATRAIN','LOCATIONS',corrupt_count=>:cc);

3．使用以下语句查询块损坏信息：

SELECT object_name, relative_file_no, block_id,
marked_corrupt, corrupt_description, repair_description
FROM repair_table;

4．将块标志为损坏的：dbms_repair.fix_corrupt_blocks('ORATRAIN','LOCATIONS',fix_count=>:fc);

5．跳过损坏块：dbms_repair.skip_corrupt_blocks('ORATRAIN', 'LOCATIONS');

其中OBJECT_TYPE为 ：TABLE_OBJECT（表），CLUSTER_OBJECT（索引）。

6．使用REBUILD_FREELISTS重建损坏的空闲列表：DBMS_REPAIR.rebuild_freelists

7．使用以下方法查找指向损坏块的索引：

（1） 创建存放指向坏块索引的表

（2） dbms_repair.dump_orphan_keys('ORATRAIN','LOC_PK',

orphan_table_name=>'ORPHAN_TAB1',key_count=>:kc);

（3） SELECT index_name, count(*) FROM orphan_key_table WHERE table_name = 'CLASSES' GROUP BY index_name;

（4） 重建具有orphan keys的索引

限制：不能分析Index-organized tables 与 LOB indexes，DUMP_ORPHAN_KEYS不能对bitmap与 function-based indexes操作。

（三）使用SQL命令ANALYZE TABLE|INDEX … VALIDATE STRUCTURE

utlvalid.sql.创建含有损坏块信息的INVALID_ROWS表，ANALYZE TABLE VALIDATE STRUCTURE CASCADE同时校验表与索引。

（四）使用DBVERIFY

DBVERIFY是一个外部工具，所以对数据库影响很小。可用于在将备份文件拷贝回原位置前检验备份文件的完好性，并定位数据块损坏。命令如下：

dbv /users/DB00/u03/data01.dbf start=1 end=500 logfile=dbv.log