Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use the DBV command line tool in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Features:

Open the data file read-only, and the contents of the data file are not modified during the check.

You can check the data file online without shutting down the database.

You cannot check control files and log files, only data files.

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

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

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.

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

It is recommended that you 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 * blocks of the starting block number data file

* one block of a 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.tspapers.headerwriting filereports.headerwriting block 2 from v$tablespace trecincts dbathing segments s 3 where s.segmentmentharmnamebooks T' 4 and t.namebooks s.tablespacerecording names; 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 (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: 173Total 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 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 4 DBA 0x0100b314 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 4 Warning 45844: contents of previous BIFILE will be lost. Proceed? 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: 173Total 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): 593Total 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) the above is how to use the DBV command line tool in Oracle shared by the editor If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.