Bad block processing on Oracle index

Source: Internet
Author: User
Tags dba sorts

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

Store from the 4th block, construct a bad block,

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

[Email protected] ~]$ 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, 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)

Verify that there is a bad block on the index:

Sql> SELECT tablespace_name, Segment_type, Owner,segment_name, partition_name from Dba_extents WHERE file_id=6 and 603 5 between block_id and block_id+blocks-1; Tablespace_name segment_ty OWNER segment_name partition_name----------------------------------------------------- ------------------------Llcindex LILC Idx_test

In this case, if the full table scan, is normal, index scan error:

Sql> select object_id from test;

Selected rows.

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

Bayi consistent gets

0 physical Reads

0 Redo Size

17797 Bytes sent via sql*net to client

1250 Bytes received via sql*net from client

Sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

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

The index state is still valid:

Sql> Select status from Dba_indexes where index_name= ' idx_test ';

STATUS

--------

VALID

Can add hint full table scan will not error:

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

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

WORKAROUND: Rebuild the index online

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

Through DBV and RMAN

[Email protected] ~]$ 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, 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--repair from standby

Finished recover at 23-sep-15

After the index is deleted, the index is re-created and the bad block still exists, but the index can be used

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


Bad block processing on Oracle index

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.