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

淡淡的烟草味﹌ 2022-06-12 03:28 483阅读 0赞

测试环境：

数据库：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

SQL  
\--首先使用rman备份对应的数据文件   
 RMAN> backup datafile 8 format '/u01/datafile\_8\_\_%d\_%T\_%s\_%p' tag=helath;

RMAN>

System altered

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

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;

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>