通过RMAN 识别失败数据库损坏的对象
背景
业务起不来，读取数据库时报坏块，无法读取数据
数据库版本：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
从此，业务暂时可以正常访问
