oracle rman 检查坏块,Oracle 存储坏块处理方法-基于RMAN实现坏块介质恢复(blockrecover)...

最新推荐文章于 2023-03-02 17:06:31 发布

盒盒惹

最新推荐文章于 2023-03-02 17:06:31 发布

阅读量345

点赞数

文章标签： oracle rman 检查坏块

对于物理损坏的数据块，在有备份的情况下，我们可以通过RMAN块介质恢复(BLOCK MEDIA RECOVERY)功能来完成受损块的恢复，

而不需要恢复整个数据库或所有文件来修复这些少量受损的数据块。但前提条件是你得有一个可用的RMAN备份存在，

因此，无论何时备份就是一切。本篇我们来模拟产生一个坏块，然后使用RMAN实现坏块恢复。

说明：

一般出现坏块的时候，都是业务访问到这个坏块的时候报如下的错误：

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 18, block # 130)

ORA-01110: data file 18: '/ora11gSource/ora11g/tbs_tmp.dbf'

操作：

1 创建用于演示的表空间

create tablespace tbs_tmp datafile '/ora11gSource/ora11g/tbs_tmp.dbf' size 10m autoextend on;

2 基于新的数据文件创建对象tb_tmp

conn scott/tiger;

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

---------- ------------------------------------------------------------

18 /ora11gSource/ora11g/tbs_tmp.dbf

3 表对象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_N HEADER_FILE HEADER_BLOCK BLOCKS

--------- ----------- ------------ ----------

TB_TMP 18 130 1280

4 使用rman对该数据文件进行一次备份

$ $ORACLE_HOME/bin/rman target /

RMAN> backup datafile 18 tag=health;

Starting backup at 12-JUN-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 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=00018 name=/ora11gSource/ora11g/tbs_tmp.dbf

channel ORA_DISK_1: starting piece 1 at 12-JUN-18

channel ORA_DISK_1: finished piece 1 at 12-JUN-18

piece handle=/ora11gSource/zhida/ORA11G/backupset/2018_06_12/o1_mf_nnndf_HEALTH_fkz35h6r_.bkp tag=HEALTH comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 12-JUN-18

5 人为制造一个单块数据块的损坏

使用linux自带的dd命令来损坏单块数据块

ora11g[redora]/home/ora11g>dd of=/ora11gSource/ora11g/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 <

> Corrupted block!

> EOF

0+1 records in

0+1 records out

17 bytes (17 B) copied, 5.9505e-05 seconds, 286 kB/s

6 触发坏块所在对象

清空buffer cache

conn scott/tiger;

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 # 18, block # 130)

ORA-01110: data file 18: '/ora11gSource/ora11g/tbs_tmp.dbf'

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

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

18 130 1 0 CORRUPT

7 使用dbv工具来校验坏块

ora11g[redora]/home/ora11g>dbv file=/ora11gSource/ora11g/tbs_tmp.dbf feedback=1000

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jun 13 10:25:13 2018

DBVERIFY - Verification starting : FILE = /ora11gSource/ora11g/tbs_tmp.dbf

Page 130 is marked corrupt

Corrupt block relative dba: 0x04800082 (file 18, block 130)

Bad header found during dbv:

Data in bad block:

type: 67 format: 7 rdba: 0x65747075

last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21

spare1: 0x72 spare2: 0x72 spare3: 0x0

consistency value in tail: 0x8d8d2301

check value in block header: 0xc50a

block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined : 1536

Total Pages Processed (Data) : 1196

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 154

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 185

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 939342345 (3332.939342345)

8 下面使用blockrecover来恢复坏块

RMAN> blockrecover datafile 18 block 130;

Starting recover at 13-JUN-18

using channel ORA_DISK_1

searching flashback logs for block images until SCN 14311770329033

finished flashback log search, restored 1 blocks

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished recover at 13-JUN-18

9 验证修复效果

再次查询表tb_emp正常

SQL> select count(*) from tb_tmp;

COUNT(*)

----------

72449