分类: Oracle
2010-07-15 14:23:37
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错误时,前端应用就感觉不到这个错误的存在了...