Shulou(Shulou.com)06/01 Report--

DBV (DBVERIFY) is a command line tool provided by Oracle, which can check both physical and logical consistency of data files. However, this tool does not check the matching of index records and data records, which must be checked using the analyze validate structure command.

This tool has the following features:

1 Open the data file read-only, and the contents of the data file will not be modified during the inspection process.

2 you can check the data file online without shutting down the database.

Control files and log files cannot be checked, only data files can be checked.

4 this tool can check ASM files, but the database must have an Open status and a user needs to be specified through USERID, such as dbv file=+DG1/ORCL/datafile/system01.dbf userid=system/sys

5 in many UNIX platforms, DBV requires data files to have an extension, if there is no way to establish a link, and then to the link method, and then operate on the link file, such as: ls-n / dev/rdsk/mydevice / tmp/mydevice.dbf

6 on some platforms, the DBV tool cannot check files that exceed 2GB. If you encounter an DBV-100 error, please check the file size first. MOS Bug 710888 describes this problem.

7DBV only checks the correctness of the data block, but does not determine which object the data block belongs to.

8 it is recommended to specify the END parameter for the slave device to avoid going beyond the range of the data file. For example: dbv FILE=/dev/rdsk/r1.dbf END=. The end value can be obtained by dividing the bytes field by the block size in the v$datafile view.

Default value of parameter meaning

There is no default value for the data file name to be checked by FILE

START checks the first block of the starting block number data file

The last block of the last block number data file checked by END

BLOCKSIZE block size, which should be consistent with the DB_BLOCK_SIZE parameter value of the database. The default value is 8192.

LOGFILE check result log file has no default value

FEEDBAK shows progress 0

PARFILE parameter file name has no default value

USERID username and password do not have default values

SEGMENT_ID segment ID. Parameter format has no default value.

Examples of use:

[oracle@rhel6 ~] $dbv file=/u01/app/oracle/oradata/test/users01.dbf

DBVERIFY: Release 11.2.0.1.0-Production on Mon May 22 16:42:26 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY-Verification starting: FILE = / u01/app/oracle/oradata/test/users01.dbf

DBVERIFY-Verification complete

Total Pages Examined: 155520

Total Pages Processed (Data): 144530

Total Pages Failing (Data): 0

Total Pages Processed (Index): 52

Total Pages Failing (Index): 0

Total Pages Processed (Other): 1248

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 9690

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3559792 (0.3559792)

This tool reports using page as a unit, which has the same meaning as data block. From the above check result Total Pages Marked Corrupt: 0, you can see that there are no bad blocks in the file.

In addition to checking the data file, the tool also allows you to check individual Segment, where the parameter values are in the format

View the tsn,segfile,segblock property of the object:

Zx@TEST > select t. Tsbooks. Headerbooks filebooks. Headerboards block.

2 from v$tablespace t,dba_segments s

3 where s.segmentationnameplate

4 and t.name=s.tablespace_name

TS# HEADER_FILE HEADER_BLOCK

4 4 45834

The feasible parameter value from the above query result is 4.4.45834. Check Segment:

[oracle@rhel6 ~] $dbv userid=system/123456 segment_id=4.4.45834

DBVERIFY: Release 11.2.0.1.0-Production on Mon May 22 20:58:33 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY-Verification starting: SEGMENT_ID = 4.4.45834

DBVERIFY-Verification complete

Total Pages Examined: 8

Total Pages Processed (Data): 5

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: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3518579 (0.3518579)

The following artificially creates a bad block and checks it with dbv.

Create a test table

Zx@TEST > create table bbed (id number,name varchar2 (20)) tablespace users

Table created.

Zx@TEST > insert into bbed values (1meme Zhaoxu')

1 row created.

Zx@TEST > commit

Commit complete.

There are no bad blocks in the current data file

[oracle@rhel6 ~] $dbv file=/u01/app/oracle/oradata/test/users01.dbf

DBVERIFY: Release 11.2.0.1.0-Production on Mon May 22 21:03:40 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY-Verification starting: FILE = / u01/app/oracle/oradata/test/users01.dbf

DBVERIFY-Verification complete

Total Pages Examined: 155520

Total Pages Processed (Data): 66397

Total Pages Failing (Data): 0

Total Pages Processed (Index): 52

Total Pages Failing (Index): 0

Total Pages Processed (Other): 88898

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 173

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3764775 (0.3764775)

Get the storage information of the table in the file

Zx@TEST > set serveroutput on

Zx@TEST > declare rfno number

2 rtype number

3 ono number

4 blkno number

5 rowno number

6 rid varchar2 (30)

7 begin

8 select rowid into rid from bbed

9 dbms_rowid.rowid_info (ROWID_IN= > rid,RELATIVE_FNO= > rfno,BLOCK_NUMBER= > blkno,ROW_NUMBER= > rowno,ROWID_TYPE= > rtype,OBJECT_NUMBER= > ono)

10 dbms_output.put_line (rfno | |','| | blkno | |','| | rowno)

11 end

12 /

4,45844,0

PL/SQL procedure successfully completed.

Modify block information using bbed

[oracle@rhel6 bbed] $bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0-Limited Production on Mon May 22 21:17:18 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

*! For Oracle Internal Use only!! *

BBED > set dba 4pm 45844

DBA 0x0100b314 (16823060 4pm 45844)

BBED > find / c zhaoxu

File: / u01/app/oracle/oradata/test/users01.dbf (4)

Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314

7a68616f 78750106 5873

BBED > dump / v dba 4 45844 offset 8182 count 32

File: / u01/app/oracle/oradata/test/users01.dbf (4)

Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314

7a68616f 78750106 5873 l zhaoxu..Xs

BBED > modify 100 dba 4pm 45844

Warning: contents of previous BIFILE will be lost. Proceed? (Ybig N) y

File: / u01/app/oracle/oradata/test/users01.dbf (4)

Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314

6468616f 78750106 5873

BBED > dump / v dba 4 45844 offset 8182 count 32

File: / u01/app/oracle/oradata/test/users01.dbf (4)

Block: 45844 Offsets: 8182 to 8191 Dba:0x0100b314

6468616f 78750106 5873 l dhaoxu..Xs

BBED > exit

Use dbv to check the file again

[oracle@rhel6 bbed] $dbv file=/u01/app/oracle/oradata/test/users01.dbf

DBVERIFY: Release 11.2.0.1.0-Production on Mon May 22 21:18:46 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY-Verification starting: FILE = / u01/app/oracle/oradata/test/users01.dbf

Page 45844 is marked corrupt

Corrupt block relative dba: 0x0100b314 (file 4, block 45844)

Bad check value found during dbv:

Data in bad block:

Type: 6 format: 2 rdba: 0x0100b314

Last change scn: 0x0000.00397358 seq: 0x1 flg: 0x04

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x73580601

Check value in block header: 0x7c2d

Computed block checksum: 0x1e

DBVERIFY-Verification complete

Total Pages Examined: 155520

Total Pages Processed (Data): 66396

Total Pages Failing (Data): 0

Total Pages Processed (Index): 52

Total Pages Failing (Index): 0

Total Pages Processed (Other): 88898

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 173

Total Pages Marked Corrupt: 1

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3764775 (0.3764775)

Report a bad block, Total Pages Marked Corrupt: 1

Query the test table again:

Sys@TEST > select * from zx.bbed

ID NAME

1 zhaoxu

The query is normal because the block is cached in buffer_cache and the block in the file is modified. The two blocks are now inconsistent. Empty the buffer cache and query the test table again.

Zx@TEST > alter system flush buffer_cache

System altered.

Zx@TEST > select * from bbed

Select * from bbed

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 45844)

ORA-01110: data file 4:'/ u01 *

The query reported an error ORA-01578.

To check the data files in the ASM file using dbv, you need to specify the userid parameter

[oracle@rac1 ~] $dbv file=+DATA/orcl/datafile/users.259.925306091 userid=sys/123456

DBVERIFY: Release 11.2.0.4.0-Production on Mon May 22 16:48:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY-Verification starting: FILE = + DATA/orcl/datafile/users.259.925306091

DBVERIFY-Verification complete

Total Pages Examined: 640

Total Pages Processed (Data): 16

Total Pages Failing (Data): 0

Total Pages Processed (Index): 2

Total Pages Failing (Index): 0

Total Pages Processed (Other): 593

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 29

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 0 (0.0)

Reproduced from: https://www.linuxidc.com/Linux/2017-05/144202.htm

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.