通过RMAN 识别失败数据库损坏的对象

最新推荐文章于 2023-03-25 21:17:58 发布

转载最新推荐文章于 2023-03-25 21:17:58 发布 · 156 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Coye/p/7770615.html

文章标签：

#数据库 #嵌入式

本文介绍了一种在Oracle 11.2.0.3版本中处理坏块的方法，包括如何使用RMAN命令检查坏块并获取坏块信息，通过查询V$DATABASE_BLOCK_CORRUPTION视图定位损坏的segments，并提供了修复坏块的具体步骤。

背景

业务起不来，读取数据库时报坏块，无法读取数据
数据库版本：11.2.0.3
数据库无备份，无归档

1. 识别坏块

执行以下命令后，rman 会把坏块信息统计到  v$database_block_corruption 
RMAN> backup validate check logical database;
此命令只是检查坏块，不会执行备份操作
如果执行失败的话，可以通过 'SKIP INACCESSIBLE' 来跳过失败

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;
}

RMAN> configure device type disk parallelism 4;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> backup validate check logical database;

Starting backup at 31-OCT-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1718 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=574 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1144 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1726 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/orainstall/oradata/vcdb/vpx02.dbf
input datafile file number=00012 name=/orainstall/oradata/vcdb/vpx08.dbf
input datafile file number=00015 name=/orainstall/oradata/vcdb/vpx11.dbf
input datafile file number=00018 name=/orainstall/oradata/vcdb/vpx14.dbf
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00005 name=/orainstall/oradata/vcdb/vpx01.dbf
input datafile file number=00013 name=/orainstall/oradata/vcdb/vpx09.dbf
input datafile file number=00009 name=/orainstall/oradata/vcdb/vpx05.dbf
input datafile file number=00004 name=/orainstall/oracle/oradata/vCenterora/users01.dbf
input datafile file number=00017 name=/orainstall/oradata/vcdb/vpx13.dbf
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007 name=/orainstall/oradata/vcdb/vpx03.dbf
input datafile file number=00010 name=/orainstall/oradata/vcdb/vpx06.dbf
input datafile file number=00014 name=/orainstall/oradata/vcdb/vpx10.dbf
input datafile file number=00003 name=/orainstall/oracle/oradata/vCenterora/undotbs01.dbf
input datafile file number=00019 name=/orainstall/oradata/vcdb/vpx15.dbf
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00008 name=/orainstall/oradata/vcdb/vpx04.dbf
input datafile file number=00011 name=/orainstall/oradata/vcdb/vpx07.dbf
input datafile file number=00016 name=/orainstall/oradata/vcdb/vpx12.dbf
input datafile file number=00002 name=/orainstall/oracle/oradata/vCenterora/sysaux01.dbf
input datafile file number=00001 name=/orainstall/oracle/oradata/vCenterora/system01.dbf
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 10/31/2017 15:40:01
ORA-00600: internal error code, arguments: [17182], [0x009321123], [], [], [], [], [], [], [], [], [], []
continuing other job steps, job failed will not be re-run
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              758             
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
     channel ORA_DISK_4: backup set complete, elapsed time: 00:05:41
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              20929        184360          1885647138
  File Name: /orainstall/oracle/oradata/vCenterora/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              145800          
  Index      0              13777           
  Other      0              3814            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              28532        188162          1885645691
  File Name: /orainstall/oracle/oradata/vCenterora/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       2              65106           
  Index      0              62632           
  Other      0              31890           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              31           2097152         1885647875
  File Name: /orainstall/oradata/vcdb/vpx04.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              278968          
  Index      0              1812058         
  Other      0              6095            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11   OK     0              1            2097152         1885646742
  File Name: /orainstall/oradata/vcdb/vpx07.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              253700          
  Index      0              1840993         
  Other      0              2458            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
16   OK     0              4547         2097152         1885667694
  File Name: /orainstall/oradata/vcdb/vpx12.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              199581          
  Index      0              1886779         
  Other      0              6245            

validate found one or more corrupt blocks
See trace file /orainstall/oracle/diag/rdbms/vcenterora/vCenterora/trace/vCenterora_ora_20584.trc for details
 channel ORA_DISK_3: backup set complete, elapsed time: 00:06:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              38           453760          1885647473
  File Name: /orainstall/oracle/oradata/vCenterora/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              453722          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1            2097152         1885667777
  File Name: /orainstall/oradata/vcdb/vpx03.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              325889          
  Index      0              1766492         
  Other      0              4770            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              1            2097152         1885638958
  File Name: /orainstall/oradata/vcdb/vpx06.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              40920           
  Index      0              2053651         
  Other      0              2580            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
14   OK     0              1            2097152         1885575234
  File Name: /orainstall/oradata/vcdb/vpx10.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              261376          
  Index      0              1833460         
  Other      0              2315            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
19   OK     0              124505       131072          1885647021
  File Name: /orainstall/oradata/vcdb/vpx15.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              4384            
  Other      0              2183            

   channel ORA_DISK_1: backup set complete, elapsed time: 00:09:21
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              1            4096000         1885702483
  File Name: /orainstall/oradata/vcdb/vpx02.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              687520          
  Index      0              3390361         
  Other      0              18118           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
12   OK     0              1            2097152         1885647828
  File Name: /orainstall/oradata/vcdb/vpx08.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              245391          
  Index      0              1849314         
  Other      0              2446            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
15   FAILED 0              3254         1310720         1885638704
  File Name: /orainstall/oradata/vcdb/vpx11.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              150360          
  Index      1              1150449         
  Other      0              6657            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
18   FAILED 0              6761         2097152         1885667884
  File Name: /orainstall/oradata/vcdb/vpx14.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              197930          
  Index      1              1887571         
  Other      0              4890            

validate found one or more corrupt blocks
See trace file /orainstall/oracle/diag/rdbms/vcenterora/vCenterora/trace/vCenterora_ora_20570.trc for details
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_2 channel at 10/31/2017 15:40:01
ORA-00600: internal error code, arguments: [17182], [0x009321123], [], [], [], [], [], [], [], [], [], []

trace 日志和 alert 日志里面，也会有如下坏块信息

alert日志

Corrupt block relative dba: 0x04973d16 (file 18, block 1522966)
Bad check value found during user buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x04973d16
 last change scn: 0x0000.66162e03 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x2e030601
 check value in block header: 0xa7c3
 computed block checksum: 0xbf9
Reading datafile '/orainstall/oradata/vcdb/vpx14.dbf' for corruption at rdba: 0x04973d16 (file 18, block 1522966)
Reread (file 18, block 1522966) found same corrupt data (no logical check)
Tue Oct 31 17:39:24 2017
Corrupt Block Found
         TSN = 6, TSNAME = VPX
         RFN = 18, BLK = 1522966, RDBA = 77020438
         OBJN = 82355, OBJD = 82355, OBJECT = PK_VPX_HIST_STAT1, SUBOBJECT = 
         SEGMENT OWNER = VPXADMIN, SEGMENT TYPE = Index Segment

trace日志

Corrupt block relative dba: 0x0140b784 (file 5, block 46980)
Fractured block found during validation
Data in bad block:
 type: 6 format: 2 rdba: 0x0140b784
 last change scn: 0x0000.480bac9e seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x2fcd0601
 check value in block header: 0x2c69
 computed block checksum: 0x8f55
Reread of blocknum=46980, file=/orainstall/oradata/vcdb/vpx01.dbf. found same corrupt data
Reread of blocknum=46980, file=/orainstall/oradata/vcdb/vpx01.dbf. found same corrupt data
Reread of blocknum=46980, file=/orainstall/oradata/vcdb/vpx01.dbf. found same corrupt data
Reread of blocknum=46980, file=/orainstall/oradata/vcdb/vpx01.dbf. found same corrupt data
Reread of blocknum=46980, file=/orainstall/oradata/vcdb/vpx01.dbf. found same corrupt data

查看坏块信息

SQL> select * from V$DATABASE_BLOCK_CORRUPTION; 

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
     5  46980          1          0 FRACTURED
     5  47012          1          0 FRACTURED
     5  50566          2          0 CHECKSUM
     2    358          1          0 FRACTURED
     2    359          1          22124 CORRUPT
    18    1522966          1          0 CHECKSUM
    15    1069516          1          0 CHECKSUM

7 rows selected.

2. 查看损坏的 segments

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_NAME          FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ --------------------------------------------------------------------------------- ------------------------------ ---------- ----------------- --------------- ---------------- ------------------------
SYS                TABLE          PLSCOPE_IDENTIFIER$                                                       2           358         358            1 FRACTURED
SYS                TABLE          PLSCOPE_IDENTIFIER$                                                       2           359         359            1 CORRUPT
VPXADMIN               INDEX          VPX_VM_FLE_FILE_INFO_M1                                                   5         46980       46980            1 FRACTURED
VPXADMIN               INDEX          VPX_TEXT_ARRAY_M2                                                     5         47012       47012            1 FRACTURED
VPXADMIN               INDEX          VPX_VM_FLE_FILE_INFO_M1                                                   5         50566       50567            2 CHECKSUM
VPXADMIN               INDEX          PK_VPX_HIST_STAT1                                                    15       1069516     1069516            1 CHECKSUM
VPXADMIN               INDEX          PK_VPX_HIST_STAT1                                                    18       1522966     1522966            1 CHECKSUM

3. 修复

索引的坏块，可以通过重建索引来修复
对于IOT，则才用dbms_repair.skip_corrupt_blocks 来达到跳过坏块的目的，保证剩下的数据能用

SQL> select count(1) from VPXADMIN.VPX_HIST_STAT1;
select count(1) from VPXADMIN.VPX_HIST_STAT1
                              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 15, block # 1069516)
ORA-01110: data file 15: '/orainstall/oradata/vcdb/vpx11.dbf'


SQL>exec dbms_repair.skip_corrupt_blocks('VPXADMIN','VPX_HIST_STAT1');


PL/SQL procedure successfully completed.

SQL> SQL> select count(1) from VPXADMIN.VPX_HIST_STAT1;
                       
  COUNT(1)
----------
7398816601

从此，业务暂时可以正常访问

转载于:https://www.cnblogs.com/Coye/p/7770615.html