修復(fù)工具DBVERIFY的使用

發(fā)布時(shí)間：2020-08-07 09:32:10 來(lái)源：ITPUB博客閱讀：307 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫(kù)

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

DBVERIFY 工具的使用

Oracle 數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中由于硬件故障或操作系統(tǒng)故障導(dǎo)致導(dǎo)致Oracle無(wú)法以O(shè)racle格式來(lái)識(shí)別或所包含的內(nèi)容即為出現(xiàn)數(shù)據(jù)塊損壞故障，這個(gè)壞塊可以分為介質(zhì)損壞以及邏輯損壞。下面給出了塊的檢查，以及使用DBVERIFY 工具實(shí)施塊檢查。

一、塊檢查

1.何時(shí)檢查塊

    當(dāng)一個(gè)數(shù)據(jù)塊被讀或?qū)懙臅r(shí)候，將對(duì)塊的進(jìn)行一致性檢查，檢查的內(nèi)容包括如下：
        塊的版本
        比較塊在cache與block buffer中的數(shù)據(jù)塊地址
        根據(jù)要求進(jìn)行校驗(yàn)(checksum)

2.損壞的數(shù)據(jù)塊的錯(cuò)誤提示

    可以從告警日志文件中找到該錯(cuò)誤提示，以及在會(huì)話(huà)中發(fā)現(xiàn)損壞的數(shù)據(jù)塊時(shí)也會(huì)給出類(lèi)似的提示

    ORA-01578: ORACLE data block corrupted (file # 6, block # 11)
    ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'

3.與塊損壞的相關(guān)特性(幾種檢查工具)
---------------------------------------------------------------------
特性                   壞塊偵測(cè)類(lèi)型               能否修復(fù)損壞塊
----------------------------------------------------------------------
DBVERIFY                  物理                   否
ANALYZE                   邏輯                   否
DB_BLOCK_CHECKING         邏輯                   否
DB_BLOCK_CHECKSUM         物理                   否
exp                       物理                   否
FlashBack                 邏輯                   是
DBMS_REPAIR               邏輯                   是
Block media recovery      未知                   是
bbed(verify命令)           未知                   否

二、DBVERIFY工具介紹

特性:
    是一個(gè)運(yùn)行于操作系統(tǒng)提示符下的外部程序，用于驗(yàn)證數(shù)據(jù)文件，檢查塊的一致性錯(cuò)誤
    僅僅針對(duì)數(shù)據(jù)文件，能夠校驗(yàn)open階段的數(shù)據(jù)文件以及shutdown狀態(tài)下的數(shù)據(jù)文件
    可以驗(yàn)證復(fù)制的數(shù)據(jù)文件，也可以驗(yàn)證備份的鏡像副本
    不支持聯(lián)機(jī)日志文件，控制文件，歸檔日志，RMAN備份集驗(yàn)證
    被驗(yàn)證的文件可以位于文件系統(tǒng)，ASM磁盤(pán)或原始設(shè)備
    在Unix系統(tǒng)中位于:$ORACLE_HOME/bin/dbv

    在Windows系統(tǒng)中位于:%ORACLE_HOME%/bin/dbv.exe

    對(duì)于DBVERIFY工具，高版本可以自動(dòng)識(shí)別低版本數(shù)據(jù)庫(kù)，比如11g的dbv訪(fǎng)問(wèn)9i的數(shù)據(jù)庫(kù)，但是低版本的dbv訪(fǎng)問(wèn)高版本會(huì)報(bào)錯(cuò)

三、DBVERIFY工具用法
1.獲取dbv的幫助信息，直接在提示符下輸入dbv即可    或者輸入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, 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)   --指定數(shù)據(jù)文件的尺寸,缺省值為8192,對(duì)于非8192塊將收到DBV-00103錯(cuò)誤
    LOGFILE     Output Log                     (NONE)   --用于顯示驗(yàn)證進(jìn)度
    FEEDBACK    Display Progress               (0)
    PARFILE     Parameter File                 (NONE)    --可以指定參數(shù)文件
    USERID      Username/Password              (NONE)    --校驗(yàn)段、ASM文件需要使用
    SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)    --校驗(yàn)段,需要表空間ID,數(shù)據(jù)文件ID,段的頭部ID
    HIGH_SCN    Highest Block SCN To Verify    (NONE)
                (scn_wrap.scn_base OR scn)

2.校驗(yàn)online，offline數(shù)據(jù)文件，使用下面的方法： 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       --校驗(yàn)的總頁(yè)面數(shù)，一個(gè)頁(yè)面即是一個(gè)數(shù)據(jù)塊
    Total Pages Processed (Data) : 96        --已處理的數(shù)據(jù)頁(yè)面數(shù)
    Total Pages Failing   (Data) : 0         --已處理數(shù)據(jù)頁(yè)面的失敗數(shù)
    Total Pages Processed (Index): 1         --已處理的索引頁(yè)面數(shù)
    Total Pages Failing   (Index): 0         --已處理索引頁(yè)面失敗數(shù)
    Total Pages Processed (Other): 31        --已處理的其它頁(yè)面數(shù)
    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)

注意：如果Total Pages Influx的值大于零，且未存在壞塊的情況下，是由于針對(duì)open狀態(tài)的文件運(yùn)行dbv 程序遇到了一個(gè)當(dāng)前正在被DBWn進(jìn)程寫(xiě)入的數(shù)據(jù)塊

[oracle@oradb orcl]$ dbv file=$ORACLE_BASE/oradata/orcl/tbs01.dbf feedback=1000
上面這句在執(zhí)行時(shí)每驗(yàn)證1000個(gè)塊將顯示一個(gè)"."號(hào)

[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.驗(yàn)證指定段(例如表）

該方法需要獲得段所在表空間的ID，段所在數(shù)據(jù)文件的ID，段的頭部ID,如下：

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

注意：sys用戶(hù)的段可以查詢(xún)sys_user_segs，而普通用戶(hù)的段信息，需要查詢(xún)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.驗(yàn)證復(fù)制的數(shù)據(jù)文件或驗(yàn)證備份的鏡像副本

--使用RMAN備份鏡像副本

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>

--校驗(yàn)：
[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 ~]$

--注意
RMAN命令中的BACKUP VALIDATE DATABASE命令通常用于檢查全庫(kù)，該命令不產(chǎn)生任何備份集，可以通過(guò)Validate命令來(lái)檢查是否能備份，如數(shù)據(jù)文件是否存在，是否存在壞塊不能被備份，查詢(xún)視圖v$database_block_corruption，此視圖將檢查過(guò)程中存在的壞塊

如使用下面的查詢(xún)

RMAN> backup validate database;

RMAN> backup validate database archivelog all;

SQL> select * from v$database_block_corruption;

no rows selected

視圖v$database_block_corruption將列出損壞的壞塊所在的文件位置，損壞塊的起始位置，損壞快的大小以及損壞類(lèi)型如果上述視圖中發(fā)現(xiàn)了壞塊，則可以通過(guò)SQL查詢(xún)獲得壞塊所影響的范圍，以及確定壞塊所影響的是索引段還是UNDO段

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

(和分別是ORA-01578報(bào)出的壞塊出現(xiàn)的文件號(hào)和塊號(hào))

下面使用rman 來(lái)修復(fù)受損的數(shù)據(jù)塊

RMAN> run{

allocate channel ch2 device type disk;
blockrecover datafile 6 block 37;

release channel ch2;}

省略.................

四、其它

1.事實(shí)上使用dbv工具可以對(duì)控制文件進(jìn)行驗(yàn)證(數(shù)據(jù)庫(kù)處于OPEN狀態(tài)),參見(jiàn)下面的例子

[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 ~]$

2.對(duì)聯(lián)機(jī)日志文件的驗(yàn)證(數(shù)據(jù)庫(kù)處于OPEN狀態(tài))，不支持
--可以執(zhí)行，但出現(xiàn)下面很多的提示(介質(zhì)錯(cuò)誤)
[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 (512)
[oracle@wang ~]$
[oracle@wang ~]$ dbv file=/u01/app/oracle/oradata/DBdb/redo01.log BLOCKSIZE=512
省略。。。。。。。。。。。。。。。。。。

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

向AI問(wèn)一下細(xì)節(jié)

修復(fù)工具DBVERIFY的使用

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽