Use of DBVERIFY Tool

--**********************

-- Use of DBVERIFY

--**********************

Oracle cannot be identified in Oracle format or its contents are damaged due to hardware faults or operating system faults during Oracle database operation.

Fault, which can be divided into medium damage and logical damage. The following describes how to check blocks and how to use the DBVERIFY tool to check blocks.

I. block check

1. When to check the block

When a data block is read or written, the consistency of the block is checked. The check content includes

Block version

Compare block data block addresses in cache and block buffer

Checksum)

2. error message for corrupted data blocks

This error message can be found in the alarm log file, and a similar prompt will be displayed when corrupted data blocks are found in the session.

ORA-01578: ORACLE data block upted (file #6, block #11)

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'

3. features related to block damage (several checking tools)

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

Can the feature block detection type repair damaged blocks?

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

DBVERIFY physical No

ANALYZE logic No

DB_BLOCK_CHECKING logic No

DB_BLOCK_CHECKSUM physical No

Exp physical No

FlashBack logic is

DBMS_REPAIR logic is

Block media recovery unknown is

2. DBVERIFY tool Introduction

Features

Is an external program running at the operating system prompt. It is used to verify data files and check for block consistency errors.

Data files in the open stage and data files in the shutdown state can be verified only for data files.

You can verify the copied data file or the backup image copy.

Online log files, control files, archived logs, and RMAN backup set verification are not supported.

The verified file can be in the file system, ASM disk, or original device.

In Unix systems: $ ORACLE_HOME/bin/dbv

In Windows: % ORACLE_HOME %/bin/dbv.exe

For the DBVERIFY tool, the later version can automatically identify the earlier version of the database. For example, if the 11g dbv accesses the 9i database, an error is returned when the earlier version of dbv accesses the later version.

Iii. DBVERIFY tool usage

1. Obtain the help information of dbv. Enter dbv at the prompt or dbv help = y.

[Oracle @ oradb orcl] $ dbv

DBVERIFY: Release 10.2.0.4.0-Production on Tue Oct 26 18:21:09 2010

Copyright (c) 1982,200 7, Oracle. 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) -- specifies the Size of the data file. The default value is 8192. For non-8192 blocks, a DBV-00103 error is reported.

LOGFILE Output Log (NONE) -- used to display the verification progress

FEEDBACK Display Progress (0)

PARFILE Parameter File (NONE) -- you can specify the Parameter File

USERID Username/Password (NONE) -- used to verify the segment and ASM file

SEGMENT_ID Segment ID (tsn. relfile. block) (NONE) -- check the Segment. The tablespace ID, Data File ID, and Segment header ID are required.

HIGH_SCN Highest Block SCN To Verify (NONE)

(Scn_wrap.scn_base OR scn)

2. Check the online and offline data files. Use the following method:

Dbv file =

[Oracle @ oradb orcl] $ dbv file = $ ORACLE_BASE/oradata/orcl/tbs01.dbf

DBVERIFY: Release 10.2.0.4.0-Production on Tue Oct 26 18:29:39 2010

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

DBVERIFY-Verification starting: FILE =/u01/app/oracle/oradata/orcl/tbs01.dbf

DBVERIFY-Verification complete

Total Pages Examined: 128 -- Total number of Pages for verification. A page is a data block.

Total Pages Processed (Data): 96 -- number of Processed Data Pages

Total Pages Failing (Data): 0 -- number of failed Data Pages processed

Total Pages Processed (Index): 1 -- number of Processed Index Pages

Total Pages Failing (Index): 0 -- number of failed indexed Pages processed

Total Pages Processed (Other): 31 -- number of Other Processed Pages

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 0

Total Pages Marked success upt: 0

Total Pages Influx: 0

Highest block SCN: 1152518 (0.1152518)

Note: If the value of Total Pages Influx is greater than zero and no bad blocks exist, it is because dbv is run for open files.

The program encounters a data block that is currently being written by the DBWn process.

[Oracle @ oradb orcl] $ dbv file = $ ORACLE_BASE/oradata/orcl/tbs01.dbf feedback = 1000

The above statement shows a "." number for each verification of 1000 blocks during execution.

-- I/O errors are found in the verification below

[Oracle @ oradb orcl] $ dbv file =/u01/app/oracle/oradata/orcl/tbs01.dbf

DBVERIFY: Release 10.2.0.4.0-Production on Tue Oct 26 18:26:21 2010

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

DBV-00102: File I/O error on FILE (/u01/app/oracle/oradata/orcl/tbs01.dbf)

During end read operation (-1)

3. Verify the specified segment

This method requires the ID of the tablespace where the segment is located, the ID of the data file where the segment is located, and the header ID of the segment.

In the following query table, the ID of the table space is 7, the file ID is 6, and the header ID of the segment is 35.

Sys @ ORCL> select tablespace_id, tablespace_name, header_file, header_block

2 from sys_dba_segs

3 where segment_name = '83 ';

TABLESPACE_ID TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

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

7 TBS1 6 35

Note: sys_user_segs can be queried for sys user segments. For common user segments, sys_dba_segs must be queried.

[Oracle @ oradb orcl] $ dbv userid = scott/tiger segment_id = 7.6.35

DBVERIFY: Release 10.2.0.4.0-Production on Tue Oct 26 18:50:01 2010

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

DBVERIFY-Verification starting: SEGMENT_ID = 7.6.35

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

Total Pages Influx: 0

Highest block SCN: 1152518 (0.1152518)

4. Verify the copied data file or the backup image copy

RMAN> backup as copy datafile 6 -- use RMAN to back up image copies

2> format = '/u01/app/oracle/bk/rmbk/cp_dfile6'

3> tag = 'Copy _ datafile6 ';

[Oracle @ oradb orcl] $ dbv file =/u01/app/oracle/bk/rmbk/cp_dfile6

DBVERIFY: Release 10.2.0.4.0-Production on Tue Oct 26 18:59:17 2010

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

DBVERIFY-Verification starting: FILE =/u01/app/oracle/bk/rmbk/cp_dfile6

DBVERIFY-Verification complete

Total Pages Examined: 128

Total Pages Processed (Data): 96

Total Pages Failing (Data): 0

Total Pages Processed (Index): 1

Total Pages Failing (Index): 0

Total Pages Processed (Other): 31

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 0

Total Pages Marked success upt: 0

Total Pages Influx: 0

Highest block SCN: 1152518 (0.1152518)

The backup validate database command in RMAN is usually used to check the full DATABASE. This command does not generate any BACKUP sets.

The Validate command is used to check whether a backup can be performed, such as whether a data file exists and whether Bad blocks cannot be backed up. View query

V $ database_block_partition uption, which checks the Bad blocks in the process

For example, use the following query

RMAN> backup validate database;

RMAN> backup validate database archivelog all;

Sys @ ORCL> select * from v $ database_block_corruption;

No rows selected

View v $ database_block_corruption uption will list the file locations where damaged blocks are located, the starting position of damaged blocks, and the size of damaged blocks

Small and damaged types if the above view finds a bad block, you can use SQL query to obtain the range affected by the bad block and determine the Bad Block

Whether the index segment or UNDO segment is affected

Select owner, segment_name, segment_type from dba_extents where file_id = and

Between block_id and block_id + blocks-1;

(And are the file number and block number of the Bad block reported by the ORA-01578 respectively)

Next we will use rman to repair damaged data blocks.

RMAN> run {

2> allocate channel specified device type disk;

3> blockrecover datafile 6 block 37;

4> release channel success ;}

Released channel: ORA_DISK_1

Allocated channel: allocated

Channel identifier: sid = 139 devtype = DISK

Starting blockrecover at 26--10 -10

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished blockrecover at 26-OCT-10

Released channel: released

Flasher @ ORCL> alter table fig add constraint empno_tb3_pk

2 primary key (empno );

4. Others

1. In fact, you can use the dbv tool to verify the control file (the database is OPEN). See the following example.

[Oracle10g: oracle: orcl] $ dbv file = control01.ctl

DBVERIFY: Release 10.2.0.4.0-Production on Sun Jun 5 14:56:31 2011

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

DBV-00103: Specified BLOCKSIZE (8192) differs from actual (16384) -- the value of blocksize is 16384 as prompted

[Oracle @ oradb orcl] $ dbv file = control01.ctl blocksize = 16384

DBVERIFY: Release 10.2.0.4.0-Production on Sun Jun 5 14:53:01 2011

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

DBVERIFY-Verification starting: FILE = control01.ctl

DBVERIFY-Verification complete

Total Pages Examined: 404

Total Pages Processed (Data): 0

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 40

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 364

Total Pages Marked success upt: 0

Total Pages Influx: 0

Highest block SCN: 775 (65535.775)

2. Check the log file of the couplet (the database is OPEN), not supported

[Oracle10g: oracle: orcl] $ dbv file = redo01.log

DBVERIFY: Release 10.2.0.4.0-Production on Sun Jun 5 15:01:09 2011

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

DBV-00103: Specified BLOCKSIZE (8192) differs from actual (512)

[Oracle10g: oracle: orcl] $ dbv file = redo01.log blocksize = 512 -- it can be executed, but many of the following prompts appear (Media errors)

Page 88441 is influx-most likely media upt

Corrupt block relative dba: 0x00015979 (file 0, block 88441)

Fractured block found during dbv:

Data in bad block:

Type: 1 format: 2 rdba: 0x00015979

Last change scn: 0x8010.00000010 seq: 0x51 flg: 0x55

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

Consistency value in tail: 0xc2037800

Check value in block header: 0x108

Computed block checksum: 0x0

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html