oracle block corrupted,ORA-01578: ORACLE data block corrupted (file # 6, block # 132)

案例一：

SQL>create tablespace test2 datafile '/oradata/ora11/test02.dbf' size 10M;

Tablespace created.

SQL> create table lerry.a as select * from dba_objects;

Table created.

SQL> insert into lerry.a select * from dba_objects where rownum<5000;

4999 rows created.

SQL> c/5000/1000

1* insert into lerry.a select * from dba_objects where rownum<1000

SQL> /

999 rows created.

SQL> /

999 rows created.

SQL> /

insert into lerry.a select * from dba_objects where rownum<1000

*

ERROR at line 1:

ORA-01653: unable to extend table LERRY.A by 128 in tablespace TEST2

SQL> c/1000/500

1* insert into lerry.a select * from dba_objects where rownum<500

SQL>

SQL> /

499 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from lerry.a;

COUNT(*)

----------

79716

SQL> alter system checkpoint;

System altered.

注：利用vi编辑器打开test02.dbf随便更改

SQL> select count(*) from lerry .a;

COUNT(*)

----------

79716

SQL> alter system flush buffer_pool;

alter system flush buffer_pool

*

ERROR at line 1:

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

SQL> alter system flush buffer_cache;

System altered.

SQL>select count(*) from lerry .a;

select count(*) from lerry .a

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 132)

ORA-01110: data file 6: '/oradata/ora11/test02.dbf'

找出坏块影响的对象：

SQL> select *from dba_extentswhere file_id = &file_idand &block_id between block_id AND block_id + blocks - 1;

[oracle@STCS ora11]$ exp lerry/lerry file=a.dmp tables=a

Export: Release 11.2.0.1.0 - Production on Tue Dec 20 14:04:54 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting tableA

EXP-00056: ORACLE error 1578 encountered

ORA-01578: ORACLE data block corrupted (file # 6, block # 132)

ORA-01110: data file 6: '/oradata/ora11/test02.dbf'

Export terminated successfully with warnings.

[oracle@STCS ora11]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 20 14:05:09 2011

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

查找出现坏块的table

SQL> select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=6 and 132 between block_id and block_id+blocks-1;

TABLESPACE_NAMESEGMENT_TYPEOWNER

------------------------------ ------------------ ------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

TEST2TABLELERRY

A

SQL> alter system set events='10231 trace name context forever,level 10';

System altered.

然后导出未损失的坏块

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

[oracle@STCS ora11]$ exp lerry/lerry file=a.dmp tables=a

Export: Release 11.2.0.1.0 - Production on Tue Dec 20 14:06:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting tableA88 rows exported

Export terminated successfully without warnings.

然后再导入即可，但有数据丢失的！

SQL> startup;

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size2213896 bytes

Variable Size1107298296 bytes

Database Buffers536870912 bytes

Redo Buffers7135232 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/oradata/ora11/test02.dbf'

SQL> alter database ora11 datafile '/oradata/ora11/test02.dbf' offline;

alter database ora11 datafile '/oradata/ora11/test02.dbf' offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

SQL>alter database ora11 datafile '/oradata/ora11/test02.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

处理方法二：

SQL> select count(*) from lerry.a;

select count(*) from lerry.a

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6: '/oradata/ora12/lerry01.dbf'

有备份:

$dbv file=’/oradata/ora12/lerry01.dbf’ blocksize=8192

RMAN>blockrecover datafile 6 block 131;

SQL>Select * from v$database_block_corruption; --查找坏块