联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在rman中有隐藏的命令clear,可以标记数据块为corrupt,从而实现数据库坏块试验,本篇blog通过dbv,bbed,table select来验证坏块的出现和修复
创建测试表
SQL> create table t_xifenfei as 2 select object_id,object_name from dba_objects where rownum<10; Table created. SQL> select rowid, 2 dbms_rowid.rowid_relative_fno(rowid) rel_fno, 3 dbms_rowid.rowid_block_number(rowid) block 4 from chf.t_xifenfei; ROWID REL_FNO BLOCK ------------------ ---------- ---------- AAAStAAAEAAAACrAAA 4 171 AAAStAAAEAAAACrAAB 4 171 AAAStAAAEAAAACrAAC 4 171 AAAStAAAEAAAACrAAD 4 171 AAAStAAAEAAAACrAAE 4 171 AAAStAAAEAAAACrAAF 4 171 AAAStAAAEAAAACrAAG 4 171 AAAStAAAEAAAACrAAH 4 171 AAAStAAAEAAAACrAAI 4 171 9 rows selected. SQL> alter system checkpoint; System altered.
dbv验证坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf DBVERIFY: Release 11.2.0.3.0 - Production on Sun Jan 20 09:12:16 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 7680 Total Pages Processed (Data) : 3776 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 167 Total Pages Failing (Index): 0 Total Pages Processed (Other): 744 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 2993 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1006717955 (2956.1006717955)
这里创建了t_xifenfei表,数据存储在file 4 block 171中,现在该block一切正常,本试验就是要通过rman来使得该block corrupt,然后通过bbed来修复
bbed查看kcbh
BBED> set block 171
BLOCK# 171
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010000ab
ub4 bas_kcbh @8 0x3c014bfe
ub2 wrp_kcbh @12 0x0b8c
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x6e0c <--重点关注
ub2 spare3_kcbh @18 0x0000
rman标记坏块
RMAN> BLOCKRECOVER DATAFILE 4 block 171 clear; Starting recover at 20-JAN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK Finished recover at 20-JAN-13
dbv再次检查坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf DBVERIFY: Release 11.2.0.3.0 - Production on Sun Jan 20 09:53:16 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf Page 171 is marked corrupt Corrupt block relative dba: 0x010000ab (file 4, block 171) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x010000ab last change scn: 0x0b8c.3c014bfe seq: 0x2 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x4bfe0602 check value in block header: 0x6e0c computed block checksum: 0xb5bc DBVERIFY - Verification complete Total Pages Examined : 7680 Total Pages Processed (Data) : 3775 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 167 Total Pages Failing (Index): 0 Total Pages Processed (Other): 744 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 2993 Total Pages Marked Corrupt : 1 Total Pages Influx : 1 Total Pages Encrypted : 0 Highest block SCN : 1006717955 (2956.1006717955)
尝试查询数据
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 171)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
bbed验证坏块
BBED> set block 171
BLOCK# 171
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x010000ab
ub4 bas_kcbh @8 0x3c014bfe
ub2 wrp_kcbh @12 0x0b8c
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x6e0c <--注意该值未变化
ub2 spare3_kcbh @18 0x0000
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 171
Block 171 is corrupt
Corrupt block relative dba: 0x010000ab (file 0, block 171)
Bad check value found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x010000ab
last change scn: 0x0b8c.3c014bfe seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4bfe0602
check value in block header: 0x6e0c
computed block checksum: 0xb5bc
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
这里通过dbv,bbed,select table都证明rman能够标记block为Corrupt.
这里需要分析:block已经被标记,那证明该块肯定有修改,也就是说chkval_kcbh一定要变化,但是这里没有变化,证明该处异常
bbed修复rman生成坏块
BBED> sum apply Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y Check value for File 0, Block 171: current = 0xdbb0, required = 0xdbb0 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/users01.dbf BLOCK = 171 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
bbed测试坏块已经修复
dbv验证坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf DBVERIFY: Release 11.2.0.3.0 - Production on Sun Jan 20 10:01:46 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 7680 Total Pages Processed (Data) : 3776 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 167 Total Pages Failing (Index): 0 Total Pages Processed (Other): 744 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 2993 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1006717955 (2956.1006717955)
dbv测试坏块也被修复
查询表验证
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
9
数据也可以正常查询,证明rman的坏块问题通过bbed解决
总结说明
1.我们可以通过rman的clear命令来标记坏块(BLOCKRECOVER DATAFILE file# BLOCK block1#, block2#, block3#… CLEAR 😉
2.我们可以通过bbed的sum apply命令来修复该类型坏块