DBVERIFY
DBVERIFY是oracle用来检测物理结构的外部命令使用工具。可以用来检测online 或者 offline 状态下的数据库或者数据库的备份。只能用来检测数据文件文件,不能用来检测控制文件以及重做日志文件等。
在命令行执行 dbv,可以看到dbv命令的一些参数以及代表意义
[oracle@linusfay ~]$ dbv
DBVERIFY: Release 11.2.0.3.0 - Production on Tue Aug 13 09:34:42 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) --要检测的段(tablespace_id.file_id.block_id)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
有两种检测方式
1、检测单个的数据文件
2、检测单个的段
建立一些测试的表空间,表。然后模拟数据库损坏情况。最后分别用两中方式检测坏块。
--建立一个包含两个大小为5m的数据文件的测试表空间 test_tbs。
SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/orcl/test_tbs01.dbf' size 5m ,'/u01/app/oracle/oradata/orcl/test_tbs02.dbf' size 5m;
Tablespace created.
SQL> create table test_a (id number,name varchar2(10)) tablespace test_tbs;
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_a values(i,'AAAA'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> insert into test_a select * from test_a;
10000 rows created.
SQL> insert into test_a select * from test_a;
20000 rows created.
SQL> insert into test_a select * from test_a;
40000 rows created.
SQL> insert into test_a select * from test_a;
80000 rows created.
SQL> insert into test_a select * from test_a;
160000 rows created.
SQL> insert into test_a select * from test_a;
insert into test_a select * from test_a
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST_A by 128 in tablespace TEST_TBS
SQL> select count(*) from test_a;
COUNT(*)
----------
320000
--填充数据至空间不够,这样能保证表段TEST_A跨数据文件分布,方便后面测试,且能对表进行扫描。现在模拟坏块。
用ue打开数据文件test_tbs01.dbf 随便修改几个字符。然后对表进行查询,出现快坏。
SQL> select count(1) from test_a;
select count(1) from test_a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 416)
ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/test_tbs01.dbf'
检测坏块 DBVERIFY可以有两个方法
1、检测单个数据文件
此处标记坏块处于10号文件。此时数据库可以是open 也可以close。数据文件可以online也可以offline
[oracle@linusfay ~]$ dbv file=/u01/app/oracle/oradata/orcl/test_tbs01.dbf
。。。
Page 459 is marked corrupt
Corrupt block relative dba: 0x028001cb (file 10, block 459)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x028001cb
last change scn: 0x2020.2020776e seq: 0x2 flg: 0x04
spare1: 0x20 spare2: 0x20 spare3: 0x2020
consistency value in tail: 0x776e0602
check value in block header: 0xd081
computed block checksum: 0x2000
。。。省略N多输出
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 460
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 135
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 44 --检测被标记为坏块的个数
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2129565 (0.2129565)
--随便一改 没想到改了这么多坏块出来 郁闷
2、以对象对端为检测对象
可以查看 创建的对象TEST_A的段,分布于那些数据文件
SQL> select distinct segment_name,file_id from dba_extents where segment_name='TEST_A';
SEGMENT_NA FILE_ID
---------- ----------
TEST_A 10
TEST_A 11
这时,如果我们仅仅知道表test_a上有快坏,想对表test_a检测一下,就可以使用下面的方法
首先从sys_user_segs得到几个信息
SQL> select tablespace_id,header_file,header_block from sys_user_segs where segment_name='TEST_A';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
12 11 130
检测
[oracle@linusfay ~]$ dbv userid=system/oracle segment_id=12.11.130
DBVERIFY: Release 11.2.0.3.0 - Production on Tue Aug 13 11:03:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : SEGMENT_ID = 12.11.130
Page 459 is marked corrupt
Corrupt block relative dba: 0x028001cb (file 10, block 459)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x028001cb
last change scn: 0x2020.2020776e seq: 0x2 flg: 0x04
spare1: 0x20 spare2: 0x20 spare3: 0x2020
consistency value in tail: 0x776e0602
check value in block header: 0xd081
computed block checksum: 0x2000
。。。
DBVERIFY - Verification complete
Total Pages Examined : 1024
Total Pages Processed (Data) : 956
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 23
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 44
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2129565 (0.2129565)
由于只改了一个数据文件,因此也得出了相同的结果。
DBVERIFY实用工具详细介绍见官网文档:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dbverify.htm#i1006970