扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Oracle 索引出现坏块处理
  • 623

0
分享到

Oracle 索引出现坏块处理

2024-04-02 19:04:59 623人浏览 独家记忆
摘要

sql> create table test as select * from dba_objects where rownum<1001;Table created.SQL

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 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)


您可能感兴趣的文档:

--结束END--

本文标题: Oracle 索引出现坏块处理

本文链接: https://lsjlt.com/news/41693.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • Oracle 索引出现坏块处理
    SQL> create table test as select * from dba_objects where rownum<1001;Table created.SQL...
    99+
    2024-04-02
  • 如何处理oracle中出现的坏块
    这篇文章主要为大家展示了“如何处理oracle中出现的坏块”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“如何处理oracle中出现的坏块”这篇文章吧。在orac...
    99+
    2024-04-02
  • Oracle坏块处理
    oracle 坏块问题处理 1、说明坏块问题是经常出现在数据库系统中的，如果没有合适的处理方法往往会导致坏块对象不可用或者数据丢失。本文从坏块产生开始说起，着重说明一但坏块产生后不同情况的处理方法。2、坏块...
    99+
    2024-04-02
  • Oracle数据库中出现的坏块问题如何处理
    本篇内容主要讲解“Oracle数据库中出现的坏块问题如何处理”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle数据库中出现的坏块问题如何处理”吧!一：什...
    99+
    2024-04-02
  • 怎么理解oracle索引块分裂
    这篇文章主要介绍“怎么理解oracle索引块分裂”，在日常操作中，相信很多人在怎么理解oracle索引块分裂问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解oracle...
    99+
    2024-04-02
  • java中出现索引越界如何处理
    本篇文章给大家分享的是有关java中出现索引越界如何处理，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。Java是什么Java是一门面向对象编程语言，可以编写桌面应用程序、Web...
    99+
    2023-06-14
  • ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解
    索引 索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存...
    99+
    2024-04-02
  • mysql强制索引有哪些坏处
    强制索引的使用可能会导致以下几个问题： 性能下降：强制索引可能不是最优的查询路径，因此可能导致查询性能下降。数据库优化器通常会根...
    99+
    2024-04-09
    mysql
  • oracle索引失效如何处理
    当Oracle索引失效时，可以采取以下几种处理方法： 重新生成索引：使用ALTER INDEX … REBUILD命令重新构建失...
    99+
    2024-04-09
    oracle
  • oracle中ORA-01578和ORA-01110数据文件出现坏块时跳过损坏的块怎么办
    小编给大家分享一下oracle中ORA-01578和ORA-01110数据文件出现坏块时跳过损坏的块怎么办，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面...
    99+
    2024-04-02
  • oracle索引页块碎片分析
    这篇文章主要介绍“oracle索引页块碎片分析”，在日常操作中，相信很多人在oracle索引页块碎片分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”oracle索引页块碎片...
    99+
    2024-04-02
  • ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别有哪些
    这篇文章将为大家详细讲解有关ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 ...
    99+
    2024-04-02
  • Oracle中文件损坏如何处理
    今天就跟大家聊聊有关Oracle中文件损坏如何处理，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。概述本文将给大家介绍oracle各类文件损坏的现象和...
    99+
    2024-04-02
  • Oracle索引创建及管理
    Oracle 索引创建及管理       1.      Oracle 索引简介 在 Orac...
    99+
    2024-04-02
  • ORACLE数据库表索引失效的紧急处理
    1、定位无效索引Select * From user_indexes Where StatUs!='VALID'2、重编译alter index PK_MAA_BARRIER_REQ_L...
    99+
    2024-04-02
  • ORA-19566 exceeded limit of 0 corrupt blocks数据坏块处理
    问题描述RMAN备份失败，报错如下：  一般坏块处理过程首先确定是什么段、哪个段坏了，是索引还是表？ 执行以下语句看哪个段坏了SQL>Select b.segment_name,b.s...
    99+
    2023-06-06
  • 怎么理解oracle复合索引
    这篇文章主要讲解了“怎么理解oracle复合索引”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么理解oracle复合索引”吧！首先,在大多数情况下,复合索...
    99+
    2024-04-02
  • Oracle中基于RMAN如何实现坏块介质恢复
    小编给大家分享一下Oracle中基于RMAN如何实现坏块介质恢复，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ ...
    99+
    2024-04-02
  • 《Oracle 12c 运维实战宝典》-参数文件管理与无备份坏块处理分享
    《Oracle 12c 运维实战宝典》-参数文件管理与坏块处理分享 链接：https://pan.baidu.com/s/1gbuLk8wLX4pk68mDylxGEg 密码：ohtl ...
    99+
    2024-04-02
  • Oracle中利用函数索引处理数据倾斜案例
        本文参考realkid4的博客： http://blog.itpub.net/17203031/viewspace-681311/ ...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作