DBV是一個外部指令，能夠執行實體資料結構的完整性檢查。DBV隻能檢查可緩存管理的塊(資料塊)，是以隻能用于資料檔案，不支援控制檔案和重做日志檔案的塊檢查。DBV使用于offline或者online的資料檔案，也可也驗證備份檔案，但是備份檔案隻能是rman的copy指令或者作業系統的cp(win下是copy)指令備份的資料檔案。控制檔案和重做日志檔案的檢查可以用DBMS_HM包完成。
DBV有兩種指令行接口，一是驗證資料檔案的資料塊，二是驗證段。下面分别做測試：
一、驗證資料檔案的資料塊
在這種模式下資料庫可處在打開或關閉狀态。
[email protected]>create table scott.test as select * from dba_objects where rownum <=100;
Table created.
---查出test表所在的資料檔案和開始的塊id以及塊數量
[email protected]>select a.file_id,a.block_id,a.blocks,b.name
2 from dba_extents a,v$datafile b
3 where a.file_id=b.file# and a.owner='SCOTT' and a.segment_name='TEST';
FILE_ID BLOCK_ID BLOCKS NAME
---------- ---------- ---------- ---------------------------------------------
4 520 8 /u01/app/oracle/oradata/orcl/users01.dbf
---查出表中記錄所在的塊
[email protected]>select distinct dbms_rowid.rowid_block_number(rowid) from scott.test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
523
524
---破壞有記錄的資料塊
[[email protected] ~]$ dd of=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=524 <<EOF
> abcdefghicklmn
> EOF
0+1 records in
0+1 records out
15 bytes (15 B) copied, 3.1499e-05 s, 476 kB/s
---執行dbv指令檢查資料檔案
[[email protected] ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 12 14:45:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf
Page 524 is marked corrupt ---資料塊524标記為壞塊
Corrupt block relative dba: 0x0100020c (file 4, block 524)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6369 seq: 0xa flg: 0x04
spare1: 0x63 spare2: 0x64 spare3: 0x0
consistency value in tail: 0x65790602
check value in block header: 0x2f5b
computed block checksum: 0xad06
DBVERIFY - Verification complete
Total Pages Examined : 1920
Total Pages Processed (Data) : 1161
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 398
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 321
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1205628 (0.1205628)
Pages--表示資料塊
Total Pages Examined --表示檔案中的資料塊總數量
Total Pages Processed--表示已檢查資料塊的數量
Total Pages Failing--表示檢查失敗的資料塊數量
Total Pages Marked Corrupt--表示資料塊已損壞
Total Pages Influx--表示同一時間正在讀和寫的資料塊數量。如果資料庫是打開狀态，當DBV運作時多次讀資料塊得到一個一緻的映像，但是因為資料庫是打開的，可能同一資料塊在讀的時候又有寫入的動作，DBV不能得到一個一緻的資料塊映像
DBV除了能檢查有内容的資料塊，可以檢查空資料塊，這充分證明了dbv是從實體層面上驗證資料塊的完整性。
[[email protected] ~]$ dd of=/u01/app/oracle/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=526 <<EOF
> abcdefghijklmn
> EOF
0+1 records in
0+1 records out
15 bytes (15 B) copied, 3.0521e-05 s, 491 kB/s
[[email protected] ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 12 15:11:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf
Page 524 is marked corrupt ---資料塊524标記為壞塊
Corrupt block relative dba: 0x0100020c (file 4, block 524)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6369 seq: 0xa flg: 0x04
spare1: 0x63 spare2: 0x64 spare3: 0x0
consistency value in tail: 0x65790602
check value in block header: 0x2f5b
computed block checksum: 0xad06
Page 526 is marked corrupt ---空資料塊526也标記為壞塊
Corrupt block relative dba: 0x0100020e (file 4, block 526)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6a69 seq: 0xa flg: 0x04
spare1: 0x63 spare2: 0x64 spare3: 0x0
consistency value in tail: 0x64b30601
check value in block header: 0x54c2
computed block checksum: 0xa5cd
DBVERIFY - Verification complete
Total Pages Examined : 1920
Total Pages Processed (Data) : 1160
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 398
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 321
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1205628 (0.1205628)
DBV可以檢查ASM管理的資料檔案，但是資料庫要求是open狀态，同時要使用選項USERID，例如：
dbv userid=system/oracle file=+DATAa/orcl/datafile/users01.dbf blocksize=8192
DBV驗證rman的backup as copy指令備份的資料檔案，例如：
RMAN> backup as copy datafile 5;
Starting backup at 12-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8h22vb6m_.dbf tag=TAG20130112T150617 RECID=2 STAMP=804524793
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 12-JAN-13
[[email protected] ~]$ dbv file=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8h22vb6m_.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 12 15:08:08 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8h22vb6m_.dbf
DBVERIFY - Verification complete
Total Pages Examined : 44240
Total Pages Processed (Data) : 6600
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1148
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2841
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 33651
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1126464 (0.1126464)
二、驗證段
驗證段的時候要求資料庫必須處在open狀态，還需要提供擁有SYSDBA權限的帳号進行查詢，查詢段的指令格式例如：
dbv userid=system/oracle segment_id=tsn.segfile.segblock
tsn--表示表空間id
segfile--表示段頭所在資料檔案号
segblock--表示段頭資料塊号
這三個值可以通過資料字典sys_dba_segs擷取，相關的列分别是TABLESPACE_ID、 HEADER_FILE和HEADER_BLOCK
[email protected]>select tablespace_id,header_file,header_block
2 from sys_dba_segs
3 where owner='SCOTT' and segment_name='TEST';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
4 4 522
[[email protected] ~]$ dbv userid=system/oracle segment_id=4.4.522
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 12 15:26:30 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 4.4.522
Page 524 is marked corrupt
Corrupt block relative dba: 0x0100020c (file 4, block 524)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6369 seq: 0xa flg: 0x04
spare1: 0x63 spare2: 0x64 spare3: 0x0
consistency value in tail: 0x65790602
check value in block header: 0x2f5b
computed block checksum: 0xad06
Page 526 is marked corrupt
Corrupt block relative dba: 0x0100020e (file 4, block 526)
Bad header found during dbv:
Data in bad block:
type: 97 format: 2 rdba: 0x68676665
last change scn: 0x6e6d.6c6b6a69 seq: 0xa flg: 0x04
spare1: 0x63 spare2: 0x64 spare3: 0x0
consistency value in tail: 0x64b30601
check value in block header: 0x54c2
computed block checksum: 0xa5cd
DBVERIFY - Verification complete
Total Pages Examined : 8
Total Pages Processed (Data) : 3
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 : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1205628 (0.1205628)
三、其他
dbv指令幫助：
[[email protected] ~]$ dbv
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jan 12 15:48:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
編寫腳本一次檢查多個資料檔案
儲存下列腳本為dbv_all.sh檔案。
#!/bin/bash
DATADIR=$1
BLOCKSIZE=$2
cd $DATADIR
ls -1 *.dbf | while read FILE
do
dbv file=$FILE blocksize=$BLOCKSIZE
done
給腳本執行的權限。
[[email protected] ~]$ chmod +x dbv_all.sh
執行腳本。
[[email protected] ~]$ ./dbv_all.sh /u01/app/oracle/oradata/orcl 8192 >> dbv_all.log 2>&1
檢視執行結果。
[[email protected] ~]$ more dbv_all.log