ORA-01578: ORACLE data block corrupted

模拟：出现坏块

 

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'

 

[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 table                              A

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_NAME                SEGMENT_TYPE       OWNER

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

SEGMENT_NAME

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

TEST2                          TABLE              LERRY

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 table                              A         88 rows exported

Export terminated successfully without warnings.

 

然後再導入即可，但有數據丟失的！

  

SQL> startup;

ORACLE instance started.

 

Total System Global Area 1653518336 bytes

Fixed Size                  2213896 bytes

Variable Size            1107298296 bytes

Database Buffers          536870912 bytes

Redo Buffers                7135232 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.

 总结：如果出现的坏块不多，又 有备份，可以利用bbed恢复数据块 或是利用rman

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-714130/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24849178/viewspace-714130/