如何使用 RMAN 識別資料庫中損壞的物件 (文件 ID 1623348.1)

mosdoc發表於2016-12-14
資料庫物件

文件內容


目標

解決方案
  步驟1：識別壞塊
  步驟2：識別損壞的段

參考


適用於:

Oracle Database - Enterprise Edition - 版本 8.1.7.0 到 11.2.0.4 [發行版 8.1.7 到 11.2]
本文件所含資訊適用於所有平臺

目標

如何使用 RMAN 識別資料庫中損壞的段。

解決方案

步驟1：識別壞塊

執行下面的 RMAN 命令，使所有的壞塊資訊被記錄在 v$database_block_corruption 檢視中:

RMAN> backup validate check logical database;


注意：

這個命令只是檢查資料庫的壞塊，而不會真正進行備份。從 11g 開始可以省略 backup 子句，而直接使用命令"validate check logical database"。

如果由於缺失檔案導致命令失敗，可以增加 'SKIP INACCESSIBLE' 子句來避免這個問題。

為了加快檢查速度，可以設定 PARALLELISM 指定多個通道:

 

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;

OR

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

輸出

壞塊資訊會被記錄在檢視 V$DATABASE_BLOCK_CORRUPTION 中。11g RMAN 會生成一個 trace 檔案，詳細描述壞塊資訊：

RMAN VALIDATE 螢幕輸出：

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              501          640             1950088   
  File Name: /oracle/dbs/users.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       9              9              
  Index      0              0               
  Other      0              130             

validate found one or more corrupt blocks
See trace file /oracle/log/diag/rdbms/orcl/orcl/trace/orcl_ora_28424.trc for details
Finished validate at <Date>

Trace 檔案輸出壞塊資訊，這個例子描述了 2 個壞塊，一個物理壞塊（file 6 block 9）和一個邏輯壞塊（file 6 block 10）：

Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
 type: 16 format: 2 rdba: 0x01000009
 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x000010ff
 check value in block header: 0xb4e0
 computed block checksum: 0xa800
Reread of blocknum=9, file=/oracle/dbs/users.dbf found same corrupt data

Block Checking: DBA = 25165834, Block Type = KTB-managed data block
data header at 0x2b2deb49e07c
kdbchk: fsbo(144) wrong, (hsz 78)
Error backing up file 6, block 10: logical corruption

壞塊資訊記錄在檢視 V$DATABASE_BLOCK_CORRUPTION 中:

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

          FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
              6              10               1      8183236781662 LOGICAL
              6              42               1                  0 FRACTURED
              6              34               2                  0 CHECKSUM
              6              50               1      8183236781952 LOGICAL
              6              26               4                  0 FRACTURED

5 rows selected.

注意：

  • CHECK LOGICAL 選項既會檢查物理壞塊也會檢查邏輯壞塊。壞塊型別解釋參見 Note 840978.1 。
  • 當發現邏輯壞塊，alert 日誌會更新以下的資訊：

    Error backing up file <file#>, block <block#>: logical corruption

    在 11g 會生成一個 trace 檔案，描述壞塊資訊。
  • 當發現物理壞塊時，alert 日誌也會更新下面的資訊：
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
 type: 16 format: 2 rdba: 0x01000009
 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x000010ff
 check value in block header: 0xb4e0
 computed block checksum: 0xa800
Reread of blocknum=9, file=/oracle/dbs/users.dbf found same corrupt data
  • 檢查單獨一個資料檔案或者特定的資料檔案，使用命令"check logical validate datafile 1, 2"。
  • 監控 VALIDATE 命令執行的進度，請執行下面的查詢：
select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
  and opname not like '%aggregate%'
  and totalwork != 0
  and sofar <> totalwork;
  • 10g 或者之前版本的 NOARCHIVELOG 資料庫，資料庫必須在 MOUNT 狀態下執行 VALIDATE 命令，否則報 ORA-19602，如果不能關閉資料庫，使用 dbverify 代替 RMAN。這個限制在 11g 取消。
  • 11g或者更高版本：可以使用 section 子句並行校驗一個單獨的資料檔案。
    RMAN 將資料檔案分割為多個部分，並行處理每個部分。下面的例子中資料檔案 5 被分割為 1gb 大小的部分，如果配置或者分配了多個通道，那麼 RMAN 會並行校驗每個部分。(參考： The Oracle Database Backup and Recovery User's Guide for more information):
backup validate check logical datafile 5 SECTION SIZE 1024M;
  • 11g 或者更高版本可以使用 BLOCK TO 子句檢查一定範圍內的資料塊。下面命令檢查資料檔案 1 中的資料塊 5 到 20：
validate check logical datafile 1 BLOCK 5 TO 20;
  • 在 8i 版本中，如果 RMAN validate 命令檢查發現壞塊，那麼只會記錄在 alert 日誌中。使用者必須在alert日誌中檢視 validate 命令執行時間範圍內的日誌。壞塊資訊不會返回到 RMAN 介面。在 9i 及以上版本中，如果 RMAN validate 命令檢查發現壞塊，那麼壞塊資訊可以透過查詢檢視 V$DATABASE_BLOCK_CORRUPTION 得到。
  • 每次執行 RMAN backup validate 命令，檢視 V$DATABASE_BLOCK_CORRUPTION 中壞塊資訊都會被更新。瞭解檢視內容，參見 Oracle? Database Reference 文件。

步驟2：識別損壞的段

下面的查詢可以將檢視 v$database_block_corruption 中記錄的壞塊匹配到對應的段或者是空閒塊。

$ sqlplus / as sysdba
set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , corruption_type description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , corruption_type||' Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;



輸出例子：

OWNER SEGMENT_TYPE       SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE              EMP                         6                10              10                1
SCOTT TABLE PARTITION    ORDER        ORDER_JAN      6                26              28                3
                                                     6                29              29                1 Free Block
SCOTT TABLE              BONUS                       6                34              34                1
                                                     6                35              35                1 Free Block
SCOTT TABLE              DEPT                        6                42              42                1 Segment Header
SCOTT TABLE              INVOICE                     6                50              50                1


注意:

  • 如果壞塊出現在字典管理的表空間，並且段頭塊損壞，上面的查詢可能顯示同一個塊 2 次。
  • 如果 ASSM 表空間的段頭塊損壞，那麼上面的查詢會顯示該段頭塊，但是相同物件上的其他壞塊可能就不會再顯示。

識別出12C中NOLOGGING的壞塊
透過RMAN中的新檢視v$nonlogged_block來識別12C中NOLOGGING塊

$ sqlplus / as sysdba

set echo on

select systimestamp
from dual;

select FILE#, BLOCK#, BLOCKS, to_char(NONLOGGED_START_CHANGE#, '999999999999999') NONLOGGED_START_CHANGE#
from v$nonlogged_block;

set pagesize 2000
set linesize 250

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

 

 

參考

NOTE:377146.1 - How to Check Archivelogs for Corruption using RMAN
NOTE:836658.1 - Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
NOTE:561010.1 - Which Blocks Will RMAN Check For Corruption Or Include In A Backupset?
NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
NOTE:144911.1 - RMAN : Block-Level Media Recovery - Concept & Example
NOTE:840978.1 - Physical and Logical Block Corruptions. All you wanted to know about it.
NOTE:338607.1 - How To Check (Validate) If RMAN Backup(s) Are Good
NOTE:144911.1 - RMAN : Block-Level Media Recovery - Concept & Example
NOTE:472231.1 - How to identify all the Corrupted Objects in the Database with RMAN
NOTE:794505.1 - ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31393455/viewspace-2130473/，如需轉載，請註明出處，否則將追究法律責任。

相關文章