本文共 8709 字，大约阅读时间需要 29 分钟。
DBV(DBVERIFY)是Oracle提供的一个命令行工具，它可以对数据文件物理和逻辑两种一致性检查。但是这个工具不会检查索引记录和数据记录的匹配关系，这种检查必须使用analyze validate structure命令。
这个工具有如下特点：
以只读的方式打开数据文件，在检查过程中不会修改数据文件的内容。
可以在线检查数据文件，而不需要关闭数据库。
不能检查控制文件和日志文件，只能检查数据文件。
这个工具可以检查ASM文件，但数据库必须Open状态，并且需要通过USERID指定用户，比如：dbv file=+DG1/ORCL/datafile/system01.dbf userid=system/sys
在许多UNIX平台下，DBV要求数据文件有扩展名，如果没有可以通过建立链接的方法，然后对链接的方法，然后对链接文件进行操作，比如：ls -n /dev/rdsk/mydevice /tmp/mydevice.dbf
某些平台，DBV工具不能检查超过2GB的文件，如果碰到DBV-100错误，请先检查文件大小，MOS Bug 710888对这个问题有描述。
DBV只会检查数据块的正确性，但不会关系数据块是否属于哪个对象。
对于祼设备建议指定END参数，避免超出数据文件范围。比如：dbv FILE=/dev/rdsk/r1.dbf END=<last_block_number>。可以在v$datafile视图中用bytes字段除以块大小来获得END值。
| 参数 | 含义 | 缺省值 |
| FILE | 要检查的数据文件名 | 没有缺省值 |
| START | 检查起始数据块号 | 数据文件的第一个数据块 |
| END | 检查的最后一个数据块号 | 数据文件的最后一个数据块 |
| BLOCKSIZE | 数据块大小，这个值要和数据库的DB_BLOCK_SIZE参数值一致 | 缺省值8192 |
| LOGFILE | 检查结果日志文件 | 没有缺省值 |
| FEEDBAK | 显示进度 | 0 |
| PARFILE | 参数文件名 | 没有缺省值 |
| USERID | 用户名、密码 | 没有缺省值 |
| SEGMENT_ID | 段ID，参数格式<tsn.segfile.segblock> | 没有缺省值 |
使用示例：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@rhel6 ~]$ dbv file = /u01/app/oracle/oradata/test/users01 .dbf DBVERIFY: Release 11.2.0.1.0 - Production on Mon May 22 16:42:26 2017 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/test/users01 .dbf DBVERIFY - Verification complete Total Pages Examined : 155520 Total Pages Processed (Data) : 144530 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 52 Total Pages Failing (Index): 0 Total Pages Processed (Other): 1248 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 9690 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3559792 (0.3559792) |
这个工具报告使用的是page作为单位，含义和data block相同。从上面的检查结果Total Pages Marked Corrupt : 0可以看出文件没有坏块。
除了检查数据文件，这个工具还允许检查单独的Segment，这时参数值的格式为<tsn.segfile.segblock>
查看对象的tsn,segfile,segblock属性：
| 1 2 3 4 5 6 7 8 | zx@TEST> select t.ts#,s.header_file,s.header_block 2 from v$tablespace t,dba_segments s 3 where s.segment_name= 'T' 4 and t. name =s.tablespace_name; TS# HEADER_FILE HEADER_BLOCK ---------- ----------- ------------ 4 4 45834 |
从上面的查询结果可行参数值为4.4.45834。检查Segment：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@rhel6 ~]$ dbv userid=system /123456 segment_id=4.4.45834 DBVERIFY: Release 11.2.0.1.0 - Production on Mon May 22 20:58:33 2017 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. DBVERIFY - Verification starting : SEGMENT_ID = 4.4.45834 DBVERIFY - Verification complete Total Pages Examined : 8 Total Pages Processed (Data) : 5 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 2 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3518579 (0.3518579) |
下面人为创造一个坏块，用dbv来检查。
创建一个测试表
| 1 2 3 4 5 6 7 8 9 10 11 | zx@TEST> create table bbed (id number, name varchar2(20)) tablespace users; Table created. zx@TEST> insert into bbed values (1, 'zhaoxu' ); 1 row created. zx@TEST> commit ; Commit complete. |
当前数据文件没有坏块
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@rhel6 ~]$ dbv file = /u01/app/oracle/oradata/test/users01 .dbf DBVERIFY: Release 11.2.0.1.0 - Production on Mon May 22 21:03:40 2017 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/test/users01 .dbf DBVERIFY - Verification complete Total Pages Examined : 155520 Total Pages Processed (Data) : 66397 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 52 Total Pages Failing (Index): 0 Total Pages Processed (Other): 88898 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 173 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3764775 (0.3764775) |
获取表在文件中的存储信息
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | zx@TEST> set serveroutput on zx@TEST> declare rfno number; 2 rtype number; 3 ono number; 4 blkno number; 5 rowno number; 6 rid varchar2(30); 7 begin 8 select rowid into rid from bbed; 9 dbms_rowid.rowid_info(ROWID_IN=>rid,RELATIVE_FNO=>rfno,BLOCK_NUMBER=>blkno,ROW_NUMBER=>rowno,ROWID_TYPE=>rtype,OBJECT_NUMBER=>ono); 10 dbms_output.put_line(rfno|| ',' ||blkno|| ',' ||rowno); 11 end ; 12 / 4,45844,0 PL/SQL procedure successfully completed. |
使用bbed修改块信息
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | [oracle@rhel6 bbed]$ bbed parfile=bbed.par Password: BBED: Release 2.0.0.0.0 - Limited Production on Mon May 22 21:17:18 2017 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set dba 4,45844 DBA 0x0100b314 (16823060 4,45844) BBED> find /c zhaoxu File: /u01/app/oracle/oradata/test/users01 .dbf (4) Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314 ------------------------------------------------------------------------ 7a68616f 78750106 5873 <32 bytes per line> BBED> dump /v dba 4,45844 offset 8182 count 32 File: /u01/app/oracle/oradata/test/users01 .dbf (4) Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314 ------------------------------------------------------- 7a68616f 78750106 5873 l zhaoxu..Xs <16 bytes per line> BBED> modify 100 dba 4,45844 Warning: contents of previous BIFILE will be lost. Proceed? (Y /N ) y File: /u01/app/oracle/oradata/test/users01 .dbf (4) Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314 ------------------------------------------------------------------------ 6468616f 78750106 5873 <32 bytes per line> BBED> dump /v dba 4,45844 offset 8182 count 32 File: /u01/app/oracle/oradata/test/users01 .dbf (4) Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314 ------------------------------------------------------- 6468616f 78750106 5873 l dhaoxu..Xs <16 bytes per line> BBED> exit |
再次使用dbv检查文件
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [oracle@rhel6 bbed]$ dbv file = /u01/app/oracle/oradata/test/users01 .dbf DBVERIFY: Release 11.2.0.1.0 - Production on Mon May 22 21:18:46 2017 Copyright (c) 1982, 2009, Oracle and /or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/test/users01 .dbf Page 45844 is marked corrupt Corrupt block relative dba: 0x0100b314 ( file 4, block 45844) Bad check value found during dbv: Data in bad block: type : 6 format : 2 rdba: 0x0100b314 last change scn: 0x0000.00397358 seq : 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail : 0x73580601 check value in block header: 0x7c2d computed block checksum: 0x1e DBVERIFY - Verification complete Total Pages Examined : 155520 Total Pages Processed (Data) : 66396 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 52 Total Pages Failing (Index): 0 Total Pages Processed (Other): 88898 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 173 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 3764775 (0.3764775) |
报告一个坏块，Total Pages Marked Corrupt : 1
再次查询测试表：| 1 2 3 4 5 | sys@TEST> select * from zx.bbed; ID NAME ---------- ------------------------------------------------------------ 1 zhaoxu |
查询正常，因为在buffer_cache中缓存了块，而修改的是文件中的块。两个块现在不一致，清空buffer cache后再次查询测试表。
| 1 2 3 4 5 6 7 8 9 10 | zx@TEST> alter system flush buffer_cache; System altered. zx@TEST> select * from bbed; select * from bbed * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 4, block # 45844) ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf' |
查询报出错误ORA-01578。
使用dbv检查ASM文件中的数据文件，需要指定userid参数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [oracle@rac1 ~]$ dbv file=+DATA/orcl/datafile/users.259.925306091 userid=sys/123456 DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 22 16:48:22 2017 Copyright (c) 1982, 2011, Oracle and / or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/orcl/datafile/users.259.925306091 DBVERIFY - Verification complete Total Pages Examined : 640 Total Pages Processed (Data) : 16 Total Pages Failing (Data) : 0 Total Pages Processed ( Index ): 2 Total Pages Failing ( Index ): 0 Total Pages Processed (Other): 593 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 29 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0) |