求各位高手赐教。关于ORACLE数据坏块 解决方法
现在一生存环境的oracle数据库有报坏块错误，查询到是一个表的坏块。DB没有备份，非归档模式。本人没有处理过坏块的经验，我想先做下备份，担心在处理过程中出现其他什么问题对现有数据有影响。请问下，1.有坏块的备份能否恢复到做备份时的状态？2.请各位提供些详细的坏块处理方法与经验。感谢~~1.create tablespace qjh datafile '/u01/app/oracle/oradata/orcl/qjh01.dbf' size 1m;
2.create user qjh identified by qjh default tablespace qjh;
3.grant connect to qjh;
grant select any table to qjh;
4.alter user qjh quota unlimited on qjh;
5.sqlplus qjh/qjh
6.create table test as select * from scott.emp;
insert into test select * from test;
7.shutdown immediate;
8.vim /u01/app/oracle/oradata/orcl/qjh01.dbf
9.sqlplus / as sysdba
10.startup
11.conn qjh/qjh
SQL> select * from test;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 5, block # 20)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/qjh01.dbf'
no rows selected
12. dbv file='/u01/app/oracle/oradata/orcl/qjh01.dbf' blocksize=8192
DBVERIFY - Verification complete
Total Pages Examined : 128
Total Pages Processed (Data) : 6
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 109
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 4480104 (0.4480104)
=======================================
恢复步骤，有两种方法
首先介绍第一种：使用RMAN 恢复，好处是数据文件可以保持在线状态（前提是坏块之前有做RMAN备份，支持full或level 0的备份集）
可已修复指定块：(适用于有少量坏块的情况）
BLOCKRECOVER DATAFILE 5 BLOCK 20,21,...;
如果有大量坏块：
RMAN> BACKUP VALIDATE DATABASE;（backup validate tablespace qjh;|backup validate datafile 5
BLOCKRECOVER CORRUPTION LIST;----#可以一起运行也可以分开运行
Starting backup at 2013-03-29 12:18:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/qjh01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 0 128 4480104
File Name: /u01/app/oracle/oradata/orcl/qjh01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 7
Index 0 0
Other 108 121
详细信息可以查看 v$database_block_corruption;
SQL> select file#,block#,blocks,CORRUPTION_TYPE from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_TYPE
---------- ---------- ---------- ---------------------------
5 21 108 CORRUPT
5 20 1 FRACTURED
RMAN> blockrecover corruption list;
Starting recover at 2013-03-29 12:34:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_29/o1_mf_nnndf_TAG20130329T111552_8ob1v8f5_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_29/o1_mf_nnndf_TAG20130329T111552_8ob1v8f5_.bkp tag=TAG20130329T111552
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2013-03-29 12:35:09
================================================
第二种恢复方法就是开启 10231事件，全表扫描的时候会跳过坏块，用EXP/EXPDP导出相应的表,再把原来的表删除，把数据导入进去：
SQL> ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10' ;
System altered.
然后导出表中的数据（不过这种恢复有的时候会丢数据，因为坏块中的数据已经跳过了）
exp qjh/qjh tables=test file=test.dmp log=exp_test.log
删除表：
drop table test purge;
导入数据：
imp qjh/qjh tables=test file=test.dmp log=imp_test.log
关闭10231事件：
SQL> ALTER SYSTEM SET EVENTS='10231 trace name context off' ;
System altered.
有坏块的情况下，备份会失败
没有备份，非归档模式的恢复，先select * from dba_extents where file_id=file and block between block_id and block_id+blocks-1;查看坏块的对象
index坏块就重建，表有坏块DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('USER','TABLE');然后建立一张相同的表，再rename
undo表空间有坏块就添加新的数据文件后重新建立
我已经模拟了坏块，然后通过event 10231事件 完成了exp&imp 表T，丢了部分数据，能正常查询该表T。现在用dbv datafile和v$database_block_courruption仍然能检查到坏块，是什么原因？
SQL> select owner,segment_name,block_id,blocks from dba_extents where owner='TEST';
OWNER SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ -------------------- ---------- ----------
TEST TT 8 8
~~~~~~~~~~~~~~~~~~~
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 14 115 0 CORRUPT
6 13 1 0 FRACTURED
以上结果blocks怎么有115个，而dba_extents的blocks显示该表只有一个extent，8个block？
使用dbv 检查datafile 也有提示存在坏块：Total Pages Marked Corrupt : 113
页:
[1]