【DB笔试面试789】在Oracle中，BMR如何恢复坏块?
【DB笔试面试789】在Oracle中，BMR如何恢复坏块?
AiDBA宝典
发布于 2020-05-07 15:31:07
发布于 2020-05-07 15:31:07
题目部分
在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个数据文件的损坏块：
RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12,13 DATAFILE 3 BLOCK 5,23,24 DATAFILE 4 BLOCK 20;
② 从数据文件拷贝中恢复一系列块：
BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4 TABLESPACE ts_user DBA 4194405,4194409,4194412 from DATAFILECOPY;
③ 从指定的TAG备份中恢复块：
RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404,4194405 FROM TAG "weekly_backup";
④ 从用于恢复数据到两天以前的备份中还原、恢复SYSTEM表空间中的两个块：
RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404,4194405 RESTORE UNTIL TIME 'sysdate-2';
⑤ 运行备份验证数据库，修复在V$DATABASE_BLOCK_CORRUPTION中记录的所有损坏块：
RMAN> BACKUP VALIDATE DATABASE;
RMAN> BLOCKRECOVER CORRUPTION LIST;
blockrecover模拟修复坏块
（一）创建演示环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--创建用于演示的data file
SQL> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10m autoextend on;
SQL> conn scott/tiger;
--基于新的数据文件创建对象tb_tmp
SQL> create table tb_tmp tablespace tbs_tmp as select * from dba_objects;
SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP';
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
6 /u02/database/usbo/oradata/tbs_tmp.dbf
--表对象tb_tmp上的信息，包含对应的文件信息，头部块，总块数
SQL> select segment_name , header_file , header_block,blocks
2 from dba_segments
3 where segment_name = 'TB_TMP' and owner='SCOTT';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
------------------------------ ----------- ------------ ----------
TB_TMP 6 130 1152
--首先使用rman备份对应的数据文件
$ $ORACLE_HOME/bin/rman target /
RMAN> backup datafile 6 tag=health;
Starting backup at 2013/08/28 17:03:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
channel ORA_DISK_1: starting piece 1 at 2013/08/28 17:03:16
channel ORA_DISK_1: finished piece 1 at 2013/08/28 17:03:17
piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013/08/28 17:03:17
RMAN> exit
（二）单块数据块损坏的恢复处理
--下面使用了linux自带的dd命令来损坏单块数据块
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 <<EOF
> Corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000184519 seconds, 92.1 kB/s
--清空buffer cache
SQL> alter system flush buffer_cache;
--查询表对相 tb_tmp,收到ORA-01578
SQL> select count(*) from tb_tmp;
select count(*) from tb_tmp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
--查询视图v$database_block_corruption，提示有坏块，注意该视图可能不会返回任何数据，如无返回，先执行backup validate
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 129 1 0 CORRUPT
--也可以使用dbv工具来校验坏块，参考: http://blog.csdn.net/robinson_0612/article/details/6530890
--下面使用blockrecover来恢复坏块
RMAN> blockrecover datafile 6 block 130;
Starting recover at 2013/08/28 17:22:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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 00006
channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp
channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH
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/08/28 17:22:31
--再次查询表tb_emp正常
SQL> select count(*) from tb_tmp;
COUNT(*)
----------
72449
（三）多块数据块损坏的恢复处理
--下面使用linux dd命令对不连续块损坏
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=133 <<EOF
> New corrupted block!
> EOF
0+1 records in
0+1 records out
21 bytes (21 B) copied, 0.000182835 seconds, 115 kB/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=143 <<EOF
> New corrupted block!
> EOF
0+1 records in
0+1 records out
21 bytes (21 B) copied, 0.000115527 seconds, 182 kB/s
[oracle@linux1 ~]$ dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=153 <<EOF
> New corrupted block!
> EOF
0+1 records in
0+1 records out
21 bytes (21 B) copied, 0.000335781 seconds, 62.5 kB/s
SQL> alter system flush buffer_cache;
--下面提示块133被损坏，注意我们损坏了多块数据块，但查询时，从块号最小的开始提示，如133被修复后还有坏块则继续提示133之后的坏块
SQL> select count(*) from scott.tb_tmp;
select count(*) from scott.tb_tmp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 133)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
--查询视图v$database_block_corruption无任何记录
SQL> select * from v$database_block_corruption;
no rows selected
--下面使用backup validate来校验数据文件
RMAN> backup validate datafile 6;
Starting backup at 2013/08/29 09:42:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 223 1408 838489 --字段Status为FAILED
File Name: /u02/database/usbo/oradata/tbs_tmp.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1029
Index 0 0
Other 3 156 --有3个Blocks Failing
validate found one or more corrupt blocks
See trace file /u02/database/usbo/diag/rdbms/usbo/usbo/trace/usbo_ora_27874.trc for details
Finished backup at 2013/08/29 09:42:06
--再次查询v$database_block_corruption，表明有3个损坏的块
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 153 1 0 CORRUPT
6 143 1 0 CORRUPT
6 133 1 0 CORRUPT
--下面直接使用blockrecover corruption list来恢复，如下所有刚刚被校验的坏块都会被恢复
RMAN> blockrecover corruption list;
Starting recover at 2013/08/29 10:05:24
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp
channel ORA_DISK_1: piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_28/o1_mf_nnndf_HEALTH_91vh6ntb_.bkp tag=HEALTH
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/08/29 10:05:28
--校验结果
SQL> select count(*) from scott.tb_tmp;
COUNT(*)
----------
72449
（四）坏块的对象定位与影响
--下面我们查询块号为163上的对象
SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,
2 dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id
3 from scott.tb_tmp where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;
OBJECT_ID FILE_ID BLOCK_ID OWNER OBJECT_NAME OBJECT_ID
---------- ---------- ---------- ------------ ------------------------------ ----------
74555 6 163 SYS GV_$QUEUEING_MTH 2439
74555 6 163 PUBLIC GV$QUEUEING_MTH 2440
--使用上面的方法，我们损块块163，173，此处不再列出
a、对于坏块对象无法进行聚合汇总等操作
SQL> select count(*) from scott.tb_tmp;
select count(*) from scott.tb_tmp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 163)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
b、对于坏块上的记录无法被查询
--我们使用基于之前查询到的OBJECT_ID来查询
SQL> select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440);
select owner,object_name,object_id from scott.tb_tmp where object_id in(2439,2440)
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 163)
ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf'
--如下面的查询，位于损坏块上的数据无法被查询到，但对于未损坏的依旧可以查询。下面的查询时块161上的对象
SQL> select owner,object_name,object_id from scott.tb_tmp
2 where dbms_rowid.rowid_block_number(rowid)=161 and rownum<3;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
PUBLIC GV$RECOVERY_LOG 2285
SYS GV_$ARCHIVE_GAP 2286
--Author : Robinson Cheng
--Blog : http://blog.csdn.net/robinson_0612
c、定位受损块所对应的对象
SQL> run get_obj_name_from_corrupt_block
1 SELECT tablespace_name,
2 segment_type,
3 owner,
4 segment_name,
5 partition_name
6 FROM dba_extents
7* WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1
Enter value for file_id: 6
Enter value for block_id: 133
old 7: WHERE file_id = &file_id AND &block_id BETWEEN block_id AND block_id + blocks - 1
new 7: WHERE file_id = 6 AND 133 BETWEEN block_id AND block_id + blocks - 1
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------ -------------- ----------------- -----------------
TBS_TMP TABLE SCOTT TB_TMP
d、对于损坏的数据文件，缺省情况下，不能对其进行备份，如下
RMAN> backup datafile 6 tag='corruption';
Starting backup at 2013/08/29 10:37:32
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=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:37:32
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/29/2013 10:37:33
ORA-19566: exceeded limit of 0 corrupt blocks for file /u02/database/usbo/oradata/tbs_tmp.dbf
--需要设定允许损坏块的数量之后才能进行备份
RMAN> run{
2> set maxcorrupt for datafile 6 to 2;
3> backup datafile 6 tag='corruption';
4> }
executing command: SET MAX CORRUPT
Starting backup at 2013/08/29 10:41:24
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=00006 name=/u02/database/usbo/oradata/tbs_tmp.dbf
channel ORA_DISK_1: starting piece 1 at 2013/08/29 10:41:25
channel ORA_DISK_1: finished piece 1 at 2013/08/29 10:41:26
piece handle=/u02/database/usbo/fr_area/USBO/backupset/2013_08_29/o1_mf_nnndf_CORRUPTION_91xf6o18_.bkp tag=CORRUPTION comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013/08/29 10:41:26
--查看备份信息如下，应在修复坏块后重新备份以避免由于保留策略导致先前可用的备份被aged out
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1 B F A DISK 2013/08/28 17:03:17 1 1 NO HEALTH
3 B F A DISK 2013/08/29 10:41:25 1 1 NO CORRUPTION
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：230161599、618766405
● 微信：lhrbestxh
● 微信公众号：DB宝
● 提供Oracle OCP、OCM、高可用（rac+dg+ogg）和MySQL最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-04-29，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
