一、dbv工具
1、块检查工具说明
工具列表 坏块检查类型 能否修复坏块
dbverfy 物理 否
analyze 逻辑 否
db_block_checking 逻辑 否
db_block_checksum 物理 否
exp/expdp 物理 否
flashback 逻辑 是
Rman 未知 是
dbverfy工具介绍:
是一个运行于操作系统提示符下的外部程序,用于验证数据文件,检查块的一致性错误;
仅仅针对数据文件,能够校验open阶段的数据文件以及shutdown状态下的数据文件;
可以验证复制的数据文件,也可以验证备份的镜像副本;
不支持联机日志文件,控制文件,归档文件,Rman备份集验证;
被验证的文件可以位于文件系统,ASM磁盘或原始设备;
在unix系统中位于:$ORACLE_HOME/bin/dbv;
对于dbverfy工具,高版本可以自动识别低版本数据库,比如11g的dbv访问9i的数据库,但是低版本的dbv访问高版本会报错;
[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/ORCL/system01.dbf
DBVERIFY: Release 19.0.0.0.0 - Production on Sat Jun 1 22:55:20 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ORCL/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 116480
Total Pages Processed (Data) : 81067
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13239
Total Pages Failing (Index): 0
Total Pages Processed (Other): 5093
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 17081
Total Pages Marked Corrupt : 0 --坏块个数
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 5553701 (0.5553701)
feedback=100 : 表示100个块显示一个小点 .
[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/ORCL/system01.dbf feedback=100 logfile=1.log
DBVERIFY: Release 19.0.0.0.0 - Production on Sat Jun 1 23:02:35 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ORCL/system01.dbf
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
..................................
它可以用来检查数据文件的块是否损坏,无论是数据文件是在online状态还是off状态或是备份的数据文件都可以检查,不能检查联机重做日志文件和控制文件的块。
dbv也可以用来检查数据文件的一个表或者索引
该办法需要获得段所在表空间的ID,段所在数据文件的ID,段的头部ID
SQL> select *from tyjc.golb;
SQL> select segment_name,tablespace_id,header_file,header_block from sys.sys_dba_segs where owner='TYJC';
SEGMENT_NAME TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------------------------ ------------- ----------- ------------
GOLB 1 3 4554
$dbv userid=oracle/oracle segment_id=1.3.4554
RMAN> backup validate database; Starting backup at 2024-06-01 23:46:06 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=149 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 0 17081 116489 5556377 File Name: /u01/app/oracle/oradata/ORCL/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 81067 Index 0 13239 Other 0 5093 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3 OK 0 21270 85770 5556672 File Name: /u01/app/oracle/oradata/ORCL/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 11768 Index 0 10844 Other 0 41878
检查坏块:
select *from v$database_block_corruption;
二、使用RMAN修复坏块
RMAN命令中的backup validate database命令通常用于检查全库,该命令不产生任何备份集,可以通过validate命令来检查是否能备份,如数据文件是否存在,是否存在坏块不能被备份,查询视图v$database_block_corruption,此视图将检查过程中存在的坏块
RMAN> backup validate database; RMAN> backup validate database archivelog all; SQL> select *from v$database_block_corruption;
视图v$database_block_corruption将列出损坏的坏块所在的文件位置,损坏块的起始位置,
损坏块的大小及损坏类型如果上述视图中发现了坏块,则可以通过SQL查询获得坏块所影响的范围,以及确定坏块所影响的是索引还是UNDO段;select owner,segment_name,segment_type from dba_extents where file_id=<F> and <B> between block_id and block_id+blocks-1; (<F>和<B>分别是ORA-01578报出的坏块出现的文件号和块号)
下面使用rman来修复受损的数据块:
RMAN> run{ allocate channel ch1 device type disk; blockrecover datafile 5 block 162; release channel ch1; }