Oracle索引出现坏块处理

SQL> create table test as select * from dba_objects where rownum<1001;

专注于为中小企业提供成都网站制作、网站设计服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业内江免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

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)

分享题目：Oracle索引出现坏块处理
文章出自：https://www.cdcxhl.com/article38/pcehpp.html

成都网站建设公司_创新互联，为您提供、微信公众号、网站营销、自适应网站、企业建站、标签优化

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联