oracle故障恢复实验(3)-单个数据块损坏和多个数据块损坏(RMAN工具)

对于物理损坏的数据块，可以通过RMAN块介质恢复(BLOCK MEDIA RECOVERY)功能来完成受损块的恢复，而不需要恢复整个数据库或所有文件来修复这些少量受损的数据块。

1. 创建测试表空间、测试表

SQL>create tablespace TEST datafile '/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf' size 50M;

SQL>create table test as select * from dba_objects;

SQL>alter table test move tablespace TEST;

2.查询表对象test文件信息，头部块，文件头信息

SQL> select segment_name , header_file , header_block,blocks from dba_segments where segment_name = 'TEST';

SEGMENT_NAME            HEADER_FILE            HEADER_BLOCK              BLOCKS
--------------------------------------------------------------------------------- ----------- ------------ ----------
TEST                                 14                             130                                  1280

SQL>
SQL> select count(*) from test;

COUNT(*)
----------
86427

3.对数据文件备份

表空间test对应的file_id=14，备份其数据文件

RMAN> backup datafile 14 format '/backup/datafile14_%d_%T_%s_%p' tag=helath;

4.模拟单块数据文件数据块损坏

dd命令

[root@oracle11g ~]# dd if=/u01/app/oracle/oradata/ORACLE11G/datafile/dmhs01.dbf of=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf bs=8192 conv=notrunc seek=130

38401+0 records in
38401+0 records out
314580992 bytes (315 MB) copied, 1.59271 s, 198 MB/s

[root@oracle11g ~]# dd if=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf bs=8192 count=1 skip=130 | hexdump -C
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000558563 s, 14.7 MB/s
00000000 00 a2 00 00 00 00 c0 ff 00 00 00 00 00 00 00 00 |................|
00000010 66 6c 00 00 00 20 00 00 00 96 00 00 7d 7c 7b 7a |fl... ......}|{z|
00000020 a0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00002000

5.查询test表对象

--清空buffer cache
SQL> alter system flush buffer_cache;
System altered
--查询表对象test收到 ORA-01578
SQL> select count(*) from mytest;

select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 130)
ORA-01110: data file 14: '/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf'

6.查询视图v$database_block_corruption

该视图可能不会返回任何数据，如无返回，先执行backup validate验证数据备份坏块等信息

SQL> select * from v$database_block_corruption;

no rows selected

7.执行backup validate

RMAN> backup validate datafile 14 format '/backup/datafile14_%d_%T_%s_%p';

or

RMAN> validate datafile 14 block 130;----提示有坏块

Starting validate at 25-JUN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00014 name=/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
14 FAILED 0 0 1 0
File Name: /u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 1 1

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/oracle11g/oracle11g/trace/oracle11g_ora_5356.trc for details
Finished validate at 25-JUN-24

再查询视图v$database_block_corruption提示有坏块

SQL> select * from v$database_block_corruption;

8.使用blockrecover来恢复坏块

RMAN> blockrecover datafile 14 block 130;

修复完成

9.查询test表对象的数据量

发现报错

SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 131)
ORA-01110: data file 14: '/u01/app/oracle/oradata/ORACLE11G/datafile/test01.dbf'

130号块已经被修复，但是新的报错提示131号块为corrupted

查询v$database_block_corruption视图也提示还有其他坏块(可能是dd命令的问题，导致单块数据块损坏变成多块损坏)

SQL> select * from v$database_block_corruption;

10.多块修复

使用blockrecover corruption list来恢复，所有被校验的坏块都会被恢复

RMAN> blockrecover corruption list;

11.验证表对象test数据量

自此数据块损坏问题修复完成~~~

下一次实验使用oracle自带的DBMS_REPAIR包来进行修复~~~