Oracle 11g 基于RMAN实现坏块介质恢复(blockrecover)

最新推荐文章于 2024-06-20 17:24:57 发布

雨花石~

最新推荐文章于 2024-06-20 17:24:57 发布

阅读量1.2k

点赞数 1

分类专栏： Oracle学习笔记

本文链接：https://blog.csdn.net/shiyu1157758655/article/details/75105963

版权

Oracle学习笔记专栏收录该内容

198 篇文章 22 订阅

订阅专栏

测试环境：

数据库：11.2.0.4

OS:Centos 6.5

对于物理损坏的数据块，我们可以通过RMAN块介质恢复(BLOCK MEDIA RECOVERY)功能来完成受损块的恢复，而不需要恢复整个数据库或所有文件来修复这些少量受损的数据块。恢复整个数据库或数据文件那不是大炮用来打蚊子，有点不值得！但前提条件是你得有一个可用的RMAN备份存在，因此，无论何时备份就是一切。本文演示了产生坏块即使用RMAN实现坏块恢复的全过程。

1.创建演示环境
--创建用于演示的data file
SQL> create tablespace mytest datafile '/data1/orcl/datafile/mytest01.dbf' size 20M autoextend on;

Tablespace created.

--基于新的数据文件创建对象mytest
SQL> conn test/test;
Connected.
SQL> create table mytest tablespace mytest as select * from dba_objects;

Table created.
SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTEST';

FILE_ID FILE_NAME
---------- ------------------------------------------------------------
8 /data1/orcl/datafile/mytest01.dbf

SQL>
--表对象mytest上的信息，包含对应的文件信息，头部块，总块数
SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'MYTEST' and owner='TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
MYTEST
8 130 1408

SQL
--首先使用rman备份对应的数据文件
RMAN> backup datafile 8 format '/u01/datafile_8__%d_%T_%s_%p' tag=helath;

Starting backup at 2017/07/13 16:20:33
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=00008 name=/data1/orcl/datafile/mytest01.dbf
channel ORA_DISK_1: starting piece 1 at 2017/07/13 16:20:34
channel ORA_DISK_1: finished piece 1 at 2017/07/13 16:20:37
piece handle=/u01/datafile_8__ORCL_20170713_68_1 tag=HELATH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2017/07/13 16:20:37

Starting Control File and SPFILE Autobackup at 2017/07/13 16:20:37
piece handle=/u01/control_c-1459318340-20170713-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2017/07/13 16:20:40

RMAN>

2、单块数据块损坏的恢复处理
--下面使用了linux自带的dd命令来损坏单块数据块
orcl:/data1/orcl/datafile@db1>dd of=/data1/orcl/datafile/mytest01.dbf bs=8192 conv=notrunc seek=130 <<EOF
> Corrupted block!
> EOF
记录了0+1 的读入
记录了0+1 的写出
18字节(18 B)已复制，0.0002196 秒，82.0 kB/秒
--清空buffer cache
SQL> alter system flush buffer_cache;

System altered

--查询表对相 mytest收到 ORA-01578
SQL> select count(*) from mytest;

select count(*) from mytest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 130)
ORA-01110: data file 8: '/data1/orcl/datafile/mytest01.dbf'

--查询视图v$database_block_corruption，提示有坏块，注意该视图可能不会返回任何数据，如无返回，先执行backup validate
SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
8 130 1 0 CORRUPT

--也可以使用dbv工具来校验坏块，参考:http://blog.csdn.net/shiyu1157758655/article/details/56666350

--下面使用blockrecover来恢复坏块
RMAN> blockrecover datafile 8 block 130;

Starting recover at 2017/07/13 17:12:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
searching flashback logs for block images until SCN 12073138
finished flashback log search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:02:08

Finished recover at 2017/07/13 17:16:05

--再次查询表mytest正常

SQL> select count(*) from mytest;

COUNT(*)
----------
95338

SQL>
3、多块数据块损坏的恢复处理
--下面使用linux dd命令对不连续块损坏
orcl:/home/oracle@db1>dd of=/data1/orcl/datafile/mytest01.dbf bs=8192 conv=notrunc seek=133 <<EOF
> New corrupted block!
> EOF
记录了0+1 的读入
记录了0+1 的写出
21字节(21 B)已复制，0.0510806 秒，0.4 kB/秒
orcl:/home/oracle@db1>dd of=/data1/orcl/datafile/mytest01.dbf bs=8192 conv=notrunc seek=143 <<EOF
> New corrupted block!
> EOF
记录了0+1 的读入
记录了0+1 的写出
21字节(21 B)已复制，0.0639114 秒，0.3 kB/秒
orcl:/home/oracle@db1>dd of=/data1/orcl/datafile/mytest01.dbf bs=8192 conv=notrunc seek=182 <<EOF
> New corrupted block!
> EOF
记录了0+1 的读入
记录了0+1 的写出
23字节(23 B)已复制，0.178737 秒，0.1 kB/秒
orcl:/home/oracle@db1>
SQL> alter system flush buffer_cache;

System altered
-下面提示块133被损坏，注意我们损坏了多块数据块，但查询时，从块号最小的开始提示，如133被修复后还有坏块则继续提示133之后的坏块
SQL> select count(*) from mytest;
select count(*) from mytest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 133)
ORA-01110: data file 8: '/data1/orcl/datafile/mytest01.dbf'
--查询视图v$database_block_corruption

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#   BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
8   133        1    0 CORRUPT
8   143        1    0 CORRUPT
8   182        1    0 CORRUPT

SQL>
--下面使用backup validate来校验数据文件
RMAN> backup validate datafile 8 format '/u01/data_file8_%d_%T_%s_%p';

Starting backup at 2017/07/14 08:06:05
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 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=00008 name=/data1/orcl/datafile/mytest01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    FAILED 0              1051         2560            12070446
File Name: /data1/orcl/datafile/mytest01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              1349
Index      0              0
Other      3              160

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2370.trc for details
Finished backup at 2017/07/14 08:06:16

RMAN>
--再次查询v$database_block_corruption，表明有3个损坏的块

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#   BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
8   133        1    0 CORRUPT
8   143        1    0 CORRUPT
8   182        1    0 CORRUPT
--下面直接使用blockrecover corruption list来恢复，如下所有刚刚被校验的坏块都会被恢复
RMAN> blockrecover corruption list;

Starting recover at 2017/07/14 13:04:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
searching flashback logs for block images until SCN 12073138
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece /u01/datafile_8__ORCL_20170713_68_1
channel ORA_DISK_1: piece handle=/u01/datafile_8__ORCL_20170713_68_1 tag=HELATH
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:35

Finished recover at 2017/07/14 13:09:11

--校验结果
SQL> select count(*) from mytest;

COUNT(*)
----------
95338

SQL>

4、后记
a、对于受损的数据块，仅仅坏块上的数据无法被查询或读取，其余正常块的数据依旧可以使用。
b、对于受损的表对象进行聚合等相关运算时收到错误提示，因为坏块上的数据无法被统计。如果你聚合的是索引列，索引未损坏的情形则可正常返回。
c、可以基于RMAN可用的备份文件实现块介质恢复，其数据文件无需offline，开销最小，影响最小。
d、对于多个数据块的损坏，先执行backup validate校验数据库或相应的数据文件以便标记受损的坏块后，填充v$database_block_corruption以及后续恢复。
e、对于使用backup validate 校验后的情形，坏块恢复时可以直接使用blockrecover corruption list一次性恢复所有的坏块。
f、缺省情况下，存在坏块的数据文件无法成功备份，也会导致自动备份脚本失败。

select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,owner,object_name,object_id
from mytest where dbms_rowid.rowid_block_number(rowid)=163 and rownum<=2;

测试环境：
数据库：11.2.0.4
OS：Centos6.5

1.创建演示环境
--创建用于演示的data file
SQL> create tablespace mytest datafile '/data1/orcl/datafile/mytest01.dbf' size 20M autoextend on;

Tablespace created.

--基于新的数据文件创建对象mytest
SQL> conn test/test;
Connected.
SQL> create table mytest tablespace mytest as select * from dba_objects;

Table created.
SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTEST';

FILE_ID FILE_NAME
---------- ------------------------------------------------------------
8 /data1/orcl/datafile/mytest01.dbf

SEGMENT_NAME
--------------------------------------------------------------------------------
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
MYTEST
8 130 1408

SQL
--首先使用rman备份对应的数据文件
RMAN> backup datafile 8 format '/u01/datafile_8__%d_%T_%s_%p' tag=helath;

Starting Control File and SPFILE Autobackup at 2017/07/13 16:20:37
piece handle=/u01/control_c-1459318340-20170713-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2017/07/13 16:20:40

RMAN>

System altered

--查询表对相 mytest收到 ORA-01578
SQL> select count(*) from mytest;

select count(*) from mytest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 130)
ORA-01110: data file 8: '/data1/orcl/datafile/mytest01.dbf'

--查询视图v$database_block_corruption，提示有坏块，注意该视图可能不会返回任何数据，如无返回，先执行backup validate
SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
8 130 1 0 CORRUPT

--也可以使用dbv工具来校验坏块，参考:http://blog.csdn.net/shiyu1157758655/article/details/56666350

--下面使用blockrecover来恢复坏块
RMAN> blockrecover datafile 8 block 130;

starting media recovery
media recovery complete, elapsed time: 00:02:08

Finished recover at 2017/07/13 17:16:05

--再次查询表mytest正常

SQL> select count(*) from mytest;

COUNT(*)
----------
95338

SQL> select * from v$database_block_corruption;

SQL>
--下面使用backup validate来校验数据文件
RMAN> backup validate datafile 8 format '/u01/data_file8_%d_%T_%s_%p';

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2370.trc for details
Finished backup at 2017/07/14 08:06:16

RMAN>
--再次查询v$database_block_corruption，表明有3个损坏的块

SQL> select * from v$database_block_corruption;