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> 没有缺省值
使用示例:
- [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属性:
- 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:
- [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来检查。
创建一个测试表
- 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.
当前数据文件没有坏块
- [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)
获取表在文件中的存储信息
- 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修改块信息
- [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检查文件
- [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
再次查询测试表:
- sys@TEST>select * from zx.bbed;
- ID NAME
- ---------- ------------------------------------------------------------
- 1 zhaoxu
查询正常,因为在buffer_cache中缓存了块,而修改的是文件中的块。两个块现在不一致,清空buffer cache后再次查询测试表。
- 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参数
- [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)