案例一:
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; --查找坏块