♣题目 部分
在Oracle中，BMR如何恢复坏块？
♣答案部分
如果数据库只有很少的数据块被破坏，那么块介质恢复（Block Media Recovery，BMR）是较好的块恢复方法。BMR只能用于恢复物理损坏（Physical Corruptions），在数据文件联机时即可恢复相关坏块。BMR主要使用BLOCKRECOVER命令进行恢复坏块，该命令有以下三种使用方式：
① 使用“BLOCKRECOVER CORRUPTION LIST;”命令恢复在V$DATABASE_BLOCK_CORRUPTION视图中报告的所有块。
② 使用“BLOCKRECOVER DATAFILE 1 BLOCK 10;”命令恢复单个块，需要指定文件号和块号。
③ 使用“BLOCKRECOVER TABLESPACE TS_USER DBA XXX;”命令恢复某个表空间的坏块，需要指定表空间和数据块地址。
下面给出几个恢复示例：
① 恢复3个数据文件的损坏块：
1RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,23,24 DATAFILE 4 BLOCK 20;
② 从数据文件拷贝中恢复一系列块：
1BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4 TABLESPACE ts_user DBA 4194405,4194409,4194412 from DATAFILECOPY;
③ 从指定的TAG备份中恢复块：
1RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404,4194405 FROM TAG "weekly_backup";
④ 从用于恢复数据到两天以前的备份中还原、恢复SYSTEM表空间中的两个块：
1RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404,4194405 RESTORE UNTIL TIME 'sysdate-2';
⑤ 运行备份验证数据库，修复在V$DATABASE_BLOCK_CORRUPTION中记录的所有损坏块：
1RMAN> BACKUP VALIDATE DATABASE;
2RMAN> BLOCKRECOVER CORRUPTION LIST;
blockrecover模拟修复坏块
（一）创建演示环境
1SQL> select * from v$version where rownum<2;
2
3BANNER
4--------------------------------------------------------------------------------
5Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
6
7--创建用于演示的data file
8SQL> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;
9
10SQL> conn scott/tiger;
11
12--基于新的数据文件创建对象tb_tmp
13SQL> create table tb_tmp tablespace tbs_tmp as select * from dba_objects;
14
15SQL> col file_name format a60
16SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP';
17
18 FILE_ID FILE_NAME
19---------- ------------------------------------------------------------
20 6 /u02/database/usbo/oradata/tbs_tmp.dbf
21
22--表对象tb_tmp上的信息，包含对应的文件信息，头部块，总块数
23SQL> select segment_name , header_file , header_block,blocks
24 2 from dba_segments
25 3 where segment_name = 'TB_TMP' and owner='SCOTT';
26
27SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
28------------------------------ ----------- ------------ ----------
29TB_TMP 6 130 1152
30
31--首先使用rman备份对应的数据文件
32$ $ORACLE_HOME/bin/rman target /
33RMAN> backup datafile 6 tag=health;
34
35Starting backup at 2013/08/28 17:03:15
36allocated channel: ORA_DISK_1
37channel ORA_DISK_1: SID=24 device type=DISK
38channel ORA_DISK_1: starting full datafile backup set
39channel ORA_DISK_1: specifying datafile(s) in backup set
40input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
41channel ORA_DISK_1: starting piece 1 at 2013/08/28 17:03:16
42channel ORA_DISK_1: finished piece 1 at 2013/08/28 17:03:17
43piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH comment=NONE
44channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
45Finished backup at 2013/08/28 17:03:17
46RMAN> exit
（二）单块数据块损坏的恢复处理
1--下面使用了linux自带的dd命令来损坏单块数据块
2[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 <<EOF
3> Corrupted block!
4> EOF
50+1 records in
60+1 records out
717 bytes (17 B) copied, 0.000184519 seconds, 92.1 kB/s
8
9--清空buffer cache
10SQL> alter system flush buffer_cache;
11
12--查询表对相 tb_tmp,收到ORA-01578
13SQL> select count(*) from tb_tmp;
14select count(*) from tb_tmp
15*
16ERROR at line 1:
17ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
18ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
19
20--查询视图v$database_block_corruption，提示有坏块，注意该视图可能不会返回任何数据，如无返回，先执行backup validate
21SQL> select * from v$database_block_corruption;
22
23 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
24---------- ---------- ---------- ------------------ ---------
25 6 129 1 0 CORRUPT
26
27--也可以使用dbv工具来校验坏块，参考: http://blog.csdn.net/robinson_0612/article/details/6530890
28
29--下面使用blockrecover来恢复坏块
30RMAN> blockrecover datafile 6 block 130;
31
32Starting recover at 2013/08/28 17:22:25
33using target database control file instead of recovery catalog
34allocated channel: ORA_DISK_1
35channel ORA_DISK_1: SID=24 device type=DISK
36
37channel ORA_DISK_1: restoring block(s)
38channel ORA_DISK_1: specifying block(s) to restore from backup set
39restoring blocks of datafile 00006
40channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp
41channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH
42channel ORA_DISK_1: restored block(s) from backup piece 1
43channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
44
45starting media recovery
46media recovery complete, elapsed time: 00:00:03
47
48Finished recover at 2013/08/28 17:22:31
49
50--再次查询表tb_emp正常
51SQL> select count(*) from tb_tmp;
52
53 COUNT(*)
54----------
55 72449
（三）多块数据块损坏的恢复处理
1--下面使用linux dd命令对不连续块损坏
2[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=133 <<EOF
3> New corrupted block!
4> EOF
50+1 records in
60+1 records out
721 bytes (21 B) copied, 0.000182835 seconds, 115 kB/s
8[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=143 <<EOF
9> New corrupted block!
10> EOF
110+1 records in
120+1 records out
1321 bytes (21 B) copied, 0.000115527 seconds, 182 kB/s
14[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=153 <<EOF
15> New corrupted block!
16> EOF
170+1 records in
180+1 records out
1921 bytes (21 B) copied, 0.000335781 seconds, 62.5 kB/s
20
21SQL> alter system flush buffer_cache;
22
23--下面提示块133被损坏，注意我们损坏了多块数据块，但查询时，从块号最小的开始提示，如133被修复后还有坏块则继续提示133之后的坏块
24SQL> select count(*) from scott.tb_tmp;
25select count(*) from scott.tb_tmp
26*
27ERROR at line 1:
28ORA-01578: ORACLE data block corrupted (file # 6, block # 133)
29ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
30
31--查询视图v$database_block_corruption无任何记录
32SQL> select * from v$database_block_corruption;
33
34no rows selected
35
36--下面使用backup validate来校验数据文件
37RMAN> backup validate datafile 6;
38
39Starting backup at 2013/08/29 09:42:04
40using target database control file instead of recovery catalog
41allocated channel: ORA_DISK_1
42channel ORA_DISK_1: SID=22 device type=DISK
43channel ORA_DISK_1: starting full datafile backup set
44channel ORA_DISK_1: specifying datafile(s) in backup set
45input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
46channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
47List of Datafiles
48=================
49File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
50---- ------ -------------- ------------ --------------- ----------
516 FAILED 0 223 1408 838489 --字段Status为FAILED
52 File Name: /u02/database/usbo/oradata/tbs_tmp.dbf
53 Block Type Blocks Failing Blocks Processed
54 ---------- -------------- ----------------
55 Data 0 1029
56 Index 0 0
57 Other 3 156 --有3个Blocks Failing
58
59validate found one or more corrupt blocks
60See trace file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_27874.trc for details
61Finished backup at 2013/08/29 09:42:06
62
63--再次查询v$database_block_corruption，表明有3个损坏的块
64SQL> select * from v$database_block_corruption;
65
66 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
67---------- ---------- ---------- ------------------ ---------
68 6 153 1 0 CORRUPT
69 6 143 1 0 CORRUPT
70 6 133 1 0 CORRUPT
71
72--下面直接使用blockrecover corruption list来恢复，如下所有刚刚被校验的坏块都会被恢复
73RMAN> blockrecover corruption list;
74
75Starting recover at 2013/08/29 10:05:24
76using channel ORA_DISK_1
77
78channel ORA_DISK_1: restoring block(s)
79channel ORA_DISK_1: specifying block(s) to restore from backup set
80restoring blocks of datafile 00006
81channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp
82channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH
83channel ORA_DISK_1: restored block(s) from backup piece 1
84channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
85
86starting media recovery
87media recovery complete, elapsed time: 00:00:03
88
89Finished recover at 2013/08/29 10:05:28
90
91--校验结果
92SQL> select count(*) from scott.tb_tmp;
93
94 COUNT(*)
95----------
96 72449
（四）坏块的对象定位与影响
1--下面我们查询块号为163上的对象
2SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,
3 2 dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id
4 3 from scott.tb_tmp where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;
5
6 OBJECT_ID FILE_ID BLOCK_ID OWNER OBJECT_NAME OBJECT_ID
7---------- ---------- ---------- ------------ ------------------------------ ----------
8 74555 6 163 SYS GV_$QUEUEING_MTH 2439
9 74555 6 163 PUBLIC GV$QUEUEING_MTH 2440
10
11--使用上面的方法，我们损块块163，173，此处不再列出
12
13a、对于坏块对象无法进行聚合汇总等操作
14SQL> select count(*) from scott.tb_tmp;
15select count(*) from scott.tb_tmp
16*
17ERROR at line 1:
18ORA-01578: ORACLE data block corrupted (file # 6, block # 163)
19ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
20
21b、对于坏块上的记录无法被查询
22--我们使用基于之前查询到的OBJECT_ID来查询
23SQL> select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440);
24select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440)
25 *
26ERROR at line 1:
27ORA-01578: ORACLE data block corrupted (file # 6, block # 163)
28ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
29
30--如下面的查询，位于损坏块上的数据无法被查询到，但对于未损坏的依旧可以查询。下面的查询时块161上的对象
31SQL> select owner,object_name,object_id from scott.tb_tmp
32 2 where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;
33
34OWNER OBJECT_NAME OBJECT_ID
35------------------------------ ------------------------------ ----------
36PUBLIC GV$RECOVERY_LOG 2285
37SYS GV_$ARCHIVE_GAP 2286
38
39--Author : Robinson Cheng
40--Blog : http://blog.csdn.net/robinson_0612
41
42c、定位受损块所对应的对象
43SQL> run get_obj_name_from_corrupt_block
44 1 SELECT tablespace_name,
45 2 segment_type,
46 3 owner,
47 4 segment_name,
48 5 partition_name
49 6 FROM dba_extents
50 7* WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1
51Enter value for file_id: 6
52Enter value for block_id: 133
53old 7: WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1
54new 7: WHERE file_id = 6 AND 133 BETWEEN block_id AND block_id + blocks - 1
55
56TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
57------------------------------ ------------------ -------------- ----------------- -----------------
58TBS_TMP TABLE SCOTT TB_TMP
59
60d、对于损坏的数据文件，缺省情况下，不能对其进行备份，如下
61RMAN> backup datafile 6 tag='corruption';
62
63Starting backup at 2013/08/29 10:37:32
64using channel ORA_DISK_1
65channel ORA_DISK_1: starting full datafile backup set
66channel ORA_DISK_1: specifying datafile(s) in backup set
67input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
68channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:37:32
69RMAN-00571: ===========================================================
70RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
71RMAN-00571: ===========================================================
72RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/29/2013 10:37:33
73ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/database/usbo/oradata/tbs_tmp.dbf
74
75--需要设定允许损坏块的数量之后才能进行备份
76RMAN> run{
772> set maxcorrupt for datafile 6 to 2;
783> backup datafile 6 tag='corruption';
794> }
80
81executing command: SET MAX CORRUPT
82
83Starting backup at 2013/08/29 10:41:24
84using channel ORA_DISK_1
85channel ORA_DISK_1: starting full datafile backup set
86channel ORA_DISK_1: specifying datafile(s) in backup set
87input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
88channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:41:25
89channel ORA_DISK_1: finished piece 1 at 2013/08/29 10:41:26
90piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_29/o1_mf_nnndf_CORRUPTION_91xf6o18_.bkp tag=CORRUPTION comment=NONE
91channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
92Finished backup at 2013/08/29 10:41:26
93
94--查看备份信息如下，应在修复坏块后重新备份以避免由于保留策略导致先前可用的备份被aged out
95RMAN> list backup summary;
96
97List of Backups
98===============
99Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
100------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1011 B F A DISK 2013/08/28 17:03:17 1 1 NO HEALTH
1023 B F A DISK 2013/08/29 10:41:25 1 1 NO CORRUPTION
作者：小麦苗
