Oracle DBV tool description

 

 

1. DBV description

DBV is a common tool. An article on oraclemos introduces DBV: [ID 35512.1].

 

The main purpose of the dbverify tool is to check the physical structure of the data file, including whether the data file is damaged, whether there are logical Bad blocks, and what types of data contained in the data file.

 

DBV checks oracledatafiles to ensure that:

(1) The datafile has a valid header

(2) Each datablock in the file has aspecial "wrapper" which identifies the block-This "wrapper" is checked for correctness

(3) data (table) and index blocks areinternally consistent

(4) from 8.1.6 onwards: That variousother block types are internally consistent (such as rollback segmentblocks)

 

The tool can be used to give some degree of confidence that a datafile is free from0000uption. It opens files in a read only mode and so cannot change thecontents of the file being checked.

 

The dbverify tool can verify online or offline data files. Data files can be accessed regardless of whether the database is opened or not.

 

Note:

(1) For the dbverify tool, the higher version can automatically identify the lower version of the database. For example, if the 11g DBV accesses the 9i Database, the lower version of DBV accesses the higher version and reports the following errors:

Dbverify-verification starting: file = E:/Oracle/oradata/Dave/test01.dbf

Imported page 1-Possible Media damage

(2) view the data file number and block number of the bad data block. You can perform a full table scan on the table, for example:

Select count (*) from tablename;

If there is a bad block, an error will be reported during scanning.

 

Ii. DBV Command Parameters

C:/> DBV help = y

Dbverify: Release 11.1.0.7.0-productionon Tuesday December 15 23:35:24 2009

Copyright (c) 1982,200 7, Oracle. allrights reserved.

Keyword description (default)

----------------------------------------------------

File the file to be verified (none)

Start start block (the first block of the file)

End end block (the last block of the file)

Blocksize logical block size (8192)

Logfile output log (none)

Display progress of feedback (0)

Parfile parameter file (none)

Userid username/password (none)

Segment_id segment ID (TSN. relfile. Block) (none)

Maximum chunks to be verified by high_scn (none)

(Scn_wrap.scn_base or SCN)

 

(1) file (file to verify): This is the name of the file to verify. See "limitations" below if your datafile name has no suffix.

(2) Start (start block): This is the first datablock to check in the file. this defaults to the first block in the file and need only be specifiedif you want to check just a portion of a given file.

(3) end (End Block): This is the last datablock to check in the file. thisdefaults to the last block of the filebut may need specifying for raw devices (see "limitations" below) blocksize logical block size this is
Database blocksize of the datafile you wish to scan. The value defaults to "2048". thisparameter must be set to the db_block_size of the datafile to be scanned.

(4) logfile (output log): This is the name of file to outputthe results to. The default is "NONE" and output is sent to terminal.

(5) feedback (display progress): If set to a value above 0 (the default) Then dbvoutputs ". "For every n pages of the datafile checked. this isuseful To See That DBV is working through the Ile.

(6) parfile (parameter file): parameters can be specified in a parameterfile and parfile used to cause the filecontents to be used as input parameters. The parfile can contain in any of theabove options.

(7) high_scn (SCN): highest block SCN to verify (scn_wrap.scn_base or SCN) Find theblocks exceeding the SCN. Available in version 9.2.0.6 and above.

(8) USERID (username/password): If the file you are verifying isan Automatic Storage Management (ASM) file, you must supply a userid. this isbecause dbverify needs to connect to an oracle instance to access ASM files.

(9) segment_id (TS #. File #. Block #): Specifies the segment that youwant to verify. For more info, review
Note: 139962.1

 

Iii. DBV restrictions

3.1 As DBV performs checks at a block levelit cannot detect problems such as index versus table mismatches which can bedetected by the 'analyze table... validate Structure Cascade 'command.

 

3.2 This utility can only be used against datafiles. It cannot be used to verify redolog files or control files.

 

3.3 you can use DBV to verify an Automatic Storage Management (ASM) file.

However, the database must be opened and the option userid has to beused

 

Example: DBV file = + DG1/orcl/datafile/system01.dbf userid = system/sys

 

DBV checks the userid/password for ASM managed files, which is notpossible when database is not open.

 

3.4 On most releases on unix dbv expects afilename extension.

This means that DBV cannot be used against datafiles with no filenamesuffix, or against Raw devices.
-- DBV requires that datafile must have an extension.

The workaround is to create a symbolic link to the raw device where thelink name must have an extension.

Eg: ln-S/dev/rdsk/mydevice/tmp/mydevice. DBF

Now use DBV against/tmp/mydevice. DBF

-- If the bare device does not have an extension, you can create a link and perform DBV check.

 

3.5 For raw devices you shocould use the endparameter to avoid running off the end of the Oracle file space.

Eg: "DBV file =/dev/rdsk/r1.dbf end = <last_block_number>"

Ifyou get the end value too high DBV can report the last page/s of the file ascorrupt as these are beyond the end of the Oracle portion of the raw device.

 

You can find value for end from the V $ datafile view by dividing the bytesvalue by the database block size.

 

Eg: To find out the end value to use for file # = 5:

Svrmgrl> show parameter db_block_size

Name type value

------------------------------------------------

Db_block_size integer 2048

 

Svrmgrl> select Bytes/2048 from V $ datafile where file # = 5;

Bytes/2048

----------

5120

So the command wocould be:

Dbvfile =/dev/rdsk/r1.dbf blocksize = 2048 end = 5120

-- The svrmgrl command has been canceled on Oracle 10 Gb, and the default value of db_block_size has also changed to 8 KB.

 

3.6 DBV may not be able to scan datafileslarger than 2 GB and may report "DBV-100". This is reported in
BUG: 710888for UNIX and
BUG: 1372172for 8.1.6 on NT. This problem is platform and release specific so if you getDBV-100 errors check The filesize first.

 

3.7 DBV from 8.1.6 onwards may reportspurous Errors for rollback segment blocks if the database has been migratedfrom oracle7. see
BUG: 1359160and
Note: 118008.1.

 

3.8 DBV only checks a block in isolation-it does not know if the block is part of an existing object or not.

 

3.9 DBV is broken on SCO Unix-see
BUG: 814249

 

3.10 DBV of a lower version shocould not beused against a higher DB version.

 

 

Iv. DBV example

 

4.1 verify files on ASM

Sys @ anqing2 (rac2)> select file_name fromdba_data_files;

File_name

--------------------------------------------------------------------------------

+ Data/Anqing/datafile/users.273.751548233

+ Data/Anqing/datafile/sysaux01.dbf

+ Data/Anqing/datafile/undotbs01.dbf

+ Data/Anqing/datafile/system01.dbf

+ Data/Anqing/datafile/system02.dbf

+ Data/Anqing/datafile/undotbs02.dbf

+ Data/Anqing/datafile/dave01.dbf

+ Data/Anqing/datafile/test01.dbf

 

-- DBV check the files on ASM. Note that the userid parameter is added.

[Oracle @ rac2 ~] $ Dbvfile = '+ Data/Anqing/datafile/undotbs02.dbf' userid = sys/Oracle

 

Dbverify: Release 10.2.0.4.0-productionon Tue Aug 9 21:44:36 2011

 

Copyright (c) 1982,200 7, Oracle. All rights reserved.

 

Dbverify-verification starting: file = + Data/Anqing/datafile/undotbs02.dbf

 

Dbverify-verification complete

 

Total pages examined: 64000

Total pages processed (data): 0

Total pages failing (data): 0

Total pages processed (INDEX): 0

Total pages failing (INDEX): 0

Total pages processed (other): 32748

Total pages processed (SEG): 0

Total pages failing (SEG): 0

Total pages empty: 31252

Total pages marked success upt: 0

Total pages influx: 0

High est block SCN: 0 (0.0)

 

4.2 verify common datafile

-- Verify datafile when the instance is not started

D: \ app \ Administrator \ oradata \ newccs> dbvfile = undotbs01.dbf

 

Dbverify: Release 11.2.0.1.0-productionon Tue Aug 9 21:52:41 2011

 

Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All rights reserved.

 

Dbverify-verification starting: file = D: \ app \ Administrator \ oradata \ newccs \ undotbs01.dbf

 

Dbverify-verification complete

 

Total pages examined: 64000

Total pages processed (data): 0

Total pages failing (data): 0

Total pages processed (INDEX): 0

Total pages failing (INDEX): 0

Total pages processed (other): 35199

Total pages processed (SEG): 10

Total pages failing (SEG): 0

Total pages empty: 28801

Total pages marked success upt: 0

Total pages influx: 0

Total pages encrypted: 0

Highest block SCN: 1315907818 (0.1315907818)

 

-- In this case, the database is not started. To enter the directory where data files are stored, run the command. Otherwise, no data file is found.

 

4.3 dB open status, verify the specified segment

The command format is as follows:

DBV userid = username/passwordsegment_id = TSN. relfile. Block

 

You can use the sys_dba_segs table to obtain the tablespace_id, header_file, and header_block fields, which correspond to TSN, relfile, and block.

 

Sys @ anqing2 (rac2)> select tablespace_id, header_file, header_block from sys_dba_segs where segment_name = 'ta ';

 

Tablespace_id header_file header_block

------------------------------------

0 1 71513

 

[Oracle @ rac2 ~] $ DBV segment_id = 0.1.71513 userid = sys/Oracle

 

Dbverify: Release 10.2.0.4.0-productionon Tue Aug 9 21:59:32 2011

 

Copyright (c) 1982,200 7, Oracle. All rights reserved.

 

 

DBV-00111: OCI failure (1913) (ORA-28009: connection as sys shocould be as sysdba or sysoper

)

[Oracle @ rac2 ~] $ Dbvsegment_id = 0.1.71513 userid = system/Oracle

 

Dbverify: Release 10.2.0.4.0-productionon Tue Aug 9 21:59:46 2011

 

Copyright (c) 1982,200 7, Oracle. All rights reserved.

 

Dbverify-verification starting: segment_id = 0.1.71513

 

 

Dbverify-verification complete

 

Total pages examined: 2281

Total pages processed (data): 2280

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

Total pages influx: 0

High est block SCN: 0 (0.0)

[Oracle @ rac2 ~] $

 

Note: This method requires the database to be open.

 

4.4 verify data copy

Because DBV can verify the data file when the instance is closed, DBV can also verify the copy of the data file. This copy refers to the data file copied through the Copy command of RMAN or the command CP of the operating system, rather than the backup set format generated by RMAN.

 

For example:

E:/APP/Administrator/oradata/orcl> dbvfile = users01bak. DBF blocksize = 8192

Dbverify: Release 11.1.0.7.0-productionon Wednesday December 16 00:30:17 2009

Copyright (c) 1982,200 7, Oracle. allrights reserved.

Dbverify-Start verification: file = E:/APP/Administrator/oradata/orcl/users01bak. DBF

Dbverify-verification completed

Total number of checked pages: 640

Total number of processed pages (data): 91

Total number of failed pages (data): 0

Total number of processed pages (INDEX): 33

Total number of failed pages (INDEX): 0

Total number of processed pages (Others): 496

Total number of processed pages (segments): 0

Total number of failed pages (segments): 0

Total number of blank pages: 20

Total number of pages marked as damaged: 0

Total number of inbound pages: 0

Total number of encrypted pages: 0

Maximum block SCN: 904088 (0.904088)

 

Compare the copy of datafile by comparing two DBV results

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) chat group: 40132017 (full) chat group 2: 69087192 (full)

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.