【Oracle坏块】DBVERIFY（DBV）坏块的检测工具
一、介绍
DBV(DBVERIFY)是Oracle提供的一个命令行工具，它可以对数据文件物理和逻辑两种一致性检查。但是这个工具不会检查索引记录和数据记录的匹配关系，这种检查必须使用analyze validate structure命令。
这个工具有如下特点：
- 以只读的方式打开数据文件，在检查过程中不会修改数据文件的内容。
- 可以在线检查数据文件，而不需要关闭数据库。
- DBV只会检查数据块的正确性，但不会关系数据块是否属于哪个对象。
[oracle@T1 ~]$ dbv help=y 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)
二、测试实验
1、检查数据文件
[oracle@T1 ~]$ dbv file=/u01/app/oracle/oradata/T1/users01.dbf DBVERIFY: Release 19.0.0.0.0 - Production on Thu Jul 29 15:48:18 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 640 Total Pages Processed (Data) : 60 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 15 Total Pages Failing (Index): 0 Total Pages Processed (Other): 464 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 101 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1252497 (0.1252497)
2、指定BLOCKSIZE检测数据文件，blocksize=8192
查看db_block_size
SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
获取数据文件号
SQL> select file_id,file_name from dba_data_files; FILE_ID FILE_NAME ---------- ------------------------------------------------------------ 1 /u01/app/oracle/oradata/T1/system01.dbf 3 /u01/app/oracle/oradata/T1/sysaux01.dbf 7 /u01/app/oracle/oradata/T1/users01.dbf 4 /u01/app/oracle/oradata/T1/undotbs01.dbf
获取数据文件1的END值
SQL> select bytes/8192 from v$datafile where file#=1; BYTES/8192 ---------- 120320
检查文件是否有坏块
[oracle@T1 ~]$ dbv file=/u01/app/oracle/oradata/T1/system01.dbf blocksize=8192 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Jul 29 15:55:14 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/system01.dbf DBVERIFY - Verification complete Total Pages Examined : 120320 Total Pages Processed (Data) : 83708 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 13546 Total Pages Failing (Index): 0 Total Pages Processed (Other): 5085 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 17981 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 2934625 (0.2934625)
3、检查控制文件，blocksize=16384
查看控制文件的blocksize为16K
Database file: /u01/app/oracle/oradata/T1/control01.ctl
Database file type: file system
Database file size: 1162 16384 byte blocks
检查控制文件是否有坏块，不指定blocksize会报错
[oracle@T1 ~]$ dbv file=/u01/app/oracle/oradata/T1/control01.ctl blocksize=16384 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Jul 29 16:01:41 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/T1/control01.ctl DBVERIFY - Verification complete Total Pages Examined : 1162 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 65 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 1097 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 281470681761137 (65535.17777)
4、检查单独的Segment
select t.ts#,s.header_file,s.header_block from v$tablespace t,dba_segments s where s.segment_name='TEST' and t.name=s.tablespace_name; TS# HEADER_FILE HEADER_BLOCK ---------- ----------- ------------ 0 1 116536 0 1 116536 0 1 116536
检查segment是否有坏块
[oracle@T1 ~]$ dbv userid=system/oracle segment_id=0.1.116536 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Jul 29 16:11:24 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : SEGMENT_ID = 0.1.116536 DBVERIFY - Verification complete Total Pages Examined : 2 Total Pages Processed (Data) : 1 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 0 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 : 2929330 (0.2929330)
5、检查log文件（redo和arch）blocksize=512
dbv file=/u01/app/oracle/oradata/T1/redo03.log blocksize=512 DBVERIFY - Verification complete Total Pages Examined : 409600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 0 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 409600 Total Pages Influx : 409597 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0)
检查archivelog文件
dbv file=1_9_1079089425.dbf blocksize=512 logfile=check_archive.log feedback=100 [oracle@T1 oracle_archive]$ cat check_archive.log DBVERIFY - Verification complete Total Pages Examined : 232542 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 0 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 0 Total Pages Marked Corrupt : 232542 Total Pages Influx : 46158 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0)
