DBVERIFY(DBV)坏块的检测工具 (Doc ID 35512.1)
2 --> 一、介绍 DBV(DBVERIFY)是Oracle提供的一个命令行工具,它可以对数据文件物理和逻辑两种一致性检查。但是这个工具不会检查索引记录和数据记录的匹配关系,这种检查必须使用analyze validate structure命令。 这个工具有如下特点: 以只读的方式打开数据文件,在检查过程中不会修改数据文件的内容。 可以在线检查数据文件,而不需要关闭数据库。 DBV只会检查数据块的正确性,但不会关系数据块是否属于哪个对象。 dbv help=y 参数 含义 缺省值 FILE 要检查的数据文件名 没有缺省值 START 检查起始数据块号 数据文件的第一个数据块 END 检查的最后一个数据块号 数据文件的最后一个数据块 BLOCKSIZE 数据块大小,这个值要和数据库的DB_BLOCK_SIZE参数值一致 缺省值8192 LOGFILE 检查结果日志文件 没有缺省值 FEEDBAK 显示进度 0 PARFILE 参数文件名 没有缺省值 USERID 用户名、密码 没有缺省值 SEGMENT_ID 段ID,参数格式<tsn.segfile.segblock> 没有缺省值 二、测试实验(db version:19.3.0.0,ASM) 1、检查ASM实例数据文件 [grid@p19c01 ~]$ dbv file=+DATA/ORCL/DATAFILE/SYSAUX.258.1067243075 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 07:15:40 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/ORCL/DATAFILE/SYSAUX.258.1067243075 DBVERIFY - Verification complete Total Pages Examined : 69120 Total Pages Processed (Data) : 5437 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 2684 Total Pages Failing (Index): 0 Total Pages Processed (Lob) : 25350 2、指定BLOCKSIZE检测数据文件,blocksize=8192kb --获取数据库db_block_size SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 --获取数据文件号 SQL> select file#,name from v$datafile; FILE# NAME ---------- -------------------------------------------------------------------------------- 1 +DATA/ORCL/DATAFILE/system.274.1067312029 3 +DATA/ORCL/DATAFILE/sysaux.275.1067312063 4 +DATA/ORCL/DATAFILE/undotbs1.276.1067312079 5 +DATA/ORCL/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.282.1067312545 6 +DATA/ORCL/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.283.1067312545 7 +DATA/ORCL/DATAFILE/users.277.1067312079 8 +DATA/ORCL/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.284.1067312545 9 +DATA/ORCL/DATAFILE/undotbs2.286.1067312997 8 rows selected. --获取数据文件1的END SQL> select bytes/8192 from v$datafile where file#=1; BYTES/8192 ---------- 113920 --检查数据文件是否有坏块 [oracle@p19c01 ~]$ dbv file=+DATA/ORCL/DATAFILE/system.274.1067312029 blocksize=8192 end=113920 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 07:28:51 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/ORCL/DATAFILE/system.274.1067312029 DBVERIFY - Verification complete Total Pages Examined : 113920 Total Pages Processed (Data) : 79434 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 12737 Total Pages Failing (Index): 0 Total Pages Processed (Other): 5111 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 16638 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 2369647 (0.2369647) 3、检查控制文件,blocksize=16384kb --检测控制文件是否坏块 --不指定bolcksize会报错 [oracle@p19c01 ~]$ dbv file=+DATA/ORCL/CONTROLFILE/current.278.1067312147 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:23:06 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/ORCL/CONTROLFILE/current.278.1067312147 DBV-00111: OCI failure (4409) (ORA-19501: read error on file "+DATA/ORCL/CONTROLFILE/current.278.1067312147", block number 1 (block size=8192) ORA-17507: I/O request size is not a multiple of logical block size. ORA-06512: at "SYS.DBMS_DBVERIFY", line 24 ORA-06512: at line 1 ) --查看控制文件的blocksize为16K [grid@p19c01 ~]$ dbfsize Current.261.1067243211 Database file: Current.261.1067243211 Database file type: file system Database file size: 1202 16384 byte blocks --指定blocksize为16K [oracle@p19c01 ~]$ dbv file=+DATA/ORCL/CONTROLFILE/current.278.1067312147 blocksize=16384 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:24:03 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/ORCL/CONTROLFILE/current.278.1067312147 DBVERIFY - Verification complete Total Pages Examined : 1202 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 85 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 1117 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 1382 (0.1382) 4、检查单独的Segment --查看对象的tsn,segfile,segblock属性: select t.ts#,s.header_file,s.header_block from v$tablespace t,dba_segments s where s.segment_name='LUCIFER' 4 and t.name=s.tablespace_name; TS# HEADER_FILE HEADER_BLOCK ---------- ----------- ------------ 0 10 33600 --检查segment是否坏块 [oracle@p19c01 ~]$ dbv userid=lucifer/lucifer@pdb01 segment_id=0.10.33600 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:25:35 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : SEGMENT_ID = 0.10.33600 DBV-00600: Fatal Error - [1] [1] [1] [1] -- 5、检查log文件(redo和arch)blocksize=512kb [oracle@p19c01 ~]$ dbv file=+DATA/ORCL/ONLINELOG/group_1.280.1067312151 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:30:58 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = +DATA/ORCL/ONLINELOG/group_1.280.1067312151 Segmentation fault (core dumped) --将asm中redolog文件复制一份出来 [grid@p19c01 ~]$ asmcmd -p ASMCMD [+] > cp +DATA/ORCL/ONLINELOG/group_1.280.1067312151 /home/grid copying +DATA/ORCL/ONLINELOG/group_1.280.1067312151 -> /home/grid/group_1.280.1067312151 ASMCMD [+] > exit [grid@p19c01 ~]$ ls Current.261.1067243211 group_1.280.1067312151 --检查redo日志文件 [grid@p19c01 ~]$ dbv file=group_1.280.1067312151 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:32:20 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBV-00103: Specified BLOCKSIZE (8192) differs from actual (512) --查看redo log的blocksize为512k [grid@p19c01 ~]$ dbv file=group_1.280.1067312151 DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 08:32:20 2021 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. DBV-00103: Specified BLOCKSIZE (8192) differs from actual (512) [grid@p19c01 ~]$ dbfsize group_1.280.1067312151 Database file: group_1.280.1067312151 Database file type: file system Database file size: 409600 512 byte blocks --[grid@p19c01 ~]$ dbv file=group_1.280.1067312151 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 : 190957 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0) --查看归档日志 [grid@p19c01 ~]$ dbv file=thread_1_seq_8.295.1067503735 blocksize=512 logfile=archdbv.log feedback=100 [grid@p19c01 ~]$ cat archdbv.log DBVERIFY - Verification complete Total Pages Examined : 42293 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 : 42293 Total Pages Influx : 12885 Total Pages Encrypted : 0 Highest block SCN : 0 (0.0)