oracle 坏块 恢复,Oracle 11GR2坏块自动恢复功能

Oracle 11GR2坏块自动恢复功能

花了些时间测试了一下坏块11G的坏快自动恢复功能，供大家参考

背景:

数据文件有坏块想必很多dba都碰到过，这时访问到这个块时，就会出现传说中的ORA-01578: ORACLE data block corrupted这个错误，碰到坏块我们就要去手工去做些操作恢复了，既然这个错误这么常见，能不能做到自动恢复呢，在Oracle11g里就提供了这个Automatic Repair of Corrupt Data Blocks(坏块自动恢复)功能

原理：

在配有物理备库的环境下，当主库出现坏块时自动从备库复制数据块过来恢复，前端应用可以透明访问，不再报错。同里备库发生坏块时也可以自动恢复

Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.

条件：

要求有一个物理备库处于real-time query mode，也就是常说的active dataguard，至于怎么搭建active dataguard，不是本文重点，请另行参考。

The physical standby database must be in real-time query mode – Also called active dataguard.

过程:

1.创建一个测试表test2

create table test2 tablespace ts_tools as select * from dba_objects;

insert into test2 select * from test2;--运行几次commit

2.制造坏块

select segment_name,file_id,block_id from dba_extents where segment_name='TEST2' and rownum<5;

SEGMENT_NAME                      FILE_ID   BLOCK_ID

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

TEST2                                   4      12288

TEST2                                   4      12416

TEST2                                   4      12544

TEST2                                   4      12672

接下去用bbed工具把表用到的数据块file_id=4,block_id=12288改写一下，让它变成坏块

BBED> modify 1000 file 1 block 12288

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /arc/oradata/my11g/ts_tools01.dbf (1)

Block: 12288            Offsets:    0 to  511           Dba:0x00403000

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

03e80000 00300001 18ea0300 00000204 29f70000 01000000 c2340000 98e90300

...

BBED> verify

DBVERIFY - Verification starting

FILE = /arc/oradata/my11g/ts_tools01.dbf

BLOCK = 12288

Block 12288 is corrupt

Corrupt block relative dba: 0x01003000 (file 0, block 12288)

用dbv检查也报12288块为corrupt

Page 12288 is marked corrupt

Corrupt block relative dba: 0x01003000 (file 4, block 12288)

Bad header found during dbv:

3.查看备库状态，先看不是real-time query mode情况下的表现

备库：

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> SQL> SQL>

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

--让备库处理普通恢复状态

在主库在执行

SQL> select count(*) from test2;

select count(*) from test2

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 12288)

ORA-01110: data file 4: '/arc/oradata/my11g/ts_tools01.dbf'

--出现了经典的1578错误，因备库不在real-time query mode，不能自动恢复

4.备库为active dataguard时表现

备库:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

主库再次查询:

SQL>  select count(*) from test2;

COUNT(*)

----------

958409

--这时前端完全感觉不到坏块的存在，可以正常访问了

从alert中可以看到这个过程

Corrupt block relative dba: 0x01003000 (file 4, block 12288)

Bad header found during user buffer read

Data in bad block:

type: 3 format: 0 rdba: 0x01003000

last change scn: 0x0000.0003ea18 seq: 0x2 flg: 0x04

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

consistency value in tail: 0xea180602

check value in block header: 0xf729

computed block checksum: 0x4a05

Reading datafile '/arc/oradata/my11g/ts_tools01.dbf' for corruption at rdba: 0x01003000 (file 4, block 12288)

Reread (file 4, block 12288) found same corrupt data

Requesting Auto BMR for (file# 4, block# 12288)  --ABMR进程开始工作

Waiting Auto BMR response for (file# 4, block# 12288)

Auto BMR successful  --成功恢复

有了坏块自动恢复，当出现1578错误时，前端应用就感觉不到这个错误的存在了...