Buddy Yuan的个人技术博客

在11g Active DataGuard中,如果我们使用real time的功能,我们就可以使用Automatic Block Media Repair的特性来修复主库的坏块. 因为当你使用了real time的功能后,你对主库的更新会迅速传到备库,此时主库出现坏块,我们可以相反的从备库上把已经applying的block在传回给主库.下面我就来测试一下这个案例.这儿我参考了mos上的ABMR: How to test Automatic Block Recover Feature [ID 1266059.1]
1.首先我需要开启real time的功能.

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.

2.建测试数据,通过rowid来查询块号和文件号

-----------from primary database 
SQL> connect scott/tiger
Connected. 
SQL> create table test as select * from dba_objects; 

Table created.

-----------from standby database 
SQL> select count(1) from scott.test; 

 COUNT(1)
 ----------
 71904

real time query几乎只有1秒钟的延迟,数据已经在备库可以进行查询了.

SQL> select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno
2 from test where rownum<10; 

ROWID BLOCKNO FNO
------------------ ---------- ----------
AAAR7kAAEAAAACrAAA 171 4
AAAR7kAAEAAAACrAAB 171 4
AAAR7kAAEAAAACrAAC 171 4
AAAR7kAAEAAAACrAAD 171 4
AAAR7kAAEAAAACrAAE 171 4
AAAR7kAAEAAAACrAAF 171 4
AAAR7kAAEAAAACrAAG 171 4
AAAR7kAAEAAAACrAAH 171 4
AAAR7kAAEAAAACrAAI 171 4 

9 rows selected.

3.使用dd命令制造坏块

[oracle@dg1 ~]$ dd if=/dev/zero of=/oracle/app/oracle/oradata/dg1/users01.dbf count=1 seek=171 bs=8192 conv=notrunc 
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.8e-05 seconds, 141 MB/s

4.使用RMAN做校验

[oracle@dg1 ~]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 1 16:25:25 2012 

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 

connected to target database: DG1 (DBID=1769729736) 

RMAN> backup check logical validate datafile 4; 

Starting backup at 01-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 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=00004 name=/oracle/app/oracle/oradata/dg1/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 231 1440 1014981
File Name: /oracle/app/oracle/oradata/dg1/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1038
Index 0 2
Other 1 169 

validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/dg1/dg1/trace/dg1_ora_4308.trc for details
Finished backup at 01-AUG-12 

RMAN> exit

[oracle@dg1 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 1 16:27:17 2012 

Copyright (c) 1982, 2009, Oracle. All rights reserved. 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> select *from V$DATABASE_BLOCK_CORRUPTION; 

 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 171 1 0 ALL ZERO

5.Flush Buffer Cache,运行查询,ABMR自动从Standby恢复数据

SQL> ALTER SYSTEM Flushing buffer cache;
SQL> select count(*) from scott.test where dbms_rowid.rowid_block_number(rowid)=171 and dbms_rowid.rowid_relative_fno(rowid)=4; 

 COUNT(*)
----------
88

此时我们可以看到数据能够查询出来,那么看一下我们的后台alert日志,记录了下列内容.

Wed Aug 01 16:34:25 2012
ALTER SYSTEM: Flushing buffer cache
Wed Aug 01 16:35:09 2012
Hex dump of (file 4, block 171) in trace file /oracle/app/oracle/diag/rdbms/dg1/dg1/trace/dg1_ora_4310.trc
Corrupt block relative dba: 0x010000ab (file 4, block 171)
Completely zero block found during multiblock buffer read
Reading datafile '/oracle/app/oracle/oradata/dg1/users01.dbf' for corruption at rdba: 0x010000ab (file 4, block 171)
Reread (file 4, block 171) found same corrupt data
Starting background process ABMR
Wed Aug 01 16:35:09 2012
ABMR started with pid=55, OS id=4364
Auto BMR service is active.
Requesting Auto BMR for (file# 4, block# 171)
Waiting Auto BMR response for (file# 4, block# 171)
Auto BMR successful

参考文档:ABMR: How to test Automatic Block Recover Feature [ID 1266059.1]
附录：