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

Http://blog.csdn.net/leshami/article/details/6530890

The use of DBVERIFY tools

During the operation of Oracle database, due to hardware failure or operating system failure, Oracle can not be identified in Oracle format or the content contained is data block damage fault, which can be divided into media damage and logic damage. The block check is given below, and the block check is implemented using the DBVERIFY tool.

1. Block inspection

1. When to check the block

When a data block is read or written, a consistency check is performed on the block, which includes the following:

Version of the block

Compare block addresses in cache and block buffer

Check as required (checksum)

two。 Error prompt for corrupted data block

The error prompt can be found in the alarm log file, and a similar prompt is given when a corrupted block is found in the session

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

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

3. Characteristics related to block damage (several inspection tools)

Can the property bad block detection type repair the damaged block?

DBVERIFY physical No

ANALYZE Logic No

DB_BLOCK_CHECKING Logic No

DB_BLOCK_CHECKSUM physical No

Exp physical No

The FlashBack logic is

The DBMS_REPAIR logic is

Block media recovery unknown is

Bbed (verify command) unknown

II. Introduction of DBVERIFY tools

Properties:

Is an external program that runs at the operating system prompt to verify data files and check block consistency errors

For data files only, you can verify the data files in the open phase and the data files in the shutdown state

You can verify the copied data file, or you can verify the mirrored copy of the backup

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

The authenticated file can be located on the file system, ASM disk, or raw device

Located at: $ORACLE_HOME/bin/dbv in Unix system

Located at:% ORACLE_HOME%/bin/dbv.exe in Windows system

For DBVERIFY tools, the higher version can automatically identify the lower version of the database, for example, 11g dbv accesses the 9i database, but the lower version of dbv accesses the higher version will report an error.

III. Usage of DBVERIFY tools

1. To get help information for dbv, enter dbv or dbv help=y directly at the prompt

[oracle@oradb orcl] $dbv

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

Copyright (c) 1982, 2007, 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, you will receive a DBV-00103 error.

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

FEEDBACK Display Progress (0)

PARFILE Parameter File (NONE)-you can specify a parameter file

USERID Username/Password (NONE)-check segments, ASM files need to be used

SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)-check segment, requires tablespace ID, data file ID, segment header ID

HIGH_SCN Highest Block SCN To Verify (NONE)

(scn_wrap.scn_base OR scn)

two。 To verify the online,offline data file, 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, 2007, Oracle. All rights reserved.

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

DBVERIFY-Verification complete

Total Pages Examined: 128-the total number of pages checked. A page is a data block.

Total Pages Processed (Data): 96-number of data pages processed

Total Pages Failing (Data): 0-the number of failures on processed data pages

Total Pages Processed (Index): 1-number of index pages processed

Total Pages Failing (Index): 0-- number of index page failures processed

Total Pages Processed (Other): 31-number of other pages processed

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 0

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Highest block SCN: 1152518 (0.1152518)

Note: if the value of Total Pages Influx is greater than zero and there is no bad block, it is because running the dbv program against the file in open state encountered 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 sentence will display one for every 1000 blocks validated during execution. Number

[oracle@wang ~] $dbv FILE=/u01/app/oracle/oradata/DBdb/users01.dbf FEEDBACK=1000

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:09:26 2017

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

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

Page 526 is marked corrupt

Corrupt block relative dba: 0x0100020e (file 4, block 526)

Bad check value found during dbv:

Data in bad block:

Type: 6 format: 2 rdba: 0x0100020e

Last change scn: 0x0000.00396631 seq: 0x1 flg: 0x06

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

Consistency value in tail: 0x66310601

Check value in block header: 0xb29f

Computed block checksum: 0x3b0c

DBV-00200: Block, DBA 16777783, already marked corrupt

Csc (0x0000.001082b4) higher than block scn (0x0000.00000000)

Page 567 failed with check code 6054

....

DBVERIFY-Verification complete

Total Pages Examined: 391520

Total Pages Processed (Data): 239410

Total Pages Failing (Data): 0

Total Pages Processed (Index): 55722

Total Pages Failing (Index): 1

Total Pages Processed (Other): 73037

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 23350

Total Pages Marked Corrupt: 2

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 3784055 (0.3784055)

[oracle@wang ~] $

3. To validate a specified segment (such as a table).

This method needs to get the ID of the tablespace where the segment resides, the ID of the data file where the segment resides, and the header ID of the segment, as follows:

SQL > select owner,tablespace_id,tablespace_name,header_file,header_block from sys_dba_segs where segment_name='JOBS'

OWNER TABLESPACE_ID TABLESPACE_NAME HEADER_FILE HEADER_BLOCK

SCOTT 4 USERS 4 1082

Note: sys users' segments can be queried for sys_user_segs, while ordinary users' segment information needs to be queried for sys_dba_segs.

[oracle@wang ~] $dbv USERID=scott/tiger segment_id=4.4.1082

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:22:07 2017

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

DBVERIFY-Verification starting: SEGMENT_ID = 4.4.1082

DBVERIFY-Verification complete

Total Pages Examined: 8

Total Pages Processed (Data): 1

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

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 1388330 (0.1388330)

[oracle@wang ~] $

4. Verify the replicated data file or verify the mirror copy of the backup

-- use RMAN to back up the mirror copy

RMAN > backup as copy datafile 5

Starting backup at 27-NOV-17

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbf

Output file name=/u01/app/oracle/fast_recovery_area/DBDB/datafile/o1_mf_example_f1qtvfph_.dbf tag=TAG20171127T183941 RECID=2 STAMP=961180797

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

Finished backup at 27-NOV-17

RMAN >

-- check:

[oracle@wang ~] $dbv file=/u01/app/oracle/fast_recovery_area/DBDB/datafile/o1_mf_example_f1qtvfph_.dbf

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:40:44 2017

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

DBVERIFY-Verification starting: FILE = / u01/app/oracle/fast_recovery_area/DBDB/datafile/o1_mf_example_f1qtvfph_.dbf

DBVERIFY-Verification complete

Total Pages Examined: 43360

Total Pages Processed (Data): 6597

Total Pages Failing (Data): 0

Total Pages Processed (Index): 1149

Total Pages Failing (Index): 0

Total Pages Processed (Other): 2844

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 32770

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 1795627 (0.1795627)

[oracle@wang ~] $

-- attention

The BACKUP VALIDATE DATABASE command in the RMAN command is usually used to check the whole library. This command does not produce any backup set. You can check whether it can be backed up through the Validate command, such as whether the data file exists and whether there are bad blocks that cannot be backed up. Query view v$database_block_corruption, which will check for bad blocks in the process.

Such as using the following query

RMAN > backup validate database

RMAN > backup validate database archivelog all

SQL > select * from v$database_block_corruption

No rows selected

The view v$database_block_corruption will list the location of the file where the damaged block is located, the starting position of the damaged block, the size of the damaged block, and the type of damage. If a bad block is found in the above view, you can obtain the range affected by the bad block through the SQL query, and determine whether the bad block affects the index segment or the UNDO segment.

Select owner,segment_name,segment_type from dba_extents where file_id= and between block_id and block_id+blocks-1

(and the file number and block number of the bad block reported by ORA-01578, respectively)

Let's use rman to repair damaged data blocks

RMAN > run {

Allocate channel ch2 device type disk

Blockrecover datafile 6 block 37

Release channel ch2;}

Omit.

IV. Other

1. In fact, the control file can be validated using the dbv tool (the database is in the OPEN state), see the following example

[oracle@wang ~] $dbv file=/u01/app/oracle/oradata/DBdb/control01.ctl

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:48:48 2017

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

DBV-00103 Specified BLOCKSIZE (8192) differs from actual (16384)

[oracle@wang ~] $

[oracle@wang ~] $dbv file=/u01/app/oracle/oradata/DBdb/control01.ctl BLOCKSIZE=16384

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:49:11 2017

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

DBVERIFY-Verification starting: FILE = / u01/app/oracle/oradata/DBdb/control01.ctl

DBVERIFY-Verification complete

Total Pages Examined: 604

Total Pages Processed (Data): 0

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 59

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 545

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 11504 (65535.11504)

[oracle@wang ~] $

two。 Validation of online log files (the database is in OPEN state), not supported

-- can be executed, but there are a lot of prompts below (media error)

[oracle@wang ~] $dbv file=/u01/app/oracle/oradata/DBdb/redo01.log

DBVERIFY: Release 11.2.0.4.0-Production on Mon Nov 27 18:51:19 2017

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

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

[oracle@wang ~] $

[oracle@wang ~] $dbv file=/u01/app/oracle/oradata/DBdb/redo01.log BLOCKSIZE=512

Omit.

Page 35627 is marked corrupt

Corrupt block relative dba: 0x00008b2b (file 0, block 35627)

Bad header found during dbv:

Data in bad block:

Type: 1 format: 2 rdba: 0x00008b2b

Last change scn: 0x801c.0000034c seq: 0x99 flg: 0xc2

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

Consistency value in tail: 0x0001040a

Check value in block header: 0x1f4

Block checksum disabled

Page 35628 is influx-most likely media corrupt

Corrupt block relative dba: 0x00008b2c (file 0, block 35628)

Fractured block found during dbv:

Data in bad block:

Type: 1 format: 2 rdba: 0x00008b2c

Last change scn: 0x8044.0000034c seq: 0xa2 flg: 0x11

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

Consistency value in tail: 0x000201f4

Check value in block header: 0x2

Block checksum disabled

Page 35629 is influx-most likely media corrupt

Corrupt block relative dba: 0x00008b2d (file 0, block 35629)

Fractured block found during dbv:

Data in bad block:

Type: 1 format: 2 rdba: 0x00008b2d

Last change scn: 0x80a8.0000034c seq: 0x46 flg: 0xcd

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

Consistency value in tail: 0x00395909

Check value in block header: 0x19b9

Computed block checksum: 0x0

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.