[0424]11GR2 Automatic block media repair

[20140424]11GR2 Automatic block media repair.txt

摘要:PacktPub.Oracle.Data.Guard.11gR2.Administration.Beginners.Guide.Jun.2013.pdf P388

In Oracle 11gR2, when Actve Data Guard is being used with Real-Time Apply, if a physical corrupton is detected on the
primary database, Oracle will automatcally try to repair the corrupton using the non-corrupted block on the standby
database. This operaton is also valid in the opposite directon, which means standby database corrupton will be repaired
using the data block on the primary database. A notfcaton will be printed in the alert log about the automatc block
media repair operaton in the meantme; this repair operaton is completely transparent to database users.

We can also manually repair a corrupted data block with the RMAN command's RECOVER BLOCKcommand. By default, this
command will try to use an Active Data Guard physical standby database if it exists. In order to exclude the standby
database as a source to repair corruption, we must use the EXCLUDE STANDBYoption of this command.

--自己做一个另类的测试:
1.建立测试环境:
DGMGRL> show configuration
Configuration - study
  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> show parameter db_block_
NAME               TYPE     VALUE
------------------ -------- ---------
db_block_buffers   integer  0
db_block_checking  string   FALSE
db_block_checksum  string   TYPICAL
db_block_size      integer  8192
--这些参数是缺省设置.

SCOTT@test> select rowid,a.* from dept1 a where deptno=60;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 MMMM           DDDDDx

SCOTT@test> @lookup_rowid AAAcC1AAIAAAACHAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    114869          8        135          0 8,135                alter system dump datafile 8 block 135 ;

SCOTT@test> select name from v$datafile where file#=8;
NAME
--------------------------------------------------
/u01/app/oracle11g/oradata/test/test01.dbf

2.关闭数据库,安全起见,做一个冷备份,修改该块信息:

SYS@test> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

$ cp /u01/app/oracle11g/oradata/test/test01.dbf /data/testtest/

-- 135*8192=1105920,使用bvi修改该块信息,将'MMMM'替换成'AAAA'.
$ bvi -b 1105920 -s 8192 /u01/app/oracle11g/oradata/test/test01.dbf

3.启动数据库:
--由于修改信息,导致检查和不一致,看看是否会报错.
$ dbv file=/u01/app/oracle11g/oradata/test/test01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Apr 24 09:14:33 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 8192
Total Pages Processed (Data) : 1507
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 225
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 6447
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3269838040 (0.3269838040)
--昏!这样修改dbv竟然检查不出来.我以前没有想到这种情况.

SCOTT@test> select rowid,a.* from dept1 a where deptno=60;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 AAAA           DDDDDx

--可以发现并没有报错.而实际上检查和应该不一致.如果这个时候修改这条记录的Loc信息:
SCOTT@test> update scott.dept1 set loc='DDDDDy' where deptno=60;
1 row updated.

SCOTT@test> commit ;
Commit complete.

--检查testdg库看看.
SYS@testdg> select rowid,a.* from scott.dept1 a where deptno=60;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 MMMM           DDDDDy

-- 很明显修改后两边的信息不一致.这种逻辑损坏,oracle并不监测在缺省的参数下db_block_*参数.
-- 使用rman检查逻辑数据库的验证检查.
backup validate check logical datafile 8;
RMAN> backup validate check logical datafile 8;

Starting backup at 2014-04-24 09:35:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 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=00008 name=/u01/app/oracle11g/oradata/test/test01.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
---- ------ -------------- ------------ --------------- ----------
8    OK     0              6447         8192            3269839017
  File Name: /u01/app/oracle11g/oradata/test/test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1507
  Index      0              13
  Other      0              225

Finished backup at 2014-04-24 09:36:07

--可以发现错误已经不存在,因为我修改信息时已经将正确的检查和写入块中.
--将两边信息修改一致,再来测试看看.

SCOTT@test> update scott.dept1 set dname='NNNN' where deptno=60;
1 row updated.

SCOTT@test> commit ;
Commit complete.

4.再来重复测试:
--关闭数据库
-- 135*8192=1105920,使用bvi修改该块信息,将'NNNN'替换成'AAAA'.
$ bvi -b 1105920 -s 8192 /u01/app/oracle11g/oradata/test/test01.dbf

-- 启动到mount模式.
SYS@test> startup mount
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size            1006636496 bytes
Database Buffers          587202560 bytes
Redo Buffers                7344128 bytes
Database mounted.

RMAN> backup validate check logical datafile 8;

Starting backup at 2014-04-24 09:42:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=206 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=00008 name=/u01/app/oracle11g/oradata/test/test01.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
---- ------ -------------- ------------ --------------- ----------
8    OK     0              6447         8192            3269839703
  File Name: /u01/app/oracle11g/oradata/test/test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1507
  Index      0              13
  Other      0              225

Finished backup at 2014-04-24 09:42:51
--昏,逻辑检查也不能发现问题.
SYS@test> alter system set db_block_checksum=full scope=memory;
System altered.

SYS@test> alter system set db_block_checking=full scope=memory;
System altered.

SYS@test> alter database open;
Database altered.

SCOTT@test> select rowid,a.* from dept1 a where deptno=60;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 AAAA           DDDDDy

--昏这样也不报错误吗?

5.现在修改两边一致:
SCOTT@test> select rowid,a.* from dept1 a where deptno=60;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 MMMM           DDDDDz

--关闭数据库.
--这次使用bvi写入一些垃圾数据到该数据块头部中.

$ dbv file=/u01/app/oracle11g/oradata/test/test01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Apr 24 10:17:24 2014

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
Page 135 is influx - most likely media corrupt
Corrupt block relative dba: 0x02000087 (file 8, block 135)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xd1b90601
check value in block header: 0x0
block checksum disabled

--启动执行,等1小会,显示正常:
SCOTT@test> select rowid,a.* from dept1 a where deptno=60;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 MMMM           DDDDDz

--看看alert*.log文件:

Hex dump of (file 8, block 135) in trace file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_9395_127_0_0_1.trc
Corrupt block relative dba: 0x02000087 (file 8, block 135)
Fractured block found during multiblock buffer read
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xd1b90601
check value in block header: 0x0
block checksum disabled
Reading datafile '/u01/app/oracle11g/oradata/test/test01.dbf' for corruption at rdba: 0x02000087 (file 8, block 135)
Reread (file 8, block 135) found same corrupt data (no logical check)
Starting background process ABMR
Thu Apr 24 10:18:35 2014
ABMR started with pid=36, OS id=9397
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 8, block# 135)
Thu Apr 24 10:18:35 2014
Automatic block media recovery successful for (file# 8, block# 135)
Automatic block media recovery successful for (file# 8, block# 135)
WARNING: AutoBMR fixed mismatched on-disk block 0 with in-mem rdba 2000087.

6.重复测试,使用rman:
--关闭数据库.
--这次使用bvi写入一些垃圾数据到该数据块头部中.
$ dbv file=/u01/app/oracle11g/oradata/test/test01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Thu Apr 24 10:24:14 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
Page 135 is influx - most likely media corrupt
Corrupt block relative dba: 0x02000087 (file 8, block 135)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xd41e0601
check value in block header: 0x0
block checksum disabled

RMAN> list backup ;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
--我没有做任何备份!

RMAN> recover datafile 8 block 135;
Starting recover at 2014-04-24 10:28:37
using channel ORA_DISK_1
finished standby search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2014-04-24 10:28:41

--从提示也可以看出来,恢复是从standby取出数据.
SCOTT@test> select rowid,a.* from dept1 a where deptno=60;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAAcC1AAIAAAACHAAA         60 MMMM           DDDDDz

总结:
测试有点困惑,如果这样造成的逻辑损坏,oracle无法查看吗?不知那位知道我的测试问题在那里....

时间: 2024-07-31 01:45:28

[0424]11GR2 Automatic block media repair的相关文章

[20140424]oracle的逻辑坏块.txt

[20140424]oracle的逻辑坏块.txt 今天上午本来想做一个11GR2的Automatic block media repair,链接如下:http://blog.itpub.net/267265/viewspace-1148315/ 但是我遇到一个奇怪的问题,检查和的计算问题: SYS@test> @ver BANNER --------------------------------------------------------------------------------

Oracle后台进程及其作用简介

Oracle后台进程及其作用简介 This background process is available starting with Oracle Database 11g Release 2 (11.2.0.2). describes Oracle Database background processes. In this context, a background process is defined as any process that is listed in V$PROCESS 

[20160816]11G dataguard坏块修复.txt

[20160816]11G dataguard坏块修复.txt --11GR2 不仅仅支持在备库在只读的情况下,日志应用(ACTIVE Data Guard),还提供主备库的坏块修复.自己以前也做过相关测试, --我记得上次测试的仅仅是主库数据块损坏,没有测试备库的数据块损坏.补充一些测试: 1.环境: SYS@test> @ ver1 PORT_STRING                    VERSION        BANNER ---------------------------

Oracle 11g 的 VKTM 进程 - virtual keeper of time

链接:http://www.eygle.com/archives/2012/06/oracle_11g_vktm.html 在Oracle Database 11g中,VKTM是一个新增的后台进程.这个进程的含义是: VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every

oracle中11gR2 dataguard 备库文件损坏解决案例

某客户的一套11gR2 dataguard环境出现异常,检查发现是备库出现文件损坏,且无法正常情况,已经超过1个多月没同步了. 我们先来看下备库的日志: .......省略部分内容 See Note 411.1 at My Oracle Support for error and packaging details. Slave exiting with ORA-600 exception Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjn

Oracle corrupt block(坏块) 详解

转自:http://blog.csdn.net/tianlesoftware/article/details/5024966 一. 坏块说明 1.1 相关链接        在看坏块之前,先看几个相关的链接,在后面的说明中,会用到链接中的一些内容.   ORA-600 各个参数含义说明 http://blog.csdn.net/tianlesoftware/article/details/6645809   Oracle 不同故障的恢复方案 http://blog.csdn.net/tianle

oracle的ORA-01143错误:cannot disable media recovery

今天一台服务器的开启了归档,导致文件系统撑满了,由于我们是开发库,没必要开启归档,所以就把数据库改为非归档模式,但是在修改的时候碰到ORA-01143: cannot disable media recovery - file 20 needs media recovery错误.后来将数据文件恢复后才将数据库修改成功. 下面为操作过程 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance sh

【OH】Glossary Oracle词汇表(中)

Glossary [OH]Glossary Oracle词汇表(中) Oracle? Database Net Services Administrator's Guide 11g Release 2 (11.2) E41945-02 Glossary ● access control list (ACL) The group of access directives that you define. The directives grant levels of access to specif

Oracle 组件系列小结

Oracle 组件系列小结 本文整理自网络,原文地址:http://blog.csdn.net/tianlesoftware/article/details/7339998#t3 Oracle 组件的信息可以通过v$option 和 dba_registry 查看: --查看v$option: V$OPTION displaysOracle Database options and features. Typically, although not always, optionsmust be