Oracle 索引出现坏块处理
SQL> create table test as select * from dba_objects where rownum<1001;
Table created.
SQL> create index idx_test on test(object_id);
Index created.
SQL> select file_id, block_id, blocks from dba_extents where owner = 'LILC' and segment_name = 'IDX_TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 6032 8
从第4个块开始存储,构造坏块，
RMAN> recover datafile 6 block 6035 clear;
Starting recover at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 device type=DISK
Finished recover at 23-SEP-15
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 08:51:16 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 6035 is marked corrupt
Corrupt block relative dba: 0x01801793 (file 6, block 6035)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01801793
last change scn: 0x0000.001e13c3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x13c30601
check value in block header: 0xc307
computed block checksum: 0x5f27
DBVERIFY - Verification complete
Total Pages Examined : 655360
Total Pages Processed (Data) : 7507
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1181
Total Pages Failing (Index): 0
Total Pages Processed (Other): 646167
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 504
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
验证是否是该索引出现坏块：
SQL> SELECT tablespace_name, segment_type, owner,segment_name, partition_name FROM dba_extents WHERE file_id=6 and 6035 between block_id AND block_id+blocks-1;TABLESPACE_NAME SEGMENT_TY OWNER SEGMENT_NAME PARTITION_NAME--------------- ---------- -------- -------------- ------------------------------LLCINDEX LILC IDX_TEST
此时如果全表扫描，是正常的，索引扫描报错：
SQL> select object_id from test;
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000 | 13000 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
17797 bytes sent via SQL*Net to client
1250 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> select object_id from test where object_id<100;
select object_id from test where object_id<100
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 6035)
ORA-01110: data file 6: '+DATA/phub/datafile/llc01.dbf
索引状态仍然是有效:
SQL> select status from dba_indexes where index_name='IDX_TEST';
STATUS
--------
VALID
可以加hint全表扫描就不会报错了：
SQL> select /*+ full(test) */object_id from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65 | 845 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 65 | 845 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
2137 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
解决办法：在线重建索引
SQL> alter index idx_test rebuild online;
Index altered.
SQL> select object_id from test where object_id<100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 1274 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST | 98 | 1274 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2137 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
通过DBV和 RMAN
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 09:25:38 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 6035 is marked corrupt
Corrupt block relative dba: 0x01801793 (file 6, block 6035)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01801793
last change scn: 0x0000.001e13c3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x13c30601
check value in block header: 0xc307
computed block checksum: 0x5f27
DBVERIFY - Verification complete
Total Pages Examined : 655360
Total Pages Processed (Data) : 7507
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1179
Total Pages Failing (Index): 0
Total Pages Processed (Other): 646169
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 504
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
RMAN> backup check logical validate datafile 6;
Starting backup at 23-SEP-15
using channel ORA_DISK_1
using channel ORA_DISK_2
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=+DATA/phub/datafile/llc01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 504 655364 1974761
File Name: +DATA/phub/datafile/llc01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7507
Index 1 1177
Other 0 646172
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_26417.trc for details
Finished backup at 23-SEP-15
SQL> analyze index lilc.idx_test validate structure;
Index analyzed.
RMAN> recover datafile 6 block 6035;
Starting recover at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=141 device type=DISK
finished standby search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:03--从备库修复
Finished recover at 23-SEP-15
删除索引后，重新创建索引，坏块仍然存在，但是索引可以使用
SQL> drop index idx_test;
Index dropped.
RMAN> backup check logical validate datafile 6;
Starting backup at 23-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=13 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=+DATA/phub/datafile/llc01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 504 655364 1977414
File Name: +DATA/phub/datafile/llc01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7507
Index 1 1177
Other 0 646172
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/phub/PHUB/trace/PHUB_ora_26936.trc for details
Finished backup at 23-SEP-15
删除索引，然后resize 数据文件，完成后再重建索引：
SQL> select tablespace_name,file_id,sum(bytes/1024/1024) Mb from dba_free_space where file_id=6 group by tablespace_name,file_id;
TABLESPACE_NAME FILE_ID MB
------------------------------ ---------- ----------
LLC 6 388.6875
破坏
RMAN> recover datafile 6 block 14211 clear;
Starting recover at 23-SEP-15
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished recover at 23-SEP-15
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 12:15:48 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
Page 14211 is marked corrupt
Corrupt block relative dba: 0x01803783 (file 6, block 14211)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x8af33783
last change scn: 0x5302.93e68286 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x5f820602
check value in block header: 0x7198
computed block checksum: 0x968d
DBVERIFY - Verification complete
Total Pages Examined : 25600
Total Pages Processed (Data) : 12140
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1823
Total Pages Failing (Index): 0
Total Pages Processed (Other): 334
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 11302
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
删除索引，resize数据文件
SQL> drop index idx_test;
Index dropped.
SQL> select tablespace_name,file_id,sum(bytes/1024/1024) Mb from dba_free_space where file_id=6 group by tablespace_name,file_id;
TABLESPACE_NAME FILE_ID MB
------------------------------ ---------- ----------
LLC 6 402.6875
SQL> alter database datafile '+DATA/phub/datafile/llc01.dbf' resize 100M;
Database altered.
坏块消除：
[oracle@cwogg ~]$ dbv userid=grid/grid file=+DATA/phub/datafile/llc01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Sep 23 12:15:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/phub/datafile/llc01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 12140
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 329
Total Pages Failing (Index): 0
Total Pages Processed (Other): 311
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 20
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
